Autonomous Transactions: a Poor Mis-Understood Feature

In short an Autonomous Transaction lets a job, commit some data to the database as an ON-THE-SIDE event, without committing data in the MAIN-EVENT. Sounds useful and it can be. But the Autonomous Transaction can also be dangerous. An Autonomous Transaction is kind of like a teenage daughter (I have two) "Oh DAD! You just don't understand me". A lack of understanding is a foundation for trouble. Maybe we can’t understand our teenagers, but we can understand Autonomous Transactions a little better.

First, allow me to point you to some additional reading. Autonomous Transactions are not new, and there have been many good articles written about them to help us all improve our understanding of them. Normally I would post actual links to a few choice articles, but this topic is easy to find so I would suggest you do the normal stuff that you should be doing anyway when you are faced with a problem or need to learn something new.

1) Search OraFAQ using "Autonomous Transactions"
2) Head over to asktomhome and do the same search (or your other favorite oracle web site)
3) Do an internet search
4) If you have access to metalink, try the search there

So what is an Autonomous Transaction?

Recall the definition of an ATOMIC Transaction? Basically all work done between two commits. A good job stream generally is composed of just one transaction (OK oversimplified but enough for our discussion). This means we start our job, it modifies a bunch of tables with inserts, updates, and deletes, and then it commits all its work or it does a rollback. Everything gets done or it all gets undone like nothing happened. That makes everything we did before the commit, an ATOMIC UNIT OF WORK.

But if all work gets done or none gets done, then how for example do we record the error that occurred, which caused our job to roll back? We cannot just insert an error message into a table, because when the transaction gets undone by a rollback, so will our error message insert.

One way I suppose is to do a little footwork with PLSQL error trapping. But what we could really use is the ability to suspend the current transaction, and start a new one. We then do what ever we need done in this second transaction, commit to finish it, and then resume the main transaction. The Autonomous Transaction provides us with this ability. Here is a simple example of PLSQL that shows how it works.

create table t1 (a number)
/

create table t2 (a number)
/

create or replace
procedure p2
as
   pragma autonomous_transaction;
begin
   insert into t2 values (2);
   commit;
end;
/
show errors

create or replace
procedure p1
as
begin
   insert into t1 values (1);
   p2;
   rollback;
end;
/
show errors

select * from t1
/

select * from t2
/

exec p1

select * from t1
/

select * from t2
/

If you look closely at this code, you should notice two things:

1) P2 contains the AUTONOMOUS_TRANSACTION pragma thus designating it as an Autonomous Transaction. It contains a commit at the end to save its work.

2) P1 calls P2 and then does a rollback undoing everything it did.

This sequence of procedures begs two questions:

1) If P2 does a commit after P1 inserts, will the insert of P1 be saved?

2) If P1 is issuing a rollback for its work, what does that mean to the insert done by P2?

An Autonomous Transaction is independent of the calling transaction. This means that the work done by p2 cannot affect the work done by p1 and the work done by p1 cannot affect the work done by p2. With respect to transaction semantics, it is as if two different sessions were executing one of each of these pieces of code. Therefore, the commit in p2 can have no impact on the insert done by p1 and the rollback of p1 is not in conflict with the commit done in p2. If you understand this, then you should understand why a call to p1 results in a row in t2 and no rows in t1 as seen below.

SQL> select * from t1
  2  /

no rows selected

SQL> select * from t2
  2  /

no rows selected

SQL> exec p1

PL/SQL procedure successfully completed.

SQL> select * from t1
  2  /

no rows selected

SQL> select * from t2
  2  /

         A
----------
         2

If we reverse the commit and rollback with this variation of code:

create or replace
procedure p2
as
   pragma autonomous_transaction;
begin
   insert into t2 values (2);
   rollback;
end;
/
show errors

create or replace
procedure p1
as
begin
   insert into t1 values (1);
   p2;
   commit;
end;
/
show errors

SQL> delete from t1
  2  /

1 row deleted.

SQL> delete from t2
  2  /

0 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> exec p1

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from t1
  2  /

         A
----------
         1

SQL> select * from t2
  2  /

no rows selected

Then we can see there is a row in t1 and none in t2. You should run these SQL snippets yourself so that you can see first hand the behavior of Autonomous Transactions.

