PL/SQL - Autonomous Transactions (Pragma)
Table of Contents
1 - About
- suspend the calling transaction,
- perform SQL operations and commit or undo them in the autonomous transaction,
- and then resume the calling transaction.
2 - Articles Related
3 - Example
An autonomous transaction can be useful in this case:
- in a stock purchase transaction, you want to commit customer data regardless of whether the overall stock purchase goes through.
- you want to log error messages to a debug table even if the overall transaction rolls back.
4 - Characteristics
Autonomous transactions have the following characteristics:
- The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
- Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.
- Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.
5 - PL/SQL
An autonomous transaction executes within an autonomous scope, which is a routine marked with the pragma AUTONOMOUS_TRANSACTION.
In this context, routines include top-level anonymous PL/SQL blocks and PL/SQL subprograms and triggers.
A pragma is a directive that instructs the compiler to perform a compilation option. The pragma AUTONOMOUS_TRANSACTION instructs the database that this procedure, when executed, is to be executed as a new autonomous transaction that is independent of its parent transaction.
The figure below shows how control flows from the main routine (MT) to an autonomous routine and back again. The main routine is proc1 and the autonomous routine is proc2. The autonomous routine can commit multiple transactions (AT1 and AT2) before control returns to the main routine.
- When you enter the executable section of an autonomous routine, the main routine suspends.
- When you exit the autonomous routine, the main routine resumes.
In the figure above, the COMMIT inside proc1 makes permanent not only its own work but any outstanding work performed in its session. However, a COMMIT in proc2 makes permanent only the work performed in the proc2 transaction. Thus, the COMMIT statements in transactions AT1 and AT2 have no effect on the MT transaction.