Overview

An autonomous transaction is an independent transaction initiated by another transaction (the parent transaction). An autonomous transaction executes SQL operations and commits or rolls back without committing or rolling back the parent transaction.

Although an autonomous transaction is initiated by another transaction, it is not a nested transaction. The reasons are as follows:

• 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 an autonomous transaction are immediately visible to other transactions.

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

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

Advantages of autonomous transactions

After an autonomous transaction is initiated, it is completely independent. It does not share locks, resources, or commit dependencies with the parent transaction. Even if the parent transaction rolls back, you can record events and increment the retry counter.

Autonomous transactions help you build modular, reusable software components. You can encapsulate autonomous transactions in stored subprograms. The calling application does not need to know whether the stored subprogram executed successfully or failed.

Transaction context

A 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), the routines do not share any transaction context. When an autonomous routine calls a non-autonomous routine, the routines share the same transaction context.

Transaction visibility

When an autonomous transaction is committed, the changes it makes are 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 is recovered.

If the isolation level of the parent transaction is set to SERIALIZABLE, the changes made by its autonomous transactions are not visible to the parent transaction when it is recovered. Here is an example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Declare autonomous routines

To declare autonomous routines, use the AUTONOMOUS_TRANSACTION compilation directive.

For better readability, we recommend that you place the AUTONOMOUS_TRANSACTION compilation directive at the top of the declaration section. (You can use this compilation directive at any location in the declaration section.)

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