What is Autonomous Transactions?

Autonomous Transaction is an independent transaction started by another transaction, then main transaction. At the time of execution it suspends 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 resume the main transaction.

AT always execute within an autonomous scope.

Advantage:

1.     At the time execution, AT is fully independent, it share no lock, resource, or commit dependencies with the main transaction.

2.     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).

3.     The Independent nature of AT make it less error prone than regular transaction & easier to user.

Autonomous Transaction can start other Autonomous Transaction.

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):

i.e.

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;