AUTONOMOUS TRANSACTION April 2, 2008
Posted by haind in oracle.trackback
AUTONOMOUS TRANSACTION
(From http://www.expertsharing.com/2008/03/13/autonomous-transaction/)
Until Oracle 8i any Oracle session can handle only a single transaction at any given point of time. If a transaction “A” is started, no other transaction can be executed until the transaction “A” is commited or Rollbacked. But by the Autonomous transaction feature provided by Oracle, another transaction “B” can be completed suspending the Transaction “A” and resuming it after the completion of transaction “B”.
Transaction “A” can be termed as main transaction. Transaction “B” can be termed as Autonomous transaction.
An Autonomous transaction is executed as independent and out of the scope of main transaction.An autonomous transaction is a transaction that is started with in context of another transaction. An autonomous transaction can be committed or rolled back regardless of the main transaction.
An Autonomous transaction has to be defined in a Pl/sql block other than the block where main transaction is executed. You can define any Pl/sql block like anonymous block, procedure, function, package procedure, package function, database trigger as an autonomous transaction.
To define a Pl/sql block as an autonomous transaction , you can use the statement below in the declaration section of a PL/SQL block.
PRAGMA AUTONOMOUS_TRANSACTION;
You can put the autonomous transaction pragma anywhere in the declaration section of PL/SQL block. The above PRAGMA directs the pl/sql compiler to consider this block as an AUTONOMOUS TRANSACTION block.
Application of Autonomous transaction:
1. Autonomous transaction can be used as a logging mechanism to log the transaction specific data.
2. Autonomous transaction can be used to record the transaction state for a successful transaction or a failed transaction.
3.To implement commit and rollback mechanisms in database triggers.
4.Autonomous transaction can be used to develop standalone units of work. So Autonomous transaction can be helpful in building reusable components or reusable Modules.
Rules and Restrictions of using Autonomous transaction:
1. Only top level anonymous blocks can be declared as Autonomous transaction.
An example of Nested anonymous block declared as Autonomous transaction giving an error.
2. A deadlock can occur whenever an autonomous transaction tries to access a resourced which is held by the main transaction.
Ex:
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;
/
his results in the following error:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
3. You cannot declare an entire package as an Autonomous with Single Pragma.
CREATE OR REPLACE package exp3 as
PRAGMA AUTONOMOUS_TRANSACTION;
PROCEDURE X;
PROCEDURE Y;
END ;
/
4. Every Autonomous transaction should end with an either commit or ROLLBACK statements.If an autonomous block ends with out completing a transaction ORA-6519 is raised.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into tx_tab(num) values(2);
end;
/Error at line 1:
ORA-06519 : Active autonomous transaction detected and rolled back.
————More from PL/SQL best practice—————–
Doing Independent Units of Work with Autonomous Transactions
An autonomous transaction is an independent transaction started by another
transaction, the main transaction. Autonomous transactions do SQL operations and
commit or roll back, without committing or rolling back the main transaction. For
example, if you write auditing data to a log table, you want to commit the audit data
even if the operation you are auditing later fails; if something goes wrong recording
the audit data, you do not want the main operation to be rolled back.
Figure 6–1 shows how control flows from the main transaction (MT) to an
autonomous transaction (AT) and back again.
Figure 6–1 Transaction Control Flow
PROCEDURE proc1 IS
emp_id NUMBER;
BEGIN
emp_id := 7788;
INSERT … MT begins
SELECT …
proc2;
DELETE …
COMMIT; MT ends
END;
PROCEDURE proc2 IS
PRAGMA AUTON…
dept_id NUMBER;
BEGIN MT suspends
dept_id := 20;
UPDATE … AT begins
INSERT …
UPDATE …
COMMIT; AT ends
END; MT resumes
Advantages of Autonomous Transactions
Once started, an autonomous transaction is fully independent. It shares no locks,
resources, or commit-dependencies with the main transaction. You can log events,
increment retry counters, and so on, even if the main transaction rolls back.
More important, autonomous transactions help you build modular, reusable software
components. You can encapsulate autonomous transactions within stored procedures.
A calling application does not need to know whether operations done by that stored
procedure succeeded or failed.
Defining Autonomous Transactions
To define autonomous transactions, you use the pragma (compiler directive)
AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark a
routine as autonomous (independent). In this context, the term routine includes
■ Top-level (not nested) anonymous PL/SQL blocks
■ Local, standalone, and packaged functions and procedures
■ Methods of a SQL object type
■ Database triggers
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. The syntax follows:
PROCEDURE proc1 IS
emp_id NUMBER;
BEGIN
emp_id := 7788;
INSERT … MT begins
SELECT …
proc2;
DELETE …
COMMIT; MT ends
END;
PROCEDURE proc2 IS
PRAGMA AUTON…
dept_id NUMBER;
BEGIN MT suspends
dept_id := 20;
UPDATE … AT begins
INSERT …
UPDATE …
COMMIT; AT ends
END; MT resumes
Main Transaction Autonomous Transaction
Doing Independent Units of Work with Autonomous Transactions
6-36 PL/SQL User’s Guide and Reference
PRAGMA AUTONOMOUS_TRANSACTION;
In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS
…
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
CREATE PACKAGE BODY banking AS
…
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
…
END;
END banking;
Restriction: 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.
The next example marks a standalone procedure as autonomous:
CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN … END;
The following example marks a PL/SQL block as autonomous:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
my_empno NUMBER(4);
BEGIN … END;
Restriction: You cannot mark a nested PL/SQL block as autonomous.
The example below marks a database trigger as autonomous. Unlike regular triggers,
autonomous triggers can contain transaction control statements such as COMMIT and
ROLLBACK.
CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; — allowed only in autonomous triggers
END;
Comparison of Autonomous Transactions and Nested Transactions
Although an autonomous transaction is started by another transaction, it is not a
nested transaction:
■ It does not share transactional resources (such as locks) with the main transaction.
■ It does not depend on the main transaction. For example, if the main transaction
rolls back, nested transactions roll back, but autonomous transactions do not.
Doing Independent Units of Work with Autonomous Transactions
Performing SQL Operations from PL/SQL 6-37
■ Its committed changes are visible to other transactions immediately. (A nested
transaction’s committed changes are not visible to other transactions until the
main transaction commits.)
■ Exceptions raised in an autonomous transaction cause a transaction-level rollback,
not a statement-level rollback.
Transaction Context
The main transaction shares its context with nested routines, but not with autonomous
transactions. When one autonomous routine calls another (or itself recursively), the
routines share no transaction context. When an autonomous routine calls a
non-autonomous routine, the routines share the same transaction context.
Transaction Visibility
Changes made by an autonomous transaction become visible to other transactions
when the autonomous transaction commits. These changes become visible to the main
transaction when it resumes, if its isolation level is set to READ COMMITTED (the
default).
If you set the isolation level of the main transaction to SERIALIZABLE, changes made
by its autonomous transactions are not visible to the main transaction when it resumes:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Controlling Autonomous Transactions
The first SQL statement in an autonomous routine begins a transaction. When one
transaction ends, the next SQL statement begins another transaction. All SQL
statements executed since the last commit or rollback make up the current transaction.
To control autonomous transactions, use the following statements, which apply only to
the current (active) transaction:
■ COMMIT
■ ROLLBACK [TO savepoint_name]
■ SAVEPOINT savepoint_name
■ SET TRANSACTION
Note: Transaction properties set in the main transaction apply only to that transaction,
not to its autonomous transactions, and vice versa.
Entering and Exiting
When you enter the executable section of an autonomous routine, the main transaction
suspends. When you exit the routine, the main transaction resumes.
To exit normally, you must explicitly commit or roll back all autonomous transactions.
If the routine (or any routine called by it) has pending transactions, an exception is
raised, and the pending transactions are rolled back.
Committing and Rolling Back
COMMIT and ROLLBACK end the active autonomous transaction but do not exit the
autonomous routine. When one transaction ends, the next SQL statement begins
another transaction. A single autonomous routine could contain several autonomous
transactions, if it issued several COMMIT statements.
Doing Independent Units of Work with Autonomous Transactions
6-38 PL/SQL User’s Guide and Reference
Using Savepoints
The scope of a savepoint is the transaction in which it is defined. Savepoints defined in
the main transaction are unrelated to savepoints defined in its autonomous
transactions. In fact, the main transaction and an autonomous transaction can use the
same savepoint names.
You can roll back only to savepoints marked in the current transaction. In an
autonomous transaction, you cannot roll back to a savepoint marked in the main
transaction. To do so, you must resume the main transaction by exiting the
autonomous routine.
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.
Avoiding Errors with Autonomous Transactions
To avoid some common errors, keep the following points in mind:
■ If an autonomous transaction attempts to access a resource held by the main
transaction, a deadlock can occur. Oracle raises an exception in the autonomous
transaction, which is rolled back if the exception goes unhandled.
■ The Oracle initialization parameter TRANSACTIONS specifies the maximum
number of concurrent transactions. That number might be exceeded because an
autonomous transaction runs concurrently with the main transaction.
■ 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.
Using Autonomous Triggers
Among other things, you can use database triggers to log events transparently.
Suppose you want to track all inserts into a table, even those that roll back. In the
example below, you use a trigger to insert duplicate rows into a shadow table. Because
it is autonomous, the trigger can commit changes to the shadow table whether or not
you commit changes to the main table.
– create a main table and its shadow table
CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15));
CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15));
– create an autonomous trigger that inserts into the
– shadow table before each insert into the main table
CREATE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT;
END;
– insert a row into the main table, and then commit the insert
INSERT INTO parts VALUES (1040, ‘Head Gasket’);
COMMIT;
– insert another row, but then roll back the insert
INSERT INTO parts VALUES (2075, ‘Oil Pan’);
Doing Independent Units of Work with Autonomous Transactions
Performing SQL Operations from PL/SQL 6-39
ROLLBACK;
– show that only committed inserts add rows to the main table
SELECT * FROM parts ORDER BY pnum;
PNUM PNAME
——- —————
1040 Head Gasket
– show that both committed and rolled-back inserts add rows
– to the shadow table
SELECT * FROM parts_log ORDER BY pnum;
PNUM PNAME
——- —————
1040 Head Gasket
2075 Oil Pan
Unlike regular triggers, autonomous triggers can execute DDL statements using native
dynamic SQL (discussed in Chapter 7, “Performing SQL Operations with Native
Dynamic SQL”). In the following example, trigger bonus_trig drops a temporary
database table after table bonus is updated:
CREATE TRIGGER bonus_trig
AFTER UPDATE ON bonus
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — enables trigger to perform DDL
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE temp_bonus’;
END;
For more information about database triggers, see Oracle Database Application
Developer’s Guide – Fundamentals.
Calling Autonomous Functions from SQL
A function called from SQL statements must obey certain rules meant to control side
effects. (See “Controlling Side Effects of PL/SQL Subprograms” on page 8-22.) To check
for violations of the rules, you can use the pragma RESTRICT_REFERENCES. The
pragma asserts that a function does not read or write database tables or package
variables. (For more information, See Oracle Database Application Developer’s Guide -
Fundamentals.)
However, by definition, autonomous routines never violate the rules “read no database
state” (RNDS) and “write no database state” (WNDS) no matter what they do. This can be
useful, as the example below shows. When you call the packaged function log_msg
from a query, it inserts a message into database table debug_output without
violating the rule “write no database state.”
– create the debug table
CREATE TABLE debug_output (msg VARCHAR2(200));
– create the package spec
CREATE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;
– create the package body
CREATE PACKAGE BODYq debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
Doing Independent Units of Work with Autonomous Transactions
6-40 PL/SQL User’s Guide and Reference
BEGIN
– the following insert does not violate the constraint
– WNDS because this is an autonomous routine
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END;
END debugging;
– call the packaged function from a query
DECLARE
my_empno NUMBER(4);
my_ename VARCHAR2(15);
BEGIN
…
SELECT debugging.log_msg(ename) INTO my_ename FROM emp
WHERE empno = my_empno;
– even if you roll back in this scope, the insert
– into ‘debug_output’ remains committed because
– it is part of an autonomous transaction
IF … THEN
ROLLBACK;
END IF;
END;
——————-From Metalink——————————-
| Oracle 8.1.5 and higher : Autonomous Transactions | ||||
| Doc ID: | Note:65961.1 | Type: | FAQ | |
| Last Revision Date: | 08-OCT-2007 | Status: | PUBLISHED | |
"Checked for relevance on 08-Oct-2007"
Introduction
------------
Oracle Support analysts are often asked whether it is possible to
commit to a given savepoint in the same way that one may roll back to
a savepoint. The question usually stems from a desire to be able to
have some operation take place independently of the current
transaction, for instance to allow error messages written to a table
to be committed, but to roll back everything else that has taken place
prior to the error.
With Oracle8i we have not introduced "commit to savepoint" as such
but have provided the ability to temporarily suspend the current
transaction and begin another. This second transaction is known as
an autonomous transaction and, as the name suggests, runs
independently of its parent. The autonomous or child transaction can
commit or rollback as applicable with the execution of the parent
transaction being resumed upon its completion. The parent may then
perform further operations and commit or rollback without affecting
the outcome of any operations performed within the child.
Thus, the introduction of autonomous transactions allows users to
more easily develop modular, reusable components. Calling
applications no longer need to be aware of what components do
internally and the components themselves need no knowledge of which
operations an application has performed prior to calling, making
this feature of particular interest to Cartridge Developers.
In fact, Oracle already uses similar functionality internally, known as
recursive transactions, to handle the updating of system resources.
For example, when one application selects "nextval" from a non-cached
sequence, the value is immediately incremented in the database. Thus,
a second application will always get the incremented value from the
sequence, regardless of whether the first application has committed or
rolled back.
A. Defining Autonomous Transactions
------------------------------------
In the current release of Oracle8i (8.1), autonomous transactions are
provided through PL/SQL only, though the intention is to extend the
capability to OCI in later releases.
A PL/SQL routine is marked as autonomous by placing the following
pragma (compiler directive) anywhere within its declare section:
pragma AUTONOMOUS_TRANSACTION;
The pragma may appear in the declare section of any of the following:
(i) top-level anonymous blocks
(ii) local, standalone or packaged functions and procedures
(iii) methods of object types
(iv) database triggers
It may not appear:
(i) outside of a declare section
(ii) within the declare section of a nested block (i.e., a block
within a block)
(iii) in a package specification
(iv) in a package body outside of a procedure or function
definition
(v) in a type body outside of a method definition
B. Transaction Control Flow
---------------------------
When a transaction is in progress, and a code unit declared as an
autonomous transaction is encountered, the following happens:
(i) The parent (or main) transaction remains active while any
statements specified in the declare section of the autonomous unit
are executed.
Note that statements physically positioned after the pragma in the
declare section are run as part of the parent transaction, just as
exceptions generated in a declare section are always caught in the
calling routine's handler.
(ii) At the point that the first executable step after the begin
is reached, the parent transaction is suspended and a new one is
started.
(iii) The code unit is executed as normal but with its transaction
context set to the new transaction.
(iv) If a commit or rollback is executed in the code unit, the
autonomous transaction is ended; the main transaction is still
suspended at this point. If any further operations are performed
within the autonomous unit after this point, then a new transaction is
started.
(v) As the code unit exits and control returns to the parent, the
main transaction is resumed and the transaction context is switched
back to the parent.
Nesting Autonomous Transactions
--------------------------------
Autonomous transactions may be nested. If an autonomous transaction
is already in progress when another is encountered, then the current
one is suspended and a new one started. The limit on the number of
suspended transactions is governed by the initialization parameter
"transactions".
C. Scope and Visibility
-----------------------
Variables
---------
Just like any other code units, those declared as autonomous
transactions have the same scoping rules for accessing global and
local variables. If variables are in the scope of both the parent and
the child, then any values set in the parent are visible to the
child since variables are not controlled by the transaction context.
Similarly, if a child updates a variable in the parent's scope, then
the parent sees that change when its execution resumes.
Session Parameters
------------------
Session parameters are also not affected by transaction context.
Therefore, an alter session performed during the parent transaction is
applicable to the child transaction and vice versa.
Database Changes
----------------
As all database changes are part of a transaction, if a parent has
modified data, but not committed it at the point the autonomous
transaction begins, then those modifications are not visible to
the child. Whether changes committed as part of a completed child
transaction are visible to the parent or not depends upon the
isolation level of the parent. See "Transaction Control Commands"
below.
Locks
-----
Because the parent and child transactions are independent, they
also are not be able to share any locks; if a parent transaction has a
resource locked that a child attempts to obtain, then a deadlock
situation occurs. In this case, the offending statement is
automatically rolled back with an "ORA-00060: deadlock detected while
waiting for resource" exception raised within the child.
Examples
--------
Assume the following has been run:
create table msg (msg varchar2(120));
1. First, some PL/SQL code that does not use autonomous transactions:
declare
var1 number := 0; --} Global variables
cnt number := -1; --}
procedure local is
begin
if cnt = -1 then
dbms_output.put_line('var1 in local is '||var1);
var1 := var1*10;
end if;
select count(*) into cnt from msg;
dbms_output.put_line('local: # of rows is '||cnt);
insert into msg values ('New Record');
commit;
end;
begin
var1 := 2;
insert into msg values ('Row 1');
local;
dbms_output.put_line('var1 in main is '||var1);
select count(*) into cnt from msg;
dbms_output.put_line('main: # of rows is '||cnt);
rollback;
local;
insert into msg values ('Row 2');
commit;
local;
select count(*) into cnt from msg;
dbms_output.put_line('main: # of rows is '||cnt);
end;
Running this results in:
var1 in local is 2 -> var1 is visible in local
local: # of rows is 1 -> local can see the uncommitted row
var1 in main is 20 -> new value of var1 is visible in main block
main: # of rows is 2 -> main block sees new row; both are committed
local: # of rows is 2 -> rollback has no effect
local: # of rows is 4 -> local can see rows added in main block and
from its previous execution
main: # of rows is 5 -> main sees all rows
Running the same block but with the local procedure declared as
an autonomous transaction as follows:
...
procedure local is
pragma AUTONOMOUS_TRANSACTION;
begin
...
results in:
var1 in local is 2 -> var1 is visible in local
local: # of rows is 0 -> local cannot see the uncommitted row
inserted in the main block
var1 in main is 20 -> new value of var1 is visible in main block
main: # of rows is 2 -> main block sees new row also; only one row
is committed
local: # of rows is 1 -> local sees the row it inserted last time;
row inserted in main block is rolled back
local: # of rows is 3 -> local sees its rows plus the one committed
in the main block
main: # of rows is 4 -> main sees all rows
2. This example demonstrates how statements executed in the declare
section are run in the scope of the parent. With the following
function created:
create or replace function cntmsg return number as
cnt number := 0;
begin
select count(*) into cnt from msg;
return cnt;
end;
running:
insert into msg values ('Hello');
declare
pragma AUTONOMOUS_TRANSACTION;
x number := cntmsg;
begin
dbms_output.put_line('Number rows = '||x);
x := cntmsg;
dbms_output.put_line('Number rows = '||x);
rollback;
end;
commit;
results in:
Number rows = 1 -> function called in the declare section sees
the row inserted in the parent transaction
Number rows = 0 -> function called in the body is unable to
see the row
with 1 row inserted into the table.
D. Transaction Control
----------------------
Exiting an Autonomous Transaction
---------------------------------
As soon as an autonomous code unit has made any database changes,
locked resources or issued transaction control statements such as set
transaction or savepoint, the transaction is said to be
active. This means that the unit must either explicitly issue a
commit or a full rollback before exiting back to the parent
transaction, or perform an implied commit by issuing a DDL (Data
Definition Language) statement.
It is not sufficient to simply roll back to a savepoint, even if doing
so would leave no outstanding database locks or changes. If no commit
or rollback is done before exiting, then at the point of executing the
"return" or "end" statement, the whole autonomous transaction is rolled
back with the following error raised in the child:
ORA-06519: active autonomous transaction detected and rolled back
If the code unit does not currently have an active transaction in
progress, then it may successfully exit without issuing a commit or
rollback.
Error Handling
--------------
If an autonomous code unit exits in error with an active transaction
in progress, then that transaction is automatically rolled back.
For example, consider that the following function fails upon
execution:
create or replace procedure atx_fail as
pragma AUTONOMOUS_TRANSACTION;
x number;
begin
insert into msg values ('Hello');
x := 'AAA'; -- will generate invalid number error
commit;
end;
Running:
begin
insert into msg values ('Bye');
atx_fail;
exception
when others then
commit;
end;
results in only the row containing "Bye" being written to the
table. If atx_fail was not an autonomous transaction, then the above
example results in both rows being inserted. Similarly, if the
autonomous procedure handled the error itself, committed and exited
successfully back to the parent, then again, both rows are present
in the table upon completion.
Transaction Control Commands
----------------------------
(i) Savepoints
Savepoint and rollback to savepoint commands may be issued within
autonomous transactions. As an autonomous transaction is independent
of the parent transaction context, it is not possible for the child
to roll back to a savepoint in the parent. This also means that the
same savepoint names may be used in both the parent and the child
transactions.
For example, running:
savepoint A;
insert into msg values ('aaa');
declare
pragma AUTONOMOUS_TRANSACTION;
cnt number;
begin
insert into msg values ('bbb');
savepoint A;
insert into msg values ('ccc');
rollback to savepoint A;
insert into msg values ('ddd');
commit;
end;
results in three rows, "aaa", "bbb" and "ddd", in the table.
Issuing:
rollback to savepoint A;
causes row "aaa" to be rolled back.
(ii) Set Transaction and Isolation Level
Set transaction commands may be issued within autonomous transactions
with the same effect as in regular transactions. Setting the
isolation level within a parent transaction governs whether or
not the parent is able to see changes made by the child.
By default, the isolation level is set to "read committed". Thus, if a
child transaction (or separate session) commits changes, the parent
is able to query back those changes immediately. By setting
isolation level to "serializable" in the parent, changes
committed prior to the set transaction command are visible to the
parent, thus, providing a read consistent snapshot at that point in time.
Set transaction commands issued in the parent apply only to that
transaction. Thus, a parent may issue a "set transaction read only"
statement and then invoke a child transaction to perform updates.
E. Invoking Autonomous Transactions via SQL
-------------------------------------------
As a result of issuing SQL, an autonomous transaction can be invoked
in one of three ways:
(i) a trigger fires as part of an update, insert or delete
(ii) a stored function is called from a DML (Data Manipulation
Language) statement
(iii) a method is called directly from a DML statement on an object
column or table, or invoked implicitly as a map or order
method
Triggers
--------
A trigger itself may be declared as an autonomous transaction. For
example:
create or replace trigger trig1 after insert on tab1 for each row
declare
pragma AUTONOMOUS_TRANSACTION;
...
begin
...
end;
or may invoke a procedure or function that is itself autonomous.
This means that as a result of an insert into one table, updates
may be performed on other tables and the changes saved regardless of
the final outcome of the insert.
It also means that it is possible to access the triggering table without
getting a "mutating table" error when the triggering operation performed
affects multiple rows. (In the past, if an operation affected more than
one row, then any triggers firing at row level within the same transaction
were not permitted to query the table that caused the trigger to fire in
the first place.)
It is important to remember that if a trigger is running as an
autonomous transaction, although it still has access to
:OLD and :NEW values as appropriate, it does not see any rows
inserted into the table by the calling transaction. Thus, using an
autonomous trigger to obtain the maximum value currently in the
table, in order to set a primary key column, is unlikely to work.
Using an autonomous trigger to maintain an audit trail of data
changes is possible though.
Functions and Methods
---------------------
Functions and type methods may be used within SQL statements directly,
provided they are shown to have a certain level of purity. The purity
required in this case is a guarantee not to write to the database.
Any code run as part of an autonomous transaction is always deemed
not to read from or write to the database, regardless of which
operations it performs. This means a query can now call a routine
that may update, insert, or delete data as required.
Examples
--------
1. The following is a somewhat dangerous example:
create or replace package p as
function delmsg (p1 in varchar2) return number;
-- A full purity level is permitted because WNDS and RNDS
-- are implicit by the fact that the function is declared
-- as autonomous. Thus the compiler only needs to check
-- operations performed in the declare section plus ensure
-- the body of the function touches no package variables.
--
-- WNDS = writes no database state
-- RNDS = reads no database state
-- WNPS = writes no package state
-- RNPS = reads no package state
--
pragma restrict_references(delmsg,WNDS,RNDS,WNPS,RNPS);
end;
create or replace package body p as
function delmsg (p1 in varchar2) return number as
pragma AUTONOMOUS_TRANSACTION;
begin
delete from msg where msg = p1;
commit;
return 1;
end;
end;
insert into msg values ('Hello');
insert into msg values ('Bye');
commit;
select msg, delmsg(msg) ret from msg;
Although rows are returned, they are deleted at the same time leaving the
table empty.
2. This example defines a method that calculates a person's current
age and then updates the table accordingly if their age in the
database is not correct:
create or replace type peops as object (
name varchar2(50),
dob date,
age number,
member function get_age return number,
pragma restrict_references(get_age,WNDS,RNDS,WNPS,RNPS)
);
create table peops_tab of peops;
create or replace type body peops as
member function get_age return number is
pragma AUTONOMOUS_TRANSACTION;
lage number := 0;
begin
lage := trunc( (sysdate-self.dob)/365 );
if lage != nvl(age,0) then
update peops_tab set age = lage where name = self.name;
commit;
end if;
return lage;
end;
end;
insert into peops_tab values
('Sharon',to_date('08/04/66','dd/mm/rr'),30);
insert into peops_tab values
('George',to_date('10/01/72','dd/mm/rr'),null);
insert into peops_tab values
('Fred',to_date('17/03/84','dd/mm/rr'),14);
commit;
select p.get_age() from peops_tab p;
This results in the correct ages being returned and also written to the
table.
F. Distributed Transactions
---------------------------
Autonomous transactions are not currently supported as part of distributed
transactions.
G. Autonomous Transactions and 3GLs
-----------------------------------
Autonomous transactions may be invoked from 3GLs such as PRO*C and
OCI in the same way they may be invoked from say SQL*Plus, i.e., by:
(i) calling stored program units declared as autonomous
(ii) executing SQL that results is an autonomous trigger, function
or method firing
(iii) executing locally declared autonomous PL/SQL blocks. For
example from PRO*C:
EXEC SQL EXECUTE DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
END;
END-EXEC;
H. Miscellaneous Examples
------------------------------
This section looks at some further examples.
1. This example demonstrates sharing a cursor between a parent and child
transaction. Note that although the parent has not committed its inserts,
the rows are still visible within the autonomous transaction since it is
fetching the data via a cursor opened by the parent:
create table msg (msg varchar2(120));
insert into msg values ('Row 1');
insert into msg values ('Row 2');
insert into msg values ('Row 3');
insert into msg values ('Row 4');
set serveroutput on
declare
cursor c1 is select * from msg;
ret varchar2(20);
procedure local is
pragma AUTONOMOUS_TRANSACTION;
begin
fetch c1 into ret;
dbms_output.put_line(ret);
insert into msg values ('Row n');
commit;
end;
begin
open c1;
fetch c1 into ret;
dbms_output.put_line(ret);
local;
fetch c1 into ret;
dbms_output.put_line(ret);
local;
close c1;
end;
gives:
Row 1
Row 2
Row 3
Row 4
with 6 rows currently present in the table.
2. This example shows how a LOB locator can be passed from a parent
transaction to an autonomous one without losing its context. By
passing the locator as a parameter, the parent allows the child
access to uncommitted data; that same data is not visible when
selected from the table directly within the child:
create table test_lobs (c1 number,c2 clob);
create table msg (msg varchar2(120));
create or replace function getlen (p1 in clob,
p2 in number) return number as
pragma AUTONOMOUS_TRANSACTION;
len number;
buf varchar2(120);
tlen number;
begin
len := dbms_lob.getlength(p1);
if len != 0 then
dbms_lob.read(p1,len,1,buf);
dbms_output.put_line('Value: '||buf);
insert into msg values (buf);
commit;
end if;
select dbms_lob.getlength(c2) into tlen
from test_lobs where c1=p2;
dbms_output.put_line('Length selected from table is '||tlen);
return len;
end;
insert into test_lobs values (1,'Hello');
insert into test_lobs values (2,'The quick brown fox');
commit;
declare
cl1 clob;
cl2 clob;
ret number;
begin
select c2 into cl1 from test_lobs where c1 = 1 for update;
select c2 into cl2 from test_lobs where c1 = 2;
ret := getlen(cl1,1);
dbms_output.put_line('Length of first row is '||ret);
ret := getlen(cl2,2);
dbms_output.put_line('Length of second row is '||ret);
dbms_lob.writeappend(cl1,6,' there');
ret := getlen(cl1,1);
dbms_output.put_line('Length of first row is now '||ret);
end;
gives:
Value: Hello
Length selected from table is 5
Length of first row is 5
Value: The quick brown fox
Length selected from table is 19
Length of second row is 19
Value: Hello there
Length selected from table is 5
Length of first row is now 11
Selecting from the msg table gives three rows as follows:
Hello
The quick brown fox
Hello there
References
----------
PL/SQL User's Guide and Reference, Interaction with Oracle
Application Developer's Guide - Fundamentals, Processing SQL Statements
Comments»
No comments yet — be the first.