Control an autonomous transaction

An autonomous routine starts a transaction when the first SQL statement is executed. When a transaction ends, the next SQL statement starts another transaction.

All SQL statements executed after the last commit or rollback constitute the current transaction. To control an autonomous transaction, use the following statements, which are effective only in the current (active) transaction:

• COMMIT

• ROLLBACK [TO savepoint_name]

• SAVEPOINT savepoint_name

Enter and exit an autonomous routine

When the execution enters the autonomous routine, the parent transaction is suspended. When the execution exits the routine, the parent transaction is resumed.

If you attempt to exit an active autonomous transaction without committing or rolling back the transaction, the database will raise an exception. If the exception is not handled, or another exception is raised and not handled, the transaction will be rolled back.

To exit an autonomous transaction normally, you must commit or roll back all autonomous transactions. If any autonomous transaction has uncommitted transactions when the execution exits the routine, the PL engine will raise an exception and roll back the uncommitted transactions.

Commit and roll back an autonomous transaction

You can use the COMMIT and ROLLBACK statements to end an active autonomous transaction, but not exit the autonomous routine. When an autonomous transaction ends, the next SQL statement starts another transaction. If an autonomous routine issues multiple COMMIT statements, it can contain multiple autonomous transactions.

Savepoint

A savepoint is effective only in the transaction that defines it. Savepoints defined in a parent transaction are unrelated to those defined in an autonomous transaction. In fact, the same savepoint name can be used in a parent transaction and an autonomous transaction.

You can roll back a transaction only to a savepoint marked in the transaction. In an autonomous transaction, you cannot roll back to a savepoint marked in the parent transaction. In this case, you must exit the autonomous transaction to resume the parent transaction.

If you roll back to a savepoint marked before the autonomous transaction is started, the autonomous transaction is not rolled back. Remember that an autonomous transaction is independent of the parent transaction.

Avoid errors in autonomous transactions

To avoid some common errors, remember the following points:

• A deadlock occurs if an autonomous transaction attempts to access resources held by the parent transaction. In this case, the database will raise an exception in the autonomous transaction. If the exception is not handled, the transaction will be rolled back.

• If you attempt to exit an active autonomous transaction without committing or rolling back the transaction, the database will raise an exception. If the exception is not handled, the transaction will be rolled back.

• The PIPE ROW statement cannot be executed within an autonomous transaction when it is opened. You must close the transaction before you execute the PIPE ROW statement. Typically, you commit or roll back the transaction before you execute the PIPE ROW statement.