Autonomous Transactions

Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.

Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent … something the application developer should watch out for.

As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).

Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):

E.g.

PRAGMA AUTONOMOUS_TRANSACTION;

Here is an example of defining a stored procedure as autonomous:

CREATE PROCEDURE process_ord_line_shipment

       (p_order_no number, p_line_no number) AS

   PRAGMA AUTONOMOUS_TRANSACTION;

   l_char_1     varchar2(100);

BEGIN

   ...

END;