AUTONOMOUS_TRANSACTION Pragma V8.1-10.2
--------------------------------------
Before the release of PL/SQL 8.1, each Oracle session could have at most one active transaction at a given time.
In other words,any and all changes made in your session had to be either saved or erased in their entirety.
This restriction has long been considered a drawback in the PL/SQL world.
Developers have requested the ability to execute and save or cancel certain DML statements (INSERT, UPDATE, DELETE) without affecting the overall session’s transaction.
You can now accomplish this goal with the autonomous transaction feature of PL/SQL 8.1 and above.
When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller’s transaction context.
That block becomes an independent transaction that is started by another transaction, referred to as the main transaction.
Within the autonomous transaction block, the main transaction is suspended.
You perform your SQL operations, commit or roll back those operations, and resume the main transaction.
To define a PL/SQL block as an autonomous transaction you simply include the following statement in your declaration section:
PRAGMA AUTONOMOUS_TRANSACTION;
The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent.
For the purposes of the autonomous transaction, a PL/SQL block can be any of the following:
Top-level (but not nested) anonymous PL/SQL blocks
Functions and procedures, defined either in a package or as standalone programs
Methods (functions and procedures) of a SQL object type
Database triggers
You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block.
The best option, however, would be to place it before any data structure declarations.
That way, anyone reading your code will immediately identify the program as an autonomous transaction.
This pragma is the only syntax change made to PL/SQL to support autonomous transactions.
COMMIT, ROLLBACK, the DML statements—all the rest is as it was before. However, these statements have a different scope of impact and visibility when executed within an autonomous transaction,
and you will have to include a COMMIT or ROLLBACK in your program.
The AUTONOMOUS_TRANSACTION pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent).
An autonomous transaction is an independent transaction started by another transaction, the main transaction.
Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous.
You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction.
So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.
Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK.
Also, unlike regular triggers, autonomous triggers can execute DDL statements (such as CREATE and DROP) using native dynamic SQL.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits.
The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default).
If you set the isolation level of the main transaction to SERIALIZABLE,
as follows, changes made by its autonomous transactions are not visible to the main transaction when it resumes:
When in the main transaction, rolling back to a savepoint marked before you started an autonomous transaction does not roll back the autonomous transaction.
Remember, autonomous transactions are fully independent of the main transaction.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur.
In that case, Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back,
Oracle raises an exception. If the exception goes unhandled, the transaction is rolled back.
PRAGMA AUTONOMOUS_TRANSACTION-Data base triggers
-------------------------------------------------
The benefit of autonomous transactions for database triggers is that inside those triggers you can now issue COMMITs and ROLLBACKs, statements that are otherwise not allowed in database triggers.
The changes you commit and roll back will not affect the main transaction that caused the database trigger to fire.
They will only apply to DML activity taking place inside the trigger itself (or through stored program units called within the trigger).
You may want to take an action in the database trigger that is not affected by the ultimate disposition of the transaction that caused the trigger to fire.
For example, suppose that you want to keep track of each action against a table, whether or not the action is completed. You might even want to be able to detect which actions failed. You can use autonomous transactions to do this.
First, construct a simple autonomous transaction trigger on the ceo_compensation table that writes a simple message to the following ceo_comp_history table. Here are the two table definitions:
CREATE TABLE ceo_compensation (
company VARCHAR2(100),
name VARCHAR2(100),
compensation NUMBER,
layoffs NUMBER);
CREATE TABLE ceo_comp_history (
name VARCHAR2(100),
description VARCHAR2(255),
occurred_on DATE);
Here is the before-insert trigger to run all the elements in the script:
CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
BEFORE INSERT ON ceo_compensation FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES (
:new.name, 'BEFORE INSERT', SYSDATE);
COMMIT;
END;
/
With this trigger in place, every insert attempt can be tracked, as shown in the steps below:
BEGIN
INSERT INTO ceo_compensation VALUES (
'Mattel', 'Jill Barad', 9100000, 2700);
INSERT INTO ceo_compensation VALUES (
'American Express Company',
'Harvey Golub', 33200000, 3300);
INSERT INTO ceo_compensation VALUES (
'Eastman Kodak', 'George Fisher', 10700000, 20100);
ROLLBACK; --I wish!
END;
/
SELECT name,
description,
TO_CHAR (occurred_on,
'MM/DD/YYYY HH:MI:SS') occurred_on
FROM ceo_comp_history;
NAME DESCRIPTION OCCURRED_ON
-------------------- --------------------- -------------------
Jill Barad BEFORE INSERT 03/17/1999 04:00:56
Harvey Golub BEFORE INSERT 03/17/1999 04:00:56
George Fisher BEFORE INSERT 03/17/1999 04:00:56
Fine-Tuning the Database Trigger
----------------------------------
But there is something of a problem with the trigger just defined.
Tthe trigger was defined as an autonomous transaction because the alert was performed in the body of the trigger.
Suppose you want to perform some additional DML for the main transaction here in the trigger.
It won’t be rolled back with the rest of the transaction (if a rollback occurs).
Generally, it is recommended that you not make a database trigger itself the autonomous transaction.
Instead, push all of the independent DML activity (such as writing to the audit or history table) into its own procedure.
Make that procedure the autonomous transaction. Have the trigger call the procedure.
First, create the audit procedure:
CREATE OR REPLACE PROCEDURE audit_ceo_comp (
name IN VARCHAR2,
description IN VARCHAR2,
occurred_on IN DATE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES (
audit_ceo_comp.name,
audit_ceo_comp.description,
audit_ceo_comp.occurred_on
);
COMMIT;
END;
/
Then change the trigger to the following.
CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
AFTER INSERT ON ceo_compensation FOR EACH ROW
DECLARE
ok BOOLEAN := is_valid_comp_info (:NEW.name);
BEGIN
IF ok
THEN
audit_ceo_comp (
:new.name, 'AFTER INSERT', SYSDATE);
ELSE
RAISE VALUE_ERROR;
END IF;
END;
/
Note the following differences:
------------------------------------
The trigger is now an after-insert trigger, rather than a before-insert trigger.
Wait until after the INSERT to the compensation table takes place, then perform the audit.
When the is_valid_comp_info function returns FALSE, do not even perform an audit.
Instead, stop the transaction by raising an error.
This demonstrates the other reason you don’t want the trigger itself to be autonomous.
In some situations, you will always want to perform an audit.
Under other circumstances, however, you may want to stop the main transaction by raising an exception.
Both of those events can't happen if the exception is raised in the same block and transaction as the audit DML.
As you take advantage of the new autonomous transaction pragma, plan out how you will be using these new code elements.
You will almost always be better off hiding the details of your new, independent transactions behind a procedural interface.
AUTONOMOUS TRANSACTION USES:
You will want to define your program module as an autonomous transaction whenever you want to isolate the changes made in that module from the caller’s transaction context.
Here are some specific ideas where autonomous transactions are useful:
Logging mechanism
----------------
This is the classic example of the need for an autonomous transaction. You need to log error information in a database table, but don't want that log entry to be a part of the logical transaction.
Commits and rollbacks in your database triggers
--------------------------------------------------
If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code.
Retry counter
--------------------
Autonomous transactions can help you keep track of how many times a user tries to connect to a database or get access to a resource (you'll reject access after a certain number of attempts).
Software usage meter
------------------------
You want to keep track of how often a program is called during an application session.
This information is not dependent on, and cannot affect, the transaction being processed in the application.
Reusable application components
------------------------------------
It is becoming ever more important to be able to offer standalone units of work (also known as cartridges) that get their job done without any side effects on the calling environment.
Autonomous transactions will play a crucial role in this area.
PRAMA LOGGING MECHANISM:
--------------------------
A very common requirement in applications is to keep a log of errors that occur during transaction processing.
The most convenient repository for this log is a database table; with a table, all the information is retained in the database and you can use SQL to retrieve and analyze the log.
One problem with a database table log, however, is that entries in the log become a part of your transaction.
If you perform (or have performed to you) a ROLLBACK, you can easily erase your log.
You can use savepoints to preserve your log entries while cleaning up your transaction, but that approach is complicated.
With autonomous transactions, however, logging becomes simpler, more manageable, and less error prone.
Suppose a log table is defined as follows:
CREATE TABLE log81tab (
code INTEGER,
text VARCHAR2(4000),
created_on DATE,
created_by VARCHAR2(100),
changed_on DATE,
changed_by VARCHAR2(100),
machine VARCHAR2(100),
program VARCHAR2(100)
);
It can be used to store errors (SQLCODE and SQLERRM) that have occurred, or even for non-error-related logging.
The machine and program columns record information available from the virtual V$ SESSION table, as you will see.
Never expose your underlying logging mechanism by explicitly inserting into it in your exception sections and other locations.
Instead, you should build a layer of code around the table (this is known as encapsulation).
There are two reasons why you should do this:
If you ever change your table’s structure, all those uses of the log table will not be disrupted.
People will be able to use the log table in a much easier, more consistent manner.
So here is a simple logging package. It consists of two procedures:
CREATE OR REPLACE PACKAGE log81
IS
PROCEDURE putline (
code_in IN INTEGER,
text_in IN VARCHAR2
);
PROCEDURE saveline (
code_in IN INTEGER,
text_in IN VARCHAR2
);
END;
/
The log81.saveline procedure (as you will see in the package body) is an autonomous transaction routine,
whereas log81.putline simply performs the insert. Here is the package body:
CREATE OR REPLACE PACKAGE BODY log81
IS
CURSOR sess IS
SELECT MACHINE, PROGRAM
FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID');
rec sess%ROWTYPE;
PROCEDURE putline (
code_in IN INTEGER,
text_in IN VARCHAR2
)
IS
BEGIN
INSERT INTO log81tab
VALUES (
code_in,
text_in,
SYSDATE,
USER,
SYSDATE,
USER,
rec.machine,
rec.program
);
END;
PROCEDURE saveline (
code_in IN INTEGER,
text_in IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
putline (code_in, text_in);
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;
BEGIN
OPEN sess; FETCH sess INTO rec; CLOSE sess;
END;
/
Here are some comments on this implementation that you might find helpful:
Some useful information from V$SESSION is obtained when the package is initialized (the values are not going to change during your session, so you should only query it once)
and that information should be incorporated into the log.
The putline procedure performs the straight insert. You would probably want to add some exception handling to this program if you applied this idea in your production application.
The saveline procedure calls the putline procedure, but does so from within the context of an autonomous transaction.
With this package in place, the error handler shown earlier can be as simple as this:
EXCEPTION
WHEN OTHERS
THEN
log81.saveline (SQLCODE, SQLERRM);
END;
Developers don’t have to concern themselves with the structure of the log table. They don’t even have to know they are writing to a database table. Because an autonomous transaction was used,
they can rest assured that no matter what happens in their application, the log entry has been saved.
PRAGMA BUILDING A RETRY COUNTER:
---------------------------------
Suppose that you want to let a user try to get access to a resource (a file, a row of data, etc.) N times before an outright rejection.
You also want to keep track of attempts between connections to the database. The autonomous transaction is a perfect fit, due to the COMMITs required.
A retry mechanism allows you to specify the “item” on which you are placing a limit and keeping track of attempts.
These limits are maintained for each unique username. Here is the specification of this package:
CREATE OR REPLACE PACKAGE retry
IS
PROCEDURE incr_attempts (item IN VARCHAR2);
PROCEDURE set_limit (item IN VARCHAR2, limit IN INTEGER);
FUNCTION limit (item IN VARCHAR2) RETURN INTEGER;
FUNCTION limit_reached (item IN VARCHAR2) RETURN BOOLEAN;
PROCEDURE clear_attempts (item IN VARCHAR2);
FUNCTION attempts (item IN VARCHAR2) RETURN INTEGER;
FUNCTION attempts_left (item IN VARCHAR2) RETURN INTEGER;
FUNCTION attempted_at (item IN VARCHAR2) RETURN DATE;
PROCEDURE show_retries (item IN VARCHAR2 := '%');
END retry;
/
The programs are self-explanatory; the implementations are also very straightforward.
Here, for example, is the implementation of the procedure that lets you increment the number of attempts.
Notice the COMMITs and ROLLBACKs; these are required since the autonomous transaction pragma has been used.
PROCEDURE incr_attempts (item IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO retry_counter VALUES (
USER, incr_attempts.item, SYSDATE, 1);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE retry_counter
SET last_attempt = SYSDATE,
tries = tries + 1
WHERE username = USER
AND item = incr_attempts.item;
COMMIT;
WHEN OTHERS THEN ROLLBACK; RAISE;
END;
PRAGMA AUTONOUMOUS TRANSACTION RULES AND RESTRICIONS:
While it is certainly very easy to add the autonomous transaction pragma to your code,
there are some rules and restrictions on the use of this feature.
You can only make a top-level anonymous block an autonomous transaction. The following construction will work:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
myempno NUMBER;
BEGIN
INSERT INTO emp VALUES (myempno, ...);
COMMIT;
END;
/
whereas this construction:
DECLARE
myempno NUMBER;
BEGIN
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp VALUES (myempno, ...);
COMMIT;
END;
END;
/
results in this error:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here
You can now use COMMIT and ROLLBACK inside your database triggers. These actions will not affect the transaction that caused the database trigger to fire.
If an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), you can cause a deadlock to occur in your program. Here is a simple example to demonstrate the problem. A procedure to perform an update is created, and then called after already having all rows updated:
CREATE OR REPLACE PROCEDURE
update_salary (dept_in IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR myemps IS
SELECT empno FROM emp
WHERE deptno = dept_in
FOR UPDATE NOWAIT;
BEGIN
FOR rec IN myemps
LOOP
UPDATE emp SET sal = sal * 2
WHERE empno = rec.empno;
END LOOP;
COMMIT;
END;
/
BEGIN
UPDATE emp SET sal = sal * 2;
update_salary (10);
END;
/
This results in the following error:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
You cannot mark all subprograms in a package (or all methods in an object type) as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicitly in each program. For example, the following package specification is invalid:
CREATE PACKAGE warcrimes_pkg
AS
PRAGMA AUTONOMOUS_TRANSACTION;
PROCEDURE register (
culprit IN VARCHAR2, event IN VARCHAR2);
END warcrimes_pkg;
/
One consequence of this rule is that you cannot tell by looking at the package specification which, if any, programs will run as autonomous transactions.
To exit without errors from an autonomous transaction program, you must perform an explicit commit or rollback. If the program (or any program called by it) has transactions pending, the runtime engine will raise an exception—and then it will roll back those uncommitted transactions. Suppose, for example, that your job is to take over failing companies and make them profitable by firing employees. You would then want to use this procedure:
CREATE OR REPLACE PROCEDURE fire_em_all
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM emp;
END;
/
You want to make the program an autonomous transaction because you don’t want anyone to back out the changes when you are not looking. If you do not explicitly commit, when you run this procedure, the following error occurs:
SQL> exec fire_em_all
*
ERROR at line 1
ORA-06519: active autonomous transaction detected and rolled back
The COMMIT and ROLLBACK statements end the active autonomous transaction; they do not, however, force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.
An autonomous block is one in which autonomous transactions are expected. Zero, one, or more autonomous transactions can be executed within an autonomous block.
You can roll back only to savepoints marked in the current transaction. When you are in an autonomous transaction you cannot roll back to a savepoint set in the main transaction. If you try to do so, the runtime engine will raise this exception:
ORA-01086: savepoint 'your savepoint' never established
The TRANSACTIONS parameter in the Oracle initialization file (INIT.ORA) specifies the maximum number of transactions allowed concurrently in a session. If you use autonomous transactions (which run concurrently with the main transaction), you might exceed this number—and raise an exception—unless you raise the TRANSACTIONS value. This is the error you will get if you encounter this problem:
ORA-01574: maximum number of concurrent transactions exceeded
The default value for TRANSACTIONS in Oracle8i is 75.
Using Autonomous Transactions from Within SQL
Ever since Oracle 7.3, you have been able to call your own functions from within SQL—provided that you follow a variety of rules. The main one is this: you are not allowed to update the database. And you can’t save or cancel changes from within the function.
With the autonomous transaction feature, however, the picture changes. An autonomous transaction program never violates the two database-related purity levels, RNDS (reads no database state) and WNDS (writes no database state), even if that program actually does read from or write to the database. This is because those purity levels or constraints apply to the SQL statement (which, in this case, is the main transaction), yet an autonomous transaction’s DML actions never affect the main transaction.
As long as you define a program to be an autonomous transaction, you can also call it directly or indirectly in a SQL statement. If your program cannot assert another purity level, such as WNPS (writes no package state), you may be restricted from calling that program in certain parts of the SQL statement, such as the WHERE clause.
As an example, suppose that you want to keep a trace of all the rows that have been touched by a query. This table is created:
CREATE TABLE query_trace (
table_name VARCHAR2(30),
rowid_info ROWID,
queried_by VARCHAR2(30),
queried_at DATE
);
This function is then created to perform the audit:
CREATE OR REPLACE FUNCTION traceit (
tab IN VARCHAR2,
rowid_in IN ROWID)
RETURN INTEGER
IS
BEGIN
INSERT INTO query_trace VALUES (tab, rowid_in, USER, SYSDATE);
RETURN 0;
END;
/
When you try to use this function inside a query, you get the following error:
SQL> select ename, traceit ('emp', rowid) from emp;
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
However, if traceit is now transformed into an autonomous transaction by adding the pragma (and committing the results before the RETURN statement), the results are very different. The query works, and the query_trace table is filled:
SQL> SELECT ename, traceit ('emp', ROWID)
2 FROM emp;
ENAME TRACEIT('EMP',ROWID)
---------- --------------------
KING 0
...
MILLER 0
14 rows selected.
SQL>
SQL> SELECT table_name, rowid_info, queried_by,
2 TO_CHAR (queried_at, 'HH:MI:SS') queried_at
3 FROM query_trace;
TABLE_NAME ROWID_INFO QUERIED_BY QUERIED_AT
---------- ------------------ ---------- ----------
emp AAADEPAACAAAAg0AAA SCOTT 05:32:54
...
emp AAADEPAACAAAAg0AAN SCOTT 05:36:50
You have other options when it comes to tracing queries: you can write to the screen with the DBMS_OUTPUT built-in package or send information to a pipe with DBMS_PIPE. Now that autonomous transactions are available, if you do want to send information to a database table (or delete rows or update data, etc.), you can take that route instead, but be sure to analyze carefully the overhead of this approach.
Transaction Visibility
The default behavior of autonomous transactions is that once a COMMIT or ROLLBACK occurs in the autonomous transaction,
those changes are visible immediately in the main transaction.
Oracle offers a SET TRANSACTION statement option to hide those changes from the main transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
The default isolation level is READ COMMITTED, which means that as soon as changes are committed,
they are visible to the main transaction.
As is usually the case with the SET TRANSACTION statement, you must call it before you initiate your transactions
(i.e., issue any SQL statements); in addition, the setting affects your entire session, not just the current program.
The following script demonstrates the SERIALIZABLE isolation level at work.
First, an autonomous transaction procedure is created:
CREATE OR REPLACE PROCEDURE fire_em_all
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM emp2;
COMMIT;
END;
/
A script is run that sets the isolation level to SERIALIZABLE,
then the number of rows that appear in the emp2 table at the following times are displayed:
Before I call fire_em_all
After I call fire_em_all but before the main transaction is committed or rolled back
After I commit in the main transaction, here is the script that is run:
DECLARE
PROCEDURE showcount (str VARCHAR2) IS
num INTEGER;
BEGIN
SELECT COUNT(*) INTO num FROM emp2;
DBMS_OUTPUT.PUT_LINE (str || ' ' || num);
END;
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
showcount ('Before isolated AT delete');
fire_em_all;
showcount ('After isolated AT delete');
COMMIT;
showcount ('After MT commit');
END;
/
Here is the output from running the script:
Before isolated AT delete 14
After isolated AT delete 14
After MT commit 0
