Control autonomous transactions

Autonomous routines start a transaction when they execute the first SQL statement. When a transaction ends, the next SQL statement starts another transaction.

All SQL statements executed since the last commit or rollback form the current transaction. To control autonomous transactions, use the following statements, which apply only to the current (active) transaction:

• COMMIT

• ROLLBACK [TO savepoint_name]

• SAVEPOINT savepoint_name

Enter and exit autonomous routines

When you enter the executable part of an autonomous routine, the main transaction is suspended. When you exit the routine, the main transaction resumes.

If you attempt to exit an active autonomous transaction without committing or rolling it back, the database raises an exception. If the exception is not handled, or if another unhandled exception causes the transaction to end, the transaction is rolled back.

To exit an autonomous routine normally, you must explicitly commit or roll back all autonomous transactions. If a routine (or any routine called by the routine) has uncommitted transactions, PL/SQL raises an exception and rolls back the uncommitted transactions.

Commit and rollback autonomous transactions

The COMMIT and ROLLBACK statements end an active autonomous transaction but do not exit the autonomous routine. When a transaction ends, the next SQL statement starts another transaction. If an autonomous routine issues multiple COMMIT statements, multiple autonomous transactions can exist.

Savepoints

A savepoint is valid only for the transaction in which it is defined. A savepoint defined in the main transaction is unrelated to a savepoint defined in an autonomous transaction. In fact, the main transaction and autonomous transaction can use the same savepoint name.

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

When in the main transaction, rolling back to a savepoint marked before the autonomous transaction started does not roll back the autonomous transaction. Remember that autonomous transactions are completely independent of the main transaction.

Avoid errors in autonomous transactions

To avoid some common errors, remember the following:

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

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

• When an autonomous transaction is open, you cannot execute the PIPE ROW statement in an autonomous routine. You must close the autonomous transaction before executing the PIPE ROW statement. This is typically done by committing or rolling back the autonomous transaction before executing the PIPE ROW statement.