The Oracle database has supported autonomous transactions internally for quite a while. We see them all of the time in the form of recursive SQL. For example, a recursive transaction may be performed when selecting from a sequence, in order for you to increment the sequence immediately in the SYS.SEQ$ table. The update of the SYS. SEQ$ table in support of your sequence is immediately committed and visible to other transactions, but your transaction is not yet committed. Additionally, if you roll back your transaction, the increment to the sequence remains in place; it is not rolled back with your transaction, as it has already been committed. Space management, auditing, and other internal operations are performed in a similar recursive fashion.
This feature has now been exposed for all to use. However, I have found that the legitimate real-world use of autonomous transactions is very limited. Time after time, I see them used as a workaround to such problems as a mutating table constraint in a trigger. This almost always leads to data integrity issues, however, since the cause of the mutating table is an attempt to read the table upon which the trigger is firing. Well, by using an autonomous transaction, you can query the table, but you are querying the table now without being able to see your changes (which is what the mutating table constraint was trying to do in the first place; the table is in the middle of a modification, so query results would be inconsistent). Any decisions you make based on a query from that trigger would be questionable—you are reading “old” data at that point in time.
A potentially valid use for an autonomous transaction is in custom auditing, but I stress the words “potentially valid.” There are more efficient ways to audit information in the database than via a custom-written trigger. For example, you can use the DBMS_FGA package or just the AUDIT command itself.
A question that application developers often pose to me is, “How can I log errors in my PL/SQL routines in a manner that will persist, even when my PL/SQL routines’ work is rolled back?” Earlier, we described how PL/SQL statements are atomic—they either completely succeed or completely fail. If we logged an error in our PL/SQL routines, by default our logged error information would roll back when Oracle rolled back our statement. Autonomous transactions allow us to change that behavior, to have our error logging information persist even while the rest of the partial work is rolled back.
Let’s start by setting up a simple error logging table to use; we’ll record the timestamp of the error, the error message, and the PL/SQL error stack (for pinpointing where the error emanated from):
$ sqlplus eoda/foo@PDB1
SQL> create table error_log( ts timestamp,err1 clob,err2 clob )/
Now we need the PL/SQL routine to log errors into this table. We can use this small example:
SQL> create or replaceprocedure log_error( p_err1 in varchar2, p_err2 in varchar2 )aspragma autonomous_transaction;begininsert into error_log( ts, err1, err2 ) values ( systimestamp, p_err1, p_err2 ); commit;end;/
The “magic” of this routine is on line 5 where we used the pragma autonomous_ transaction directive to inform PL/SQL that we want this subroutine to start a new transaction, perform some work in it, and commit it—without affecting any other transaction currently in process. The COMMIT on line 9 can affect only the SQL performed by this LOG_ERROR procedure.
Now let’s test it out. To make it interesting, we’ll create a couple of procedures that will call each other:
SQL> create table t ( x int check (x>0) ); Table created.
SQL> create or replace procedure p1( p_n in number )
— some code hereinsert into t (x) values ( p_n );end;/
SQL> create or replace procedure p2( p_n in number )asbegin
— code
— code p1(p_n);end;/
And then we’ll invoke those routines from an anonymous block:
SQL> beginp2( 1 );p2( 2 );p2( -1);exceptionwhen othersthenlog_error( sqlerrm, dbms_utility.format_error_backtrace ); RAISE;end;/
ERROR at line 1:
ORA-02290: check constraint (EODA.SYS_C007751) violated
ORA-06512: at line 9
ORA-06512: at “EODA.P1”, line 5
ORA-06512: at “EODA.P2”, line 6
ORA-06512: at line 4
Now, we can see the code failed (you want that error returned, hence the RAISE on line 9). We can verify that Oracle undid our work (we know that the first two calls to procedure P2 succeeded; the values 1 and 2 are successfully inserted into our table T):
SQL> select * from t;no rows selected
But we can also verify that our error log information has persisted and in fact is committed:
SQL> rollback;Rollback complete.
SQL> set long 1000000
SQL> select * from error_log;
In my experience, that is the only truly valid use of an autonomous transaction—to log errors or informational messages in a manner that can be committed independently of the parent transaction.
