Oracle autonomous transaction

Post date: 18-May-2010 09:19:26

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction. 

The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues.

An autonomous transactions is available from Oracle 8i. 

An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.

This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block. 

There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section: 

PRAGMA AUTONOMOUS_TRANSACTION;

Sample code:

PROCEDURE test_autonomous  IS   PRAGMA AUTONOMOUS_TRANSACTION; BEGIN    insert ....    commit; END test_autonomous; 

Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.

Autonomous Transactions

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

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

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.

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

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

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.

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.

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>

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>

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>

We define a procedure to log error messages as an autonomous transaction.

The following code forces an error, which is trapped and logged.

From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren't, we would expect the first insert in the anonymous block to be preserved by the commit statement in theLOG_ERRORS procedure.

Be careful how you use autonomous transactions. If they are used indiscriminately they can lead to deadlocks, and cause confusion when analyzing session trace. To hammer this point home, here's a quote from Tom Kyte posted on my blog (here):

CREATE TABLE error_logs (   id             NUMBER(10)     NOT NULL,   log_timestamp  TIMESTAMP      NOT NULL,   error_message  VARCHAR2(4000),   CONSTRAINT error_logs_pk PRIMARY KEY (id) );  CREATE SEQUENCE error_logs_seq;

CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS   PRAGMA AUTONOMOUS_TRANSACTION; BEGIN   INSERT INTO error_logs (id, log_timestamp, error_message)   VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);   COMMIT; END; /

BEGIN   INSERT INTO at_test (id, description)   VALUES (998, 'Description for 998');    -- Force invalid insert.   INSERT INTO at_test (id, description)   VALUES (999, NULL); EXCEPTION   WHEN OTHERS THEN     log_errors (p_error_message => SQLERRM);     ROLLBACK; END; /  PL/SQL procedure successfully completed.  SELECT * FROM at_test WHERE id >= 998;  no rows selected  SELECT * FROM error_logs;          ID LOG_TIMESTAMP ---------- --------------------------------------------------------------------------- ERROR_MESSAGE ----------------------------------------------------------------------------------------------------          1 28-FEB-2006 11:10:10.107625 ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")   1 row selected.  SQL>

... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.

Where do people try to use them?

Error logging - OK.

Almost everything else - not OK.