|
By
Arabinda Banerjee
What is an Autonomous Transaction?
Autonomous Transaction is a new feature in
ORACLE. It allows setting up independent transactions
that can be called from within other transactions.
It lets you suspend the main transaction (without
committing or rolling back), perform some DML
operations, commit or roll back those operations
(without any effect on the main transaction),
and then return to the main transaction.
Being independent of the main transaction
(almost like a separate session), an autonomous
transaction does not see the uncommitted changes
from the main transaction. It also does not
share locks with the main transaction. As a
result, it can get into a deadlock with its
parent … something the application developer
should watch out for.
As expected, changes committed by an autonomous
transaction are visible to other sessions/transactions
immediately, regardless of whether the main
transaction is committed or not. These changes
also become visible to the main transaction
when it resumes, provided its isolation level
is set to READ
COMMITTED (which is the default).
Defining Autonomous Transactions
A transaction can be marked as autonomous
by putting that transaction in a separate stored
procedure/function, or a packaged procedure/function,
or a local procedure inside a stored procedure,
or a separate database trigger. Anonymous PL/SQL
blocks and methods of SQL object type can also
be defined as autonomous transactions.
Any of these routines can be marked as autonomous
simply by using the following syntax anywhere
in the declarative section of the routine (putting
it at the top is recommended for better readability):
PRAGMA AUTONOMOUS_TRANSACTION;
Here is an example of defining a stored procedure
as autonomous:
CREATE PROCEDURE process_ord_line_shipment
(p_order_no number, p_line_no number) AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_char_1 varchar2(100);
BEGIN
...
END;
There are two restrictions on the use of this
PRAGMA: it cannot be used to mark ALL members
of a package as autonomous. Simple workaround:
mark each member (proc/function) as autonomous
individually. Also, a nested PL/SQL block cannot
be marked as autonomous. Again, the workaround
is not too bad: just make it a local procedure
(or a separate stored procedure).
The called routine should have its own commit
(or rollback). This is true even for a trigger
(which otherwise cannot have a commit or rollback
statement). An attempt to exit without committing
or rolling back the changes results in an exception
condition (and the pending changes are rolled
back as a result).
Advantages of Autonomous Transactions
Autonomous transactions are likely to be quickly
embraced by PL/SQL developers. They would find
their use in many situations where some DML's
needs to be saved regardless of whether or
main transaction commits or rolls back. Here
are a few examples:
- Transaction logging: A transaction
needs to be logged for audit purposes even
if it fails and does not update any other
data. An example of this is currently available
under the sample PL/SQL code section of the
Technet website (technet.oracle.com).
- Debugging (logging messages in an error-message
table). The procedure to insert a message
into a debug table should be set up as an
autonomous transaction so the messages are
saved even if the main transaction fails
or rolls back.
- Incrementing retry counters. It
is very similar to the transaction-logging
situation.
Apart from these technical needs, there is
a significant advantage of using autonomous
transactions from the point of view of programming
style and structure. Since autonomous transactions
can be committed or rolled back independent
of the main transaction, it will facilitate
development of more modular programs.
Here is an example of debugging application
(mentioned above) that demonstrates this very
well:
Procedure debug_write(p_err_msg in varchar2,
p_proc in varchar2, …)
is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Begin
Insert into debug messages (err_msg, proc_name, …)
Values(p_err_msg, p_proc, …);
Commit;
Exception
…
end;
END;
Once set up this way, it is a modular piece
of code that can be called from any procedure/function/trigger
without compromising the integrity of the calling
transaction.
This is a great benefit and will surely make
PL/SQL programming more fun.
|
