2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Autonomous_transaction in pl/sql

What is mean by Autonomous_transaction in pl/sql .
userimage

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

Autonomous transactions allow you to leave the context of the calling transaction, perform an independant transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions.

You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method.

You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.

Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.

In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.

If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.

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 committed.

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.

Answer is