|
Chapter 2 Choose Your Transaction! |
|
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.
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.
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
|
|
|
|
| 2.3 When to Use Autonomous Transactions |
|
2.5 Examples |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.