Control autonomous transactions

An autonomous routine starts a transaction from the first SQL statement. When one transaction ends, the next SQL statement starts another transaction.

All SQL statements executed since the last commit or rollback constitute the current transaction. You can use the following statements to control an autonomous transaction. These statements apply only to the current active transaction.

• COMMIT

• ROLLBACK [TO savepoint_name]

• SAVEPOINT savepoint_name

Enter and exit an autonomous routine

When you enter the executable part of an autonomous routine, the main transaction will be suspended. After you exit the routine, the main transaction will be resumed.

If you attempt to exit an active autonomous transaction without committing it or rolling it back, the database will raise an exception. If this transaction ends because this exception is not handled or due to other unhandled exceptions, this transaction will be rolled back.

To properly exit a routine, you must explicitly commit or roll back all autonomous transactions. If the routine or any other routine called by this routine has a pending transaction, PL raises an exception and the pending transaction will be rolled back.

Commit and roll back an autonomous transaction

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

Savepoints

A savepoint is valid only for the transaction that defines this savepoint. Savepoints defined in the main transaction are irrelevant to those defined in an autonomous transaction of the main transaction. The main transaction and the autonomous transaction can use the same savepoint name.

You can roll back only to a savepoint tagged in the current transaction. In an autonomous transaction, you cannot roll back to a savepoint tagged in the main transaction. Therefore, you must exit the autonomous routine to resume the main transaction.

If you roll back the main transaction to a savepoint tagged before an autonomous transaction is started, this autonomous transaction will not be rolled back. Autonomous transactions are totally independent of the main transaction.

Avoid errors in autonomous transactions

Take note of the following items to avoid some common errors:

• If an autonomous transaction attempts to access the resources owned by the main transaction, a deadlock occurs. If the database raises an exception in an autonomous transaction and the exception is not handled, the autonomous transaction will be rolled back.

• If you attempt to exit an active autonomous transaction without committing it or rolling it back, the database raises an exception. If you do not handle this exception, the autonomous transaction will be rolled back.

• If you have opened an autonomous transaction, you cannot execute the PIPE ROW statement in the autonomous routine. You must close the autonomous transaction before you execute the PIPE ROW statement. You can commit or roll back the autonomous transaction before you execute the PIPE ROW statement.