The pragma AUTONOMOUS TRANSACTION enables data to be written to a database irrespective of the surrounding transaction.
In particular, the data is written persistently to the database before the SQL statement ROLLBACK WORK has possibly executed the transaction.
The pragma may only be specified in SQL statements for modifying data, i.e. in INSERT, UPDATE (search condition satisfied), DELETE (search condition satisfied), MERGE, and CALL. If the pragma is specified in statements for querying data, the statement is rejected with SQLSTATE.
The pragma may not be used in routines.
AUTONOMOUS TRANSACTION
Notes
The SQL statement after the pragma AUTONOMOUS TRANSACTION is executed in the user’s current transaction, but in a separate runtime environment (own thread, own transaction context). The user’s transaction-control statements have no effect.
The internal user identification (
APPLICATION-NAME=AUTTRAN) is used, see the “ Database Operation” manual. It is visible in information outputs while the autonomous transaction is executing. However, an autonomous transaction cannot be administered.Lock conflicts
The transaction context of the autonomous transaction is independent of the application’s surrounding transaction and of other transactions.
On the one hand, this can lead to a deadlock between the autonomous transaction and the surrounding transaction. This deadlock is resolved by resetting the autonomous transaction. The autonomous transaction is reported to the SQLSTATE 81SAT.On the other hand, this can lead to a deadlock between the autonomous transaction and other transactions. Such deadlocks are resolved by resetting the “least costly” transaction. When the autonomous transaction is affected by this, the SQLSTATE 81SAT is reported to it.Canceling the application
When the application which triggered thr autonomous transaction aborts, first the autonomous transaction is canceled, and then the current transaction or the application.