The
AUTONOMOUS_TRANSACTION pragma
changes the way a subprogram works within a transaction. A subprogram marked
with this pragma can do SQL operations and commit or roll back those
operations, without committing or rolling back the data in the main transaction.
Autonomous transactions
allow you to leave the context of the calling transaction, perform an independent
transaction, and return to the calling transaction without affecting it's
state. The autonomous transaction has no link to the calling transaction, so
only committed data can be shared by both transactions.
Note:
Although an autonomous transaction is
started by another transaction, it is not a nested transaction, because:
- 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.
- 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.
PRAGMA
Signifies that the statement is a pragma (compiler directive).
Pragmas are processed at compile time, not at run time. They pass information
to the compiler.
Usage Notes
The following types of
PL/SQL blocks can be defined as autonomous transactions:
·
Top-level (not nested) anonymous PL/SQL blocks
·
Local, standalone, and packaged functions and procedures
·
Methods of a SQL object type
·
Database triggers
We cannot apply this pragma to an entire package or an entire an
object type. Instead, we can apply the pragma to each packaged subprogram or
object method.
Advantages of Autonomous Transactions
Once started, an autonomous transaction is fully independent. It
shares no locks, resources, or commit-dependencies with the main transaction.
We can log events, increment retry counters, and so on, even if the main
transaction rolls back.
Autonomous transactions help us to build modular, reusable
software components. We can encapsulate autonomous transactions in stored
subprograms. An invoking application needs not know whether operations done by
that stored subprogram succeeded or failed.
Unlike regular triggers, autonomous triggers can contain
transaction control statements such as
COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.Transaction Context
The main transaction shares its context with nested routines, but not with autonomous transactions. When one autonomous routine invokes another (or itself, recursively), the routines share no transaction context. When an autonomous routine invokes a nonautonomous 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. 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 we 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;
Note:
- Transaction properties apply only to the transaction in which they
are set.
- Cursor attributes are not affected by autonomous transactions.
In the main transaction, rolling back to a savepoint located
before the call to the autonomous subprogram 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. Oracle raises an exception in the autonomous
transaction, which is rolled back if the exception goes unhandled.
If we try to exit an active autonomous transaction without
committing or rolling back, Oracle raises an exception. If the exception goes
unhandled, or if the transaction ends because of some other unhandled
exception, the transaction is rolled back.
Declaring Autonomous Transactions
To declare an autonomous transaction, use the AUTONOMOUS_TRANSACTION pragma.
Tip:
For readability,
put the AUTONOMOUS_TRANSACTION pragma at the top of the declarative
section. (The pragma is allowed anywhere in the declarative section.)
We cannot apply the
AUTONOMOUS_TRANSACTION pragma to an
entire package or ADT, but we can apply it to each subprogram in a package or
each method of an ADT.
Example: Declaring Autonomous
Function in Package
CREATE OR REPLACE PACKAGE emp_actions AS -- package specification
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body
-- code for function raise_salary
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER(8,2);
BEGIN
UPDATE employees SET salary =
salary + sal_raise WHERE employee_id = emp_id;
COMMIT;
SELECT salary INTO new_sal FROM employees
WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;
/
Example: Declaring Autonomous Standalone Procedure
CREATE OR REPLACE PROCEDURE lower_salary
(emp_id NUMBER, amount NUMBER)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END lower_salary;
/
Example: Declaring Autonomous PL/SQL
Block (A nested PL/SQL block cannot be autonomous.)
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER(6) := 200;
amount NUMBER(6,2) := 200;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END;
/
Example: The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not committed.
CREATE TABLE
at_test (
id
NUMBER NOT NULL,
description
VARCHAR2(50) NOT NULL
);
INSERT INTO
at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO
at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM
at_test;
ID DESCRIPTION
----------
--------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8
rows using an anonymous block declared as an autonomous transaction, which
contains a commit statement.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure
successfully completed.
SELECT * FROM
at_test;
ID DESCRIPTION
----------
--------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10
rows in the table. If we now issue a rollback statement we get the following
result.
ROLLBACK;
SELECT * FROM
at_test;
ID DESCRIPTION
----------
--------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our
current session (transaction) have been rolled back, while the rows inserted by
the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own
transaction, so the internal commit statement did not affect the calling
session.
As a result rollback was
still able to affect the DML issued by the current statement.
Autonomous transactions are
commonly used by error logging routines, where the error messages must be
preserved, regardless of the commit/rollback status of the transaction.
For example, the following
table holds basic error messages.
CREATE TABLE
error_logs (
id
NUMBER(10) NOT NULL,
log_timestamp
TIMESTAMP NOT NULL,
error_message
VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);
CREATE SEQUENCE
error_logs_seq;
We define a procedure to log
error messages as an autonomous transaction.
CREATE OR REPLACE
PROCEDURE log_errors (p_error_message
IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs (id, log_timestamp,
error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP,
p_error_message);
COMMIT;
END;
/
The following code forces
an error, which is trapped and logged.
BEGIN
INSERT INTO at_test (id, description)
VALUES (998, 'Description for 998');
-- Force invalid insert.
INSERT INTO at_test (id, description)
VALUES (999, NULL);
EXCEPTION
WHEN OTHERS THEN
log_errors (p_error_message => SQLERRM);
ROLLBACK;
END;
/
PL/SQL procedure
successfully completed.
SELECT * FROM
at_test WHERE id >= 998;
no rows selected
SELECT * FROM
error_logs;
ID LOG_TIMESTAMP
----------
---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot
insert NULL into
("TIM_HALL"."AT_TEST"."DESCRIPTION")
1 row selected.
SQL>
From this we can see that
the LOG_ERRORS transaction was separate to the anonymous block. If it
weren't, we would expect the first insert in the anonymous block to be
preserved by the commit statement in the LOG_ERRORS procedure.
Be careful how we use
autonomous transactions. If they are used indiscriminately they can lead to
deadlocks, and cause confusion when analyzing session trace.
Here's a quote from Tom Kyte
"... in 999 times out of 1000, if you find
yourself "forced" to use an autonomous transaction - it likely means
you have a serious data integrity issue you haven't thought about.
Where do people try to use them?
- in that trigger
that calls a procedure that commits (not an error logging routine). Ouch,
that has to hurt when you rollback.
- in that trigger
that is getting the mutating table constraint. Ouch, that hurts *even
more*
Error logging - OK.
Almost everything else - not OK."
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 run since the last commit or rollback comprise the current transaction. To control autonomous transactions, use these statements, which apply only to the current (active) transaction:
·
COMMIT
·
ROLLBACK [TO savepoint_name]
·
SAVEPOINT savepoint_name
·
SET TRANSACTIONEntering and Exiting
When we enter the executable section of an autonomous routine, the main transaction suspends. When we exit the routine, the main transaction resumes.
If we try to exit an active autonomous transaction without committing or rolling back, the database raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
To exit normally, we must explicitly commit or roll back all autonomous transactions. If the routine (or any routine invoked by it) has pending transactions, PL/SQL raises an exception 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 can contain several autonomous
transactions, if it issues several COMMIT statements.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.
We can roll back only to savepoints marked in the current transaction. In an autonomous transaction, we cannot roll back to a savepoint marked in the main transaction. To do so, we must resume the main transaction by exiting the autonomous routine.
When in the main transaction, rolling back to a savepoint marked before we 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
We cannot run a PIPE ROW statement in our
autonomous routine while our autonomous transaction is open. We must close the
autonomous transaction before running the PIPE ROW statement. This is normally accomplished by committing or
rolling back the autonomous transaction before running the PIPE ROW statement.
To avoid some common errors, remember:
·
If an autonomous transaction attempts to access a resource
held by the main transaction, a deadlock can occur. The database raises an
exception in the autonomous transaction, which is rolled back if the exception
goes unhandled.
·
The database initialization parameter
·
If we try to exit an active autonomous transaction without
committing or rolling back, the database raises an exception.TRANSACTIONS specifies the
maximum number of concurrent transactions. That number might be exceeded
because an autonomous transaction runs concurrently with the main transaction.If the exception goes unhandled, the transaction is rolled back.
No comments:
Post a Comment