Autonomous subtransactions
Autonomous Subtransactions
Autonomous Subtransactions (in short AST) denotes the capability of a single session to run multiple independent transactions, as if multiple different sessions were executing each transaction.
Behaviour
An AST can happen only inside another transaction.
Inside an existing transaction (call it T0), the user can decide to start a subtransaction. Then T0 is paused and pushed in an AST stack, and a new transaction (call it T1) is started.
At some point in the future the user can commit the subtransaction; after T1 is committed then T0 is popped from the AST stack and resumed.
The user can also decide to COMMIT the parent transaction T0, in which case T1 is committed, then T0 is popped from the AST stack and then committed.
All the transactions happen synchronously; at any time only one transaction can be active, while in the stack there are zero (or more) paused transactions in the stack.
All the possible combinations of COMMIT / ROLLBACK for T0 and T1 can happen; for instance, it is possible to COMMIT T1 and ROLLBACK T0.
It is possible to nest subtransactions, up to a global resource limit (e.g. the AST stack size) which can be set on the server.
Example 1 (one subtransaction)
The following figure describes an example where a transaction executes a subtransaction. A continuous line denotes an active transaction, while a dotted line denotes a transaction which has been paused and pushed in the AST stack. Time flows downwards.
BEGIN (start ordinary tx T0); | INSERT INTO t VALUES (1); :\ : BEGIN SUBTRANSACTION (start AST tx T1, pushes T0 into stack); : | : INSERT INTO t VALUES (2); : | : COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T1, pops tx T0 from stack); :/ COMMIT / ROLLBACK; (ends tx T0)
Depending on the two choices between COMMIT and ROLLBACK we can get 4 different outputs from
SELECT sum(x) from t;
Example 2 (more than one subtransaction)
The parent transaction can have more than one subtransaction, just by repeating the application of the push/pop cycle.
BEGIN (start ordinary tx T0); | INSERT INTO t VALUES (1); :\ : BEGIN SUBTRANSACTION (start AST tx T1, pushes T0 into stack); : | : INSERT INTO t VALUES (2); : | : COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T1, pops tx T0 from stack); :/ | :\ : BEGIN SUBTRANSACTION (start AST tx T2, pushes T0 into stack); : | : INSERT INTO t VALUES (4); : | : COMMIT SUBTRANSACTION / ROLLBACK SUBTRANSACTION; (ends tx T2, pops tx T0 from stack); :/ COMMIT / ROLLBACK; (ends tx T0)
Note. A different semantics is possible if we have the additional knowledge of what the next statement will be, for instance when T0 is executed inside a PL/PGSQL function. In that case the behaviour could be that the commit of a subtransaction immediately starts a new subtransaction, and that the parent transaction T0 is popped back from the stack only when the function returns. The function is then equivalent to a sequence of transactions T1,T2,... which are all subtransactions of T0, without having to pop/push T0 from the stack between T1 and T2, between T2 and T3, etc.
Visibility
Visibility rules work as in the case of independent transactions executed via dblink. T1 does not see the effects of T0, because the latter has not been committed yet. T0 might see the effects of T1, depending on its own transaction isolation mode.
Now single-session deadlocks become possible, because an AST can become entangled with one of the paused transactions in its session.
Caveat: AST break the assumption that no other transaction is active in the same session between the start and the end of a transaction; this creates additional potential for conflict in cases where a transaction accesses session-level resources (e.g. ON COMMIT DROP / DELETE ROWS for temporary tables).
References
Autonomous transactions have already been discussed at least twice:
- http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
- http://archives.postgresql.org/pgsql-hackers/2011-04/msg01149.php
The approach described here is similar to this:
The following patch was proposed:
