## Chapter 16. Autonomous Transactions

An autonomous transaction is an independent transaction run within a parent transaction. Unlike subtransactions, which can only be committed as part of the transaction they belong to, autonomous transactions must be committed or rolled back before their parent is finished. While subtransactions are mostly used for error handling and in stored procedures, the main purpose of autonomous transactions is to implement audits, when an attempt to perform a transaction must be logged regardless of whether this transaction has been committed successfully.

## 16.1. Behavior

Although an autonomous transaction is always run inside another transaction, only a single transaction can be active at any given moment; other transactions have to wait until the active transaction is complete. When an autonomous transaction is started, its parent transaction is paused and pushed into the autonomous transaction stack. Once the autonomous transaction is committed or rolled back, the parent transaction is popped from the stack and resumed. You cannot commit the parent transaction until all its autonomous transactions are complete.

Within a single parent transaction, you can start several autonomous ones, which can be both consecutive and nested. By default, Postgres Pro allows running up to 100 autonomous transactions in all sessions simultaneously. You can increase this limit by changing the max_autonomous_transactions configuration parameter. The maximum nesting level is restricted to 128 and cannot be changed.

Autonomous transactions do not support the following scenarios, so they will cause an error if attempted:

- The `FOR UPDATE` locking clause is not supported, so you cannot lock a row in a parent transaction and skip it in the autonomous transaction.

- If created with the `ON COMMIT DROP` clause, temporary tables with the same name cannot be used in nested autonomous transactions.

- You cannot redefine isolation level for autonomous transactions within PL/pgSQL or PL/Python blocks.

- Autonomous transactions cannot be used together with the following extensions:

  - in_memory

  - online_analyze

  - pg_variables

Consider the following examples. A continuous line denotes an active transaction, while a dotted line denotes a transaction which has been paused and pushed into 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`, the following query can return four different results:

    SELECT sum(x) FROM t;

A parent transaction can have several autonomous transactions, which can be nested or follow one another:

    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);
    : :\
    : : BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction
    : : |                             -- T3, pushes T2 into stack
    : : |
    : : INSERT INTO t VALUES (6);
    : : |
    : : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION;
    : : |                             -- ends autonomous transaction
    : : |                             -- T3, pops T2 from stack
    : :/
    : |
    : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION;
    : |                               -- ends autonomous transaction
    : |                               -- T2, pops T0 from stack
    :/
    COMMIT / ROLLBACK;                -- ends transaction T0