Oracle in a Nutshell

Autonomous Transactions

Autonomous transactions, new in Oracle8i, execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes.

Tip

Autonomous transactions also allow you to commit and rollback transactions in triggers without interfering with the operation of the trigger.

Changes made in the main transaction are not visible to the autonomous transaction, and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures, database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION.

If you are using autonomous transactions, you must do the following:

• Include the PRAGMA AUTONOMOUS_TRANSACTIONS directive

• Specify COMMIT or ROLLBACK at each exit point in the autonomous program. If you do not do this, the following error is raised:

  ORA-06519: active autonomous ...