THE KEY THING to take way from these examples is that there are TWO transactions involved; a main transaction, and a side transaction. Technically I suppose there is no such thing as a “side transaction”, but I choose these two terms for emphasis because I hope to guide you into thinking about Autonomous Transactions as side events, and not as a mechanism for main processing logic; (more on this later).

So, what do the two transactions see from each other?

This is a fundamental issue to grasp. If you know the answer to the above question then you have a reasonable handle on how Autonomous Transactions interact with other transactions. Again, let us look at a code snippet for the details.

delete from t1
/

delete from t2
/

commit
/

create or replace
procedure p2
as
   pragma autonomous_transaction;
   count_v number := 0;
begin
   select count(*) into count_v from t1;
   if count_v = 0 then raise_application_error(-20999,'no rows in table t1');
   else raise_application_error(-20999,'t1 has rows');
   end if;
   commit;
end;
/
show errors

create or replace
procedure p1
as
begin
   insert into t1 values (1);
   p2;
   commit;
end;
/
show errors

exec p1

Continuing with our original tables (t1 and t2), the code snippet above will do four things of interest:

1) P1 will insert a row into table t1
2) P1 will call P2, an Autonomous Transaction
3) The Autonomous Transaction will count the rows in table t1
4) The Autonomous Transaction will raise an exception based on how many rows it counts, thus showing us how it interacts with its calling transaction.

Executing p1 we get the following:

SQL> exec p1
BEGIN p1; END;

*
ERROR at line 1:
ORA-20999: no rows in table t1
ORA-06512: at "KEVIN.P2", line 7
ORA-06512: at "KEVIN.P1", line 5
ORA-06512: at line 1

The error thrown tells us that the SELECT statment in the Autonomous Transaction counted zero rows in table T1. How can that be? We inserted a row into t1 before we started the Autonomous Transaction, how can the Autonomous Transaction not see it? The answer is simple but requires a basic understanding of Oracle transaction semantics.

An Autonomous Transaction is as was said before, a separate transaction. We know that Oracle supports several variations of concurrency and transaction control, also called ISOLATION LEVEL, and we know that the default mode for Oracle which is almost universally the setting of all Oracle instances, is STATEMENT LEVEL CONSISTENCY and it works like this: each SQL statement can see the committed work in the database as of the time it starts, plus all uncommitted changes made by its transaction up to the time it starts.

Understanding this, it is obvious why the Autonomous Transaction counted zero rows; because being a separate transaction from the calling transaction, its SQL statements cannot see any uncommitted changes made by the calling transaction. This is a little surprise developers confront when first learning how to use Autonomous Transactions.

How about the other way around, can the calling transaction’s SQL see the committed changes of the Autonomous Transaction? Understanding Oracle transaction semantics, we would expect yes. I leave the proof of this to you guys to do.

Still even after you have shown that the calling transaction can see the changes made by the called Autonomous Transaction, you have to remember, that you were doing your work using the default ISOLATION LEVEL that Oracle runs under. There is some discussion on the internet about how changing the ISOLATION LEVEL you are using can change this result such that the calling transaction won’t see changes made by its called Autonomous Transaction. A discussion of ISOLATION LEVEL is way more that I want to get into. It is moot by my way of thinking anyway, as almost everybody uses the default ISOLATION LEVEL. But hey, I thought I’d mention it so if you were game you could search for it yourself. If you find some interesting pages on the net, post them here so we can all read them. You can check the following on metalink for a hint to this.

Note:65961.1

You may be grasping at this point, that although Autonomous Transactions look very useful on the surface (and they can be), the details of their behavior can lead the unsuspecting into trouble.

Getting into Trouble
Autonomous Transactions and avoiding Mutating Tables (bad idea)

Knowing that an Autonomous Transaction cannot see the uncommitted work of its calling transaction, what happens when a trigger calls an Autonomous Transaction? Better yet, what happens when the entire trigger is itself composed of an Autonomous Transaction? Consider the following code snippet:

create table t3 (a number)
/

create or replace
trigger air_t3
after insert on t3
for each row
declare
   count_v number := 0;
begin
   select count(*) into count_v from t3;
   raise_application_error(-20999,'t3 rowcount = '||count_v);
