Autonomous transactions allow you to create a “transaction within a transaction” that will commit or roll back changes independently of its parent transaction.
They allow you to suspend the currently executing transaction, start a new one, do some work, and commit or roll back—all without affecting the currently executing transaction state.
Autonomous transactions provide a new method of controlling transactions in PL/SQL and may be used in
•\ Top-level anonymous blocks
•\ Local (a procedure in a procedure), stand-alone, or packagedfunctions and procedures
•\ Methods of object types
•\ Database triggers
Before we take a look at how autonomous transactions work, I’d like to emphasize that this type of transaction is a powerful and therefore dangerous tool when used improperly. The true need for an autonomous transaction is very rare indeed. I would be very suspicious of any code that makes use of them—that code would get extra examination. It is far too easy to accidentally introduce logical data integrity issues into a system using them. In the sections that follow, we’ll discuss when they may safely be used after seeing how they work.
How Autonomous Transactions Work
The best way to demonstrate the actions and consequences of an autonomous transaction is by example. We’ll create a simple table to hold a message:
SQL> create table t ( msg varchar2(25) ); Table created.
Next, we’ll create two procedures, each of which simply INSERTs its name into the message table and commits. However, one of these procedures is a normal procedure, and the other is coded as an autonomous transaction. We’ll use these objects to show what work persists (is committed) in the database under various circumstances.
First, here’s the AUTONOMOUS_INSERT procedure:
$ sqlplus eoda/foo@PDB1
SQL> create or replace procedure Autonomous_Insert aspragma autonomous_transaction;begininsert into t values ( ‘Autonomous Insert’ ); commit;end;/Procedure created.
Note the use of the pragma AUTONOMOUS_TRANSACTION. This directive tells the database that this procedure, when executed, is to be executed as a new autonomous transaction, independent from its parent transaction.
Note A pragma is simply a compiler directive, a method to instruct the compiler to perform some compilation option. Other pragmas are available. Refer to the Oracle Database PL/SQL Language Reference manual; you’ll find a list of them in its index.
And here’s the “normal” NONAUTONOMOUS_INSERT procedure:
SQL> create or replace procedure NonAutonomous_Insert asbegininsert into t values ( ‘NonAutonomous Insert’ ); commit;end;/Procedure created.
Now let’s observe the behavior of the nonautonomous transaction in an anonymous block of PL/SQL code:
SQL> begininsert into t values ( ‘Anonymous Block’ );NonAutonomous_Insert;rollback;end;/
PL/SQL procedure successfully completed.
SQL> select * from t;MSG
As you can see, the work performed by the anonymous block, its INSERT, was committed by the NONAUTONOMOUS_INSERT procedure. Both rows of data were committed, so the ROLLBACK command had nothing to roll back. Compare this to the behavior of the autonomous transaction procedure:
SQL> delete from t;2 rows deleted.
SQL> commit;Commit complete.
SQL> begininsert into t values ( ‘Anonymous Block’ );Autonomous_Insert;rollback;end;/
PL/SQL procedure successfully completed.
SQL> select * from t;MSG
Here, only the work done by and committed in the autonomous transaction persists. The INSERT done in the anonymous block was rolled back by the ROLLBACK statement on line 4. The autonomous transaction procedure’s COMMIT has no effect on the parent transaction started in the anonymous block. In a nutshell, this captures the essence of autonomous transactions and what they do.
To summarize, if you COMMIT inside a “normal” procedure, it will make durable not only its own work but also any outstanding work performed in that session. However, a COMMIT performed in a procedure with an autonomous transaction will make durable only that procedure’s work.
