Autonomous Transaction:

Autonomous transactions are isolated or independent transactions or can also be called a transaction within another transaction. This can be explained in a better way with an example.

CREATE OR REPLACE PROCEDURE called_sp

AS

BEGIN

INSERT INTO table1 values ('called_sp: insert performed');

COMMIT;

END;

/

CREATE OR REPLACE PROCEDURE calling_sp

AS

BEGIN

INSERT INTO table1 values ('calling_sp: insert performed');

called_sp;

ROLLBACK;

END;

/

Now when the procedure 'calling_sp' is executed, the table1 will have the record inserted in 'calling_sp' as well as the record inserted in 'called_sp' because 'called_sp' has a COMMIT which commits the row inserted in 'calling_sp' also and the ROLLBACK in 'calling_sp' after 'called_sp' procedure is called, will have no effect.

EXECUTE calling_sp

PL/SQL procedure successfully completed.

SELECT * FROM table1;

COL1

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

calling_sp: insert performed

called_sp: insert performed

Now, we'll see how an autonomous transaction works. The procedure 'called_sp' is modified as an autonomous transaction.

CREATE OR REPLACE PROCEDURE called_sp

AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO table1 values ('called_sp: insert performed');

COMMIT;

END;

/

Thus any block structure is declared autonomous by having PRAGMA AUTONOMOUS_TRANSACTION in the declare section. Now empty 'table1' and check the result after 'calling_sp' is executed.

EXECUTE calling_sp

PL/SQL procedure successfully completed.

SELECT * FROM table1;

COL1

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

called_sp: insert performed

Here, note that the insert performed in 'calling_sp' has been rolled back by the rollback statement issued in the same procedure. This master transaction has been rolled back and the autonomous transaction initiated by this transaction is commited in 'called_sp' procedure declared as an autonomous transaction. Theoritically, one can have any number of autonomous transactions, i.e., a transaction initiating an autonomous transaction, which in turn initiates another autonomous transaction and so on.

Here are a few key points or rules that are to be kept in mind while working with autonomous transaction.

§ A nested block cannot be declared as autonomous transaction.

§ An autonomous transation has to be complete with a COMMIT or ROLLBACK because the transaction initiating the autonomous transaction is suspended and will be waiting for the autonomous transaction to complete.

§ An autonomous transaction cannot rollback to a savepoint defined in the initiating transaction as it is an independent one.

§ A package cannot be declared as an autonomous transaction to make all the procedures and functions declared in the package as autonomous transaction.

§ If the autonomous transaction attempts to access a resource held by the initiating transaction, it will result in a deadlock as the initiating transaction is suspended till the completion of the autonomous transaction.