end;
/
show errors

insert into t3 values (1)
/

After reviewing this code, some of you may recognize the inevitable mutating table error we are going to get.

SQL> insert into t3 values (1)
  2  /
insert into t3 values (1)
            *
ERROR at line 1:
ORA-04091: table KEVIN.T3 is mutating, trigger/function may not see it
ORA-06512: at "KEVIN.AIR_T3", line 4
ORA-04088: error during execution of trigger 'KEVIN.AIR_T3'

It is not legal to select off a table we just inserted into. This is one of the details of working with triggers on tables. Without getting into the details of this error, suffice it to say that Oracle is doing its job by protecting us from updating data in an ambiguous situation.

But suppose we make the trigger an Autonomous Transaction, then what? Assuming the ambiguity of the trigger is based on not really knowing the state of the table to show as is the case for a multi-row insert, then making the trigger an Autonomous Transaction should resolve the ambiguity because it defines what we must see with respect to uncommitted changes.

create or replace
trigger air_t3
after insert on t3
for each row
declare
   pragma autonomous_transaction;
   count_v number := 0;
begin
   select count(*) into count_v from t3;
   raise_application_error(-20999,'t3 rowcount = '||count_v);
end;
/
show errors

insert into t3 values (1)
/

SQL> insert into t3 values (1)
  2  /
insert into t3 values (1)
            *
ERROR at line 1:
ORA-20999: t3 rowcount = 0
ORA-06512: at "KEVIN.AIR_T3", line 6
ORA-04088: error during execution of trigger 'KEVIN.AIR_T3'

No more mutating error. Neat you say. How easy is that, to avoid mutating and constraining table errors? True, but did you examine our user defined error message, raised by the code snippet? It says there are no rows in the table the trigger is firing on. How can that be? This is an AFTER INSERT OF ROW trigger; it only fires when a new row is added to the table, so there must be a row in the table. But as we have already seen, an Autonomous Transaction cannot see uncommitted changes of the transaction that called it. How intuitive it is that a trigger fired for the insert of a row cannot see the row that fired it.

Indeed, a little forethought reveals the situation is much deeper. Since the Autonomous Transaction cannot see uncommitted changes from its calling transaction, all work done by the calling transaction is not viewable to the trigger code, not just the row inserted in to the table. If the Autonomous Transaction should happen to need to see the effects of modified data from the main transaction, it can't.

There are at least two dangers here.

1) There are sequences of events (inserts, updates, deletes) that can transpire which although you managed to get by the mutating table error, will only lead you to another error later on in the code.

2) Worse, there are sequences of events (inserts, updates, deletes) which won’t generate an error later; your transaction will succeed, but because of the nuances of Autonomous Transactions, you may not be getting the answer you think you are getting, thus resulting in possible data corruption by your application. Hmm.. I wonder what all this means to trigger chains several layers deep.

Triggers with Autonomous Transactions are clearly not for the faint of heart. Generally speaking, they are considered bad practice and except for the most knowledgeable of developers, this combination of features used together will lead to unexpected problems. Here are some interesting links that discusses the issue in more detail.

http://forums.oracle.com/forums/thread.jspa?threadID=474329

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2212445691154

Getting into Trouble
How to deadlock you own session

Deadlock is a situation where transaction A locks resource r1 and transaction B locks resource r2, then transaction A tries to lock r2 and transaction B tries to lock r1. Each transaction is waiting on the other for a resource that will never be released because neither transaction can finish. Catch 22? What does this mean to Autonomous Transactions?

I am sure you have guessed it. Locking in Oracle is managed at a transaction level, not a session level. Autonomous Transactions are nothing special in this regard. They deadlock the same way as normal transactions. Thus a single session can deadlock itself if it is not careful. Here is a simple code snippet to illustrate. Notice because we are using Autonomous Transactions which means the primary transaction is in suspense and cannot finish till the Autonomous Transaction completes, we only need one resource to lock on, not the typical circular chain of locked resources (neat a chain of length one).

create table t4 (a number)
/

insert into t4 values (1)
/

commit
/

create or replace
procedure p2
as
   pragma autonomous_transaction;
begin
   update t4 set a = 2;
   commit;
end;
/
show errors

