An autonomous transaction is an independent transaction that is started by another transaction (which is the main transaction). An autonomous transaction executes an SQL operation and is then committed or rolled back, without the need to commit or roll back the main transaction.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
An autonomous transaction is started by another transaction, but is not a nested transaction. The reasons are as follows:
It does not share resources such as locks with the main transaction.
It does not depend on the main transaction. For example, if the main transaction is rolled back, a nested transaction will also be rolled back but an autonomous transaction will not.
The changes committed by an autonomous transaction are immediately visible to other transactions.
Before the main transaction is committed, the changes committed by a nested transaction are invisible to other transactions.
An exception raised in an autonomous transaction will result in a transaction-level rollback, rather than a statement-level rollback.
Benefits of autonomous transactions
An autonomous transaction is totally independent after it is started. An autonomous transaction does not share locks, resources, or commitment dependencies with the main transaction. Even if the main transaction is rolled back, an autonomous transaction can still record events and enable the retry counter to increment.
An autonomous transaction can help you build modular reusable software components. You can encapsulate an autonomous transaction in a stored subprogram. A called application does not need to know whether the operations executed by the stored subprogram succeeded or failed.
Transaction context
The main transaction shares its context with nested routines, but not with autonomous routines. When one autonomous routine calls or recursively calls another autonomous routine, it does not share any transaction context. When an autonomous routine calls a non-autonomous routine, they share the same transaction context.
Transaction visibility
After an autonomous transaction is committed, the changes made by it are visible to other transactions. If you set the isolation level of the autonomous transaction to READ COMMITTED, which is the default value, these changes will be visible to the main transaction when it restores.
If you set the isolation level of the main transaction to SERIALIZABLE, the changes made by its autonomous transactions will be invisible to the main transaction when it restores. Here is an example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Note
- Transaction attributes apply only to transactions for which these attributes are set.
- Cursor attributes are not affected by autonomous transactions.
Declare an autonomous routine
You can use the AUTONOMOUS_TRANSACTION pragma to declare an autonomous routine.
To facilitate reading, we recommend that you put the AUTONOMOUS_TRANSACTION pragma at the top of the declarative part. This pragma can be used regardless of its position in the declarative part.
You cannot apply the AUTONOMOUS_TRANSACTION pragma to an entire program package or an abstract data type (ADT), but can apply it to each subprogram in the program package or each method in an ADT.