Autonomous Transactions 2

Autonomous transactions are started by a parent, or main, transaction but operate independently of the parent for transaction control. If a commit or rollback is used in the autonomous or main transaction, or if a failure occurs for any reason, it does not impact the other transaction.

Our favorite use of this feature is for logging application events. If the need is to log activity, regardless of the outcome, but the logging success or failure should not impact the application, autonomous transactions are the perfect solution. 

To create an autonomous transaction, use a pragma called AUTONOMOUS_TRANSACTION. The pragma is placed in the declaration section of the block. When the code is executed, the PL/SQL compiler sees the pragma (instructions for the compiler), and handles the block as autonomous.

We tend to group our code for event logging and auditing in a package, but the autonomous transaction code can be created in procedures, functions, triggers, and object types. 

In this example, we create a procedure using the pragma AUTONOMOUS_TRANSACTION:

–– Available online as part of Autonomous.sql

CREATE OR REPLACE PROCEDURE logging_ins (

i_username IN VARCHAR2,

i_datetime IN TIMESTAMP)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO logging (username, datetime)

VALUES (i_username, i_datetime);

commit;

END;

/

This procedure, when called, will operate independently of the parent transaction calling it. Here we create a procedure that inserts a record into the books table, calls the LOGGING_INS procedure we just created, and then performs a rollback.

–– Available online as part of Autonomous.sql

CREATE OR REPLACE PROCEDURE book_ins (

i_isbn IN BOOKS.ISBN%TYPE,

i_category IN BOOKS.CATEGORY%TYPE,

i_title IN BOOKS.TITLE%TYPE,

i_num_pages IN BOOKS.NUM_PAGES%TYPE,

i_price IN BOOKS.PRICE%TYPE,

i_copyright IN BOOKS.COPYRIGHT%TYPE,

i_author1 IN BOOKS.AUTHOR1%TYPE,

i_author2 IN BOOKS.AUTHOR1%TYPE,

i_author3 IN BOOKS.AUTHOR1%TYPE)

IS

BEGIN

–– Parent transaction begins

INSERT INTO books (ISBN, CATEGORY,

TITLE, NUM_PAGES,

PRICE, COPYRIGHT, AUTHOR1,

AUTHOR2, AUTHOR3)

VALUES (i_isbn, i_category,

i_title, i_num_pages,

i_price, i_copyright, i_author1,

i_author2, i_author3);

–– Call is made to procedure to begin autonomous transaction

LOGGING_INS('PLSQL', systimestamp);

–– The rollback is for the parent transaction only

ROLLBACK;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(sqlerrm);

END;

/

To test this out, we execute the BOOK_INS procedure as follows:

–– Available online as part of Autonomous.sql

BEGIN

BOOK_INS('12345678',

'Oracle Server', 'Oracle Information Retrieval with Oracle Text',

440, 35.99, 2005, 44, NULL, NULL);

END;

/

The BOOK_INS procedure inserted a record into the BOOKS table with an ISBN value of 12345678. Next, it logged the event in the LOGGING table by calling an autonomous transaction in the form of the LOGGING_INS procedure. Finally, back in the parent procedure, it performed a ROLLBACK. This is illustrated in Figure below. 

To test how well the autonomous transaction worked, we can select the records from the BOOKS and LOGGING tables.

–– Available online as part of Autonomous.sql

COL username FORMAT A10

COL datetime FORMAT A30

SELECT *

FROM logging;

This returns

USERNAME DATETIME

––––– –––––––––––––––

PLSQL 13-JUL-04 09.58.13.050000 PM

The record inserted from the autonomous transaction is in the LOGGING table, so the commit was successful. We can run the following select to check the BOOKS table:

–– Available online as part of Autonomous.sql

SELECT *

FROM books

WHERE isbn = '12345678';

no rows selected

This returns no rows, so the rollback was successful.

Be careful to always terminate autonomous transactions with a commit or rollback!

Neglecting transaction control statements in basic PL/SQL objects is sloppy. Doing so in objects declared as autonomous transactions will lead to errors. Next we create a procedure identical to the LOGGING_INS procedure used earlier, except we have no commit or rollback:

–– Available online as part of Autonomous.sql

CREATE OR REPLACE PROCEDURE logging_ins_error (

i_username IN VARCHAR2,

i_datetime IN TIMESTAMP)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO logging (username, datetime)

VALUES (i_username, i_datetime);

–– NO COMMIT OR ROLLBACK

END;

/

There is no error when compiling this code. We will try running it and see what happens.

–– Available online as part of Autonomous.sql

EXEC LOGGING_INS_ERROR('PLSQL', systimestamp)

Executing this procedure results in the following error:

BEGIN LOGGING_INS_ERROR('PLSQL', systimestamp); END;

*

ERROR at line 1:

ORA-06519: active autonomous transaction detected and rolled back

ORA-06512: at "PLSQL.LOGGING_INS_ERROR", line 13

ORA-06512: at line 1

Use transaction control statements COMMIT and ROLLBACK appropriately, and avoid this error.