Nested transactions - do they exist? Cannot find any documentation about this

[Rich B]

Hi there

Do real nested transactions exist in H2?

For example:

(Running in H2 console, auto-commit checkbox OFF):

set lock_mode 1;

select * from test;

begin;
select * from test;

begin;
insert into test (value) values ('hello');
commit;

select * from test;
rollback;

select * from test;

This is adding rows to my 'test' table. The inner trans is being committed, although it is a second-level transaction, and the first level being rollbacked.

I'm sure this is easily explained.

Thanks

Rich

[Evgenij Ryazanov]

Hello.

You can't nest one transaction into another in H2, but you can use a savepoint inside a transaction.

-- some work


SAVEPOINT name;


-- some work


-- Reverts to the specified savepoint
ROLLBACK TO SAVEPOINT name;


-- Reverts the whole transaction
ROLLBACK;

https://h2database.com/html/commands.html#savepoint

https://h2database.com/html/commands.html#rollback