16.3. Examples

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