Overview of autonomous transactions

An autonomous transaction is an independent transaction initiated by another transaction (the parent transaction). Autonomous transactions execute SQL operations and commit or roll back without requiring the parent transaction to commit or roll back.

Although an autonomous transaction is initiated by another transaction, it is not a nested transaction. Here's why:

• It does not share transaction resources (such as locks) with the parent transaction.

• It is not dependent on the parent transaction. For example, if the parent transaction rolls back, the nested transaction rolls back, but the autonomous transaction does not.

• The changes committed by the autonomous transaction are immediately visible to other transactions.

  Before the parent transaction commits, the changes committed by the nested transaction are not visible to other transactions.

• Exceptions raised in an autonomous transaction result in transaction-level rollback, not statement-level rollback.

Advantages of autonomous transactions

Once started, autonomous transactions are completely independent. They do not share locks, resources, or commit dependencies with the parent transaction. Even if the parent transaction rolls back, you can record events and increment retry counters.

Autonomous transactions help you build modular, reusable software components. You can encapsulate autonomous transactions within stored subprograms. The calling application does not need to know whether the stored subprogram's operations succeeded or failed.

Transaction context

The parent transaction shares its context with nested routines but not with autonomous routines. When an autonomous routine calls another autonomous routine (or calls itself recursively), it does not share any transaction context. When an autonomous routine calls a non-autonomous routine, they share the same transaction context.

Transaction visibility

When an autonomous transaction commits, the changes it made become visible to other transactions. If the isolation level is set to READ COMMITTED (the default), these changes are also visible to the parent transaction when it recovers.

If the parent transaction's isolation level is set to SERIALIZABLE, the changes made by the autonomous transaction are not visible to the parent transaction when it recovers. Here's an example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Declaring autonomous routines

To declare an autonomous routine, use the AUTONOMOUS_TRANSACTION compilation directive.

For readability, it is recommended to place the AUTONOMOUS_TRANSACTION compilation directive at the top of the declaration section. (You can use this directive anywhere in the declaration section.)

You cannot apply the AUTONOMOUS_TRANSACTION compilation directive to an entire package or ADT, but you can apply it to each subprogram in a package or each method in an ADT.