Autonomous Transactions
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.
The following types of PL/SQL blocks can be defined as
autonomous transactions:
Stored procedures and functions.
Local procedures and functions
defined in a PL/SQL declaration block.
Packaged procedures and
functions.
Type methods.
Top-level anonymous blocks.
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 commited.
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 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 you use autonomous transactions. If they are
used indiscriminately they can lead to deadlocks, and cause confusion when
analyzing session trace. To hammer this point home, here's a quote from Tom
Kyte posted on my blog (here):
"... 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."
No comments:
Post a Comment