[Chapter 2] 2.4 Rules and Restrictions

2.4 Rules and Restrictions

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. This 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

Just to expand your vision of what is possible, 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, of course. See Section 2.5.2, "Using Autonomous Transactions in a Database Trigger , later in this chapter, for more details on what you can accomplish.

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. I create a procedure to perform an update, and then call it after having already updated all rows:

/* Filename on companion disk: autondlock.sql*/
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;
/

The results are not pretty:

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 my job in life is to take over failing companies and make them profitable by firing lots of employees. I would then want to carry around this handy procedure in my software toolbox:

CREATE OR REPLACE PROCEDURE fire_em_all
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DELETE FROM emp;
END;
/

I want to make the program an autonomous transaction because I don't want anyone to back out the changes when I am not looking. Unfortunately, I forget to explicitly commit. As a result, when I run this procedure, I get the following error:

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, in fact, have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.

TIP: 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, therefore, 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 Oracle8 i is 75.

2.4.1 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 certainly can't save or cancel changes from within the function.

With the autonomous transaction feature, however, the picture changes a good deal. 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. How can this be? 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.

So as long as you define a program to be an autonomous transaction, you can also call it directly or indirectly in a SQL statement. Of course, 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 I want to keep a trace of all the rows that have been touched by a query. I create this table:

/* Filename on companion disk: trcfunc.sf */
CREATE TABLE query_trace (
   table_name VARCHAR2(30),
   rowid_info ROWID,
   queried_by VARCHAR2(30),
   queried_at DATE
   );

I then create this simple function 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 I try to use this function inside a query, I get the expected 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 I now transform traceit into an autonomous transaction by adding the pragma (and committing my results before the RETURN statement!), the results are very different. My 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.

2.4.2 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. But what if you want to hide those changes from the main transaction? You want them saved or erased -- no question about that -- but that information should not be available to the main transaction.

Oracle offers a SET TRANSACTION statement option to achieve this effect.

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 (the autonserial.sql file will let you run these steps yourself).

First, I create my autonomous transaction procedure:

/* Filename on companion disk: autonserial.sql */
CREATE OR REPLACE PROCEDURE fire_em_all
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   DELETE FROM emp2;
   COMMIT;
END;
/

I run a script that sets the isolation level to SERIALIZABLE, then display the number of rows that appear in the emp2 table at the following times:

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 I 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