create or replace
procedure p1
as
begin
   update t4 set a = 3;
   p2;
   commit;
end;
/
show errors

exec p1

SQL> exec p1
BEGIN p1; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "KEVIN.P2", line 5
ORA-06512: at "KEVIN.P1", line 5
ORA-06512: at line 1

Wow, that was even easier than I thought. I didn’t have to wait long at all for the error to come back. You can check this issue via this metalink document if you have access to metalink.

Note:224305.1

Or, you could just try reading the manual. It has an entire section devoted to transaction management, including a discussion on deadlock in Autonomous Transactions.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/transact.htm

Valid if Possibly Dubious ways to use Autonomous Transactions

I will admit to having an affinity for code that abuses the database. Some of the best code I have seen is people writing stuff that really stretches the intent of a feature. This natural developer creativity is not lost on Autonomous Transactions. Here is a sampling some special scenarios:

Updating Data from a Query

We all know you can write a PLSQL function and if it has the right PURITY LEVEL, you can call this function from SQL. But, these function may not write to the database. Why? Again it has to do with potential ambiguity of data that might be seen during the query.

But as we saw before, the Autonomous Transaction removes this ambiguity. Does this mean that if we build our function that writes to the databvase, as an Autonomous Transaction, we can call this function that writes to the database from a query? Yes it does. Here is a simple code snippet to illustrate:

drop table t1
/

drop table t2
/

create table t1 (a number)
/

insert into t1 values (1)
/

commit
/

create table t2 (a number)
/

create or replace
function f1 return number
as
   pragma autonomous_transaction;
begin
   insert into t2 values (2);
   commit;
   return (1);
end;
/
show errors

select t2.a from t2
/
select t1.a,f1 from t1
/
select t2.a from t2
/

SQL> select t2.a from t2
  2  /

no rows selected

SQL> select t1.a,f1 from t1
  2  /

         A         F1
---------- ----------
         1          1

SQL> select t2.a from t2
  2  /

         A
----------
         2

Yep, using an Autonomous Transaction, we just queried a row off table t1 and the select statement called a function which unknown to the query, inserted a row into t2. One proposed reason used for this is the need to record the fact that some specific data was queried. Given a controlled application, one could insert a row into an audit table of some kind whenever a specific column was selected, or a specific row was selected. It might also be possible to stop people from querying as in some obscure need to make sure people can select “such and such” only once per day. But like anything, there are nuances to consider.

As a general rule it is I suppose true, that a function in a select list is executed once per row returned, but not really. There are several situations in which we don’t know how many times oracle will actually call a function embedded in a query. In fact it really does not matter how many times a function is called from a query. As long as the query gives the correct answer, Oracle should be free to optimize query execution as it sees fit. But what if we are trying to change data in a hidden fashion along the way? If the function is not execute the expected number of times, our query may give us the correct answer, but our hidden update may well be wrong. Here are two examples (I am sure there are more):

1) Deterministic Functions. These are functions that always return the same answer for the same inputs. Oracle has an optimizing feature for deterministic functions that allows it to reduce the number of times it has to call them, to the point where it may not even call the function at all. Yes, you have to declare the function as deterministic so you have some control over it. But you sure need to know what your transactions are going to look like.

2) Functions in a Where clause get called differently depending upon query plans. Tom Kyte has a fine example of how he can make the same table and same data and same query, execute a function call a different number of times based on something as simple as adding an index, or changing statistics. Actually, anything that could cause a query plan change at runtime could have this affect.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1547006324238

Indeed, there is even a suspect issue of the read consistency of using functions from SQL in the first place. Here is an interesting post form Tom Kyte again (gee maybe you should check his site since I keep referencing him) on this issue:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:228182900346230020

Executing DDL from a Trigger

I basically said Autonomous Transactions inside Triggers was usually a bad idea. But consider this problem: “I want to create a new user, whenever a row is inserted into table X”. Using an Autonomous Transaction, we can execute DDL like “create user” if we so desire. This certainly has the basic functionality we want.

But there is of course one obvious flaw: what happens when the main transaction fails, after the Autonomous Transaction has created the user? Answer: you have a user ID in your database, without the associated referencing data used to create it because the row insert that holds this information was undone by a rollback.

