PRAGMA AUTONOMOUS_TRANSACTION v16
A stored procedural language (SPL) program can be declared as an autonomous transaction by specifying the following directive in the declaration section of the SPL block. An autonomous transaction is an independent transaction started by a calling program.
A commit or rollback of SQL commands in the autonomous transaction has no effect on the commit or rollback in any transaction of the calling program. A commit or rollback in the calling program has no effect on the commit or rollback of SQL commands in the autonomous transaction.
The following SPL programs can include
- Standalone procedures and functions
- Anonymous blocks
- Procedures and functions declared as subprograms in packages and other calling procedures, functions, and anonymous blocks
- Object type methods
The following are issues and restrictions related to autonomous transactions:
- Each autonomous transaction consumes a connection slot for as long as it's in progress. In some cases, this might mean that you need to raise the
max_connectionsparameter in the
- In most respects, an autonomous transaction behaves as if it were a completely separate session, but GUCs (settings established with
SET) are a deliberate exception. Autonomous transactions absorb the surrounding values and can propagate values they commit to the outer transaction.
- Autonomous transactions can be nested, but there is a limit of 16 levels of autonomous transactions in a single session.
- Parallel query isn't supported in autonomous transactions.
- The EDB Postgres Advanced Server implementation of autonomous transactions isn't entirely compatible with Oracle databases. The EDB Postgres Advanced Server autonomous transaction doesn't produce an error if there's an uncommitted transaction at the end of an SPL block.
The following set of examples use autonomous transactions. This first set of scenarios shows the default behavior when there are no autonomous transactions.
Before each scenario, the
dept table is reset to the following initial values:
This first set of scenarios shows the insertion of three rows:
- Starting just after the initial
BEGINcommand of the transaction
- From an anonymous block in the starting transactions
- From a stored procedure executed from the anonymous block
The stored procedure is the following:
The PSQL session is the following:
After the final commit, all three rows are inserted:
The next scenario shows that a final
ROLLBACK command after all inserts results in the rollback of all three insertions:
ROLLBACK command given at the end of the anonymous block also eliminates all three prior insertions:
The next set of scenarios shows the effect of using autonomous transactions with
PRAGMA AUTONOMOUS_TRANSACTION in various locations.
The procedure remains as initially created:
PRAGMA AUTONOMOUS_TRANSACTION is given with the anonymous block along with the
COMMIT command at the end of the anonymous block:
ROLLBACK at the end of the transaction, only the first row insertion at the beginning of the transaction is discarded. The other two row insertions in the anonymous block with
PRAGMA AUTONOMOUS_TRANSACTION were independently committed.
Scenario 2b: Autonomous transaction anonymous block with COMMIT, including procedure with ROLLBACK but not an autonomous transaction procedure
This procedure has the
ROLLBACK command at the end. However, the
PRAGMA ANONYMOUS_TRANSACTION isn't included in this procedure.
The rollback in the procedure removes the two rows inserted in the anonymous block (
deptno 60 and 70) before the final
COMMIT command in the anonymous block:
After the final commit at the end of the transaction, the only row inserted is the first one from the beginning of the transaction. Since the anonymous block is an autonomous transaction, the rollback in the enclosed procedure has no effect on the insertion that occurs before the anonymous block is executed.
Scenario 2c: Autonomous transaction anonymous block with COMMIT, including procedure with ROLLBACK that is also an autonomous transaction procedure
The procedure with the
ROLLBACK command at the end also has
PRAGMA ANONYMOUS_TRANSACTION included. This isolates the effect of the
ROLLBACK command in the procedure.
The rollback in the procedure removes the row inserted by the procedure but not the other row inserted in the anonymous block.
After the final commit at the end of the transaction, the row inserted is the first one from the beginning of the transaction as well as the row inserted at the beginning of the anonymous block. The only insertion rolled back is the one in the procedure.
The following examples show
PRAGMA AUTONOMOUS_TRANSACTION in a couple of other SPL program types.
This example shows the effect of declaring a trigger with
The following table is created to log changes to the
The trigger attached to the
emp table that inserts these changes into the
empauditlog table is the following.
PRAGMA AUTONOMOUS_TRANSACTION is included in the declaration section.
The following two inserts are made into the
emp table in a transaction started by the
The following shows the two new rows in the
emp table as well as the two entries in the
But then the
ROLLBACK command is given during this session. The
emp table no longer contains the two rows, but the
empauditlog table still contains its two entries. The trigger implicitly performed a commit, and
PRAGMA AUTONOMOUS_TRANSACTION commits those changes independent from the rollback given in the calling transaction.
This example shows the effect of declaring an object method with
The following object type and object type body are created. The member procedure in the object type body contains the
PRAGMA AUTONOMOUS_TRANSACTION in the declaration section along with
COMMIT at the end of the procedure.
In the following anonymous block, an insert is performed into the
dept table, followed by invoking the
insert_dept method of the object and ending with a
ROLLBACK command in the anonymous block.
insert_dept was declared as an autonomous transaction, its insert of department number 60 remains in the table, but the rollback removes the insertion of department 50:
- On this page
- Requirements and restrictions
- About the examples
- Scenario 1a: No autonomous transactions with only a final COMMIT
- Scenario 1b: No autonomous transactions but a final ROLLBACK
- Scenario 1c: No autonomous transactions, but anonymous block ROLLBACK
- Scenario 2a: Autonomous transaction of anonymous block with COMMIT
- Scenario 2b: Autonomous transaction anonymous block with COMMIT, including procedure with ROLLBACK but not an autonomous transaction procedure
- Scenario 2c: Autonomous transaction anonymous block with COMMIT, including procedure with ROLLBACK that is also an autonomous transaction procedure
- Autonomous transaction trigger
- Autonomous transaction object type method