Autonomous transaction

Autonomous transaction

    

    An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.

The following types of PL/SQL blocks can be defined as autonomous transactions:

• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.

The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not commited.

CREATE TABLE at_test (

  id           NUMBER       NOT NULL,

  description  VARCHAR2(50) NOT NULL

);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');

INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

2 rows selected.

SQL>

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  FOR i IN 3 .. 10 LOOP

    INSERT INTO at_test (id, description)

    VALUES (i, 'Description for ' || i);

  END LOOP;

  COMMIT;

END;

/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

10 rows selected.

SQL>

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

8 rows selected.

SQL>

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.

For more details visit https://www.youtube.com/watch?v=AcMHquDkl6M