If the user is inserted a second time (which he can be because there is no row in the table to stop it), you get an error in the Autonomous Transaction when you try to create the user. You will need some fancy error handling and transaction management to deal with this and I bet it still won’t be right. It looks like the easy solution is starting to get a lot more complicated.

In the old days I think this need was satisfied by using DBMS_JOB to submit a job request that would create the user. If I recall correctly, the job submit won’t go through unless the transaction completes successfully. This is better as you won’t get orphaned users in your database, although I suppose you have to wait for the job to finish before actually seeing and making use of the user so there is some delay which might not fit in with your plans. All in all it is an ugly problem to start with. I won’t bother with a code snippet for this one.

So, what are Autonomous Transactions for?

Maybe it’s a bit dull, but in a phrase: low volume job auditing. Oracle originally exposed Autonomous Transactions to developers as a mechanism for committing information on the side whilst not affecting the main event. They wanted to give us a way to record what happened in a job regardless of success or failure of the job itself, and without changing the transaction semantics of the main transaction.

To this end, it is actually a good idea to use Autonomous Transactions to create a job monitoring package for your self to enable the monitoring of PLSQL jobs. You would capture information like:

• Job start and end
• Job parameters
• Job step start and end
• Job step parameters
• Job and Step error conditions
• And whatever else you want.

Steve Feuerstein of Quest Software fame has one.
Tom Kyte of asktomhome fame has one.
I have one (not that I would ever put my self in the same league as these two gentlemen).
You should have one.

Job logging that uses Autonomous Transactions allows you to capture the log data for sure. My comment of Low Volume is intended to indicate that you don’t really want to spawn Autonomous Transactions for example inside a PLSQL loop for every row, because this can get expensive. Additionally, there are auditing tools built into Oracle these days and I for one am loath to write my own code to duplicate a feature that Oracle already provides so there may be an existing auditing solution you can adapt or integrate into your overall auditing plans.

Two Simple Rules to keep in mind.

All of the usual tricks people try to do with Autonomous Transactions:

• Avoiding Mutating Table Errors
• Doing DDL in Triggers
• Changing Database State (and Package State) from SQL

have one common theme; they try to circumvent the natural transaction semantics of an Oracle Session. It is in this attempt to integrate Autonomous Transactions into the main stream logic of a process that opens the door to the ugliness that follows.

If we want to use Autonomous Transactions in a known safe and efficient way, then we can keep just two simple rules in mind:

1) An Autonomous Transaction cannot see uncommitted changes from the parent transaction.

2) Autonomous Transactions should be used as “add on” capabilities which are essentially irrelevant to an otherwise wholly consistent piece of code.

We have seen rule #1 all throughout this discussion so let us concentrate on rule #2. A restatement goes like this:

If you were to remove all Autonomous Transactions from a job, and the job still executes correctly and does what it is supposed to do, then your use of Autonomous Transactions fits the bill for rule#2. They are in a word “unnecessary”, to the actual business purpose of the job. The net affect is that the Autonomous Transactions are side events to the main attraction.

I am not saying you can’t use Autonomous Transactions for the needs we have discussed here. Well OK, maybe for most people I am saying that. Lord knows I am pretty dumb overall and you guys are far smarter than I. The only reason I am writing, and you are reading this article, is a question of time. I have been working Oracle databases for twenty some odd years and in that amount of time even a blind squirrel will find an acorn now and again. It is just that my experience with Autonomous Transactions required a lot of growth on my part, and it was not always pretty.

AS a contractor, I meet people every year who have been writing PLSQL code for most of a decade yet still don’t know what an Autonomous Transaction is. For those that do, they do not really get that the Autonomous Transaction is a sophisticated and sensitive feature requiring a better than average familiarity with the Oracle RDBMS if you want to use it for anything other that what it was originally intended, and which will lead to unexpected results if applied without this understanding.

You know better than I how good you are at this job. But if the terms:

ATOMIC UNIT OF WORK
Autonomous Transaction
ISOLATION LEVEL
PURITY LEVEL
Query Sub-Factoring
Trigger Nesting
Transaction Design

scare you even a little, then my recommendation is stick to the basic problem these jewels were meant to solve and find some other way to address what ails you.

Good luck, Kevin