This example illustrates how an autonomous transaction is executed. A continuous line denotes an active transaction, while a dotted line denotes a transaction which has been paused and pushed in the autonomous transaction stack. Time flows downwards.
BEGIN; -- starts ordinary transaction T0 | INSERT INTO t VALUES (1); :\ : BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction : | -- T1, pushes T0 into stack : | : INSERT INTO t VALUES (2); : | : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION; : | -- ends autonomous transaction : | -- T1, pops transaction T0 from stack :/ COMMIT / ROLLBACK; -- ends transaction T0
Depending on the two choices between COMMIT and ROLLBACK, we can get four different outputs from:
SELECT sum(x) from t;
The parent transaction can have more than one autonomous transaction if the push/pop cycle is repeated.
BEGIN; -- starts ordinary transaction T0 | INSERT INTO t VALUES (1); :\ : BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction : | -- T1, pushes T0 into stack : | : INSERT INTO t VALUES (2); : | : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION; : | -- ends autonomous transaction : | -- T1, pops T0 from stack :/ | :\ : BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction : | -- T2, pushes T0 into stack : | : INSERT INTO t VALUES (4); : | : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION; : | -- ends autonomous transaction : | -- T2, pops T0 from stack :/ COMMIT / ROLLBACK; -- ends transaction T0