Oracle Autonomous transaction自治事务可在PL/SQL块中定义,适用于以下范围:
l Stored procedure or function
l Local procedure or function
l Package
l Type method
l Top-level anonymous block
此功能从Oracle 8i(Oracle 8.1.5 and higher)出现,用pragma AUTONOMOUS_TRANSACTION实现,使用时当前事务会被悬挂(suspend),同时Oracle会另开一个自治事务。自治事务从当前事务开始,在其自身的语境中执行,它能独立地被提交或重新运行,而不影响正在运行的事务,即不影响主事务的ACID特性。所以自治事务常被用于记录程序运行日志或者debug异常定位等方面。
以下举例说明:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t (id number,name varchar2(10));
Table created.
SQL> create table t_monitor_log (logtext varchar2(20));
Table created.
SQL> insert into t values(1,'a');
1 row created.
SQL> insert into t values(2,'b');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
(1)自治事务和主事务相分离
SQL> create or replace procedure p_autonomous is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into t_monitor_log values('insert test 1');
5 commit;
6 end;
7 /
Procedure created.
SQL> create or replace procedure p_test is
2 begin
3 insert into t values(3,'c');
4 p_autonomous;
5 rollback;
6 end;
7 /
Procedure created.
SQL> exec p_test;
PL/SQL procedure successfully completed.
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
SQL> select * from t_monitor_log;
LOGTEXT
----------------------------------------
insert test 1
这里主事务被回滚,但是自治事务正常提交。
(2)进一步证明
这里,我模拟一个主事务出错的例子,导致主事务失败,但自治事务正常提交。
SQL> create or replace procedure p_autonomous is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into t_monitor_log values('insert test 2');
5 commit;
6 end;
7 /
Procedure created.
SQL> create or replace procedure p_test is
2 begin
3 update t set name='aa' where id=1;
4 p_autonomous;
5 update t set name='bb' where id=2;
6 commit;
7 end;
8 /
Procedure created.
这里我主要是想模拟死锁,所以我另开了一个会话2,执行update操作:
SQL> update t set name='bbb' where id=2;
1 row updated.
update后不提交,切换回原来会话,执行procedure:
SQL> exec p_test;
会话hang住,切换回会话2
SQL> update t set name='aaa' where id=1;
1 row updated.
这时,原会话出现死锁报错
BEGIN p_test; END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST.P_TEST", line 5
ORA-06512: at line 1
会话2将事务都回滚:
SQL> rollback;
Rollback complete.
这里可以看到,自治事务确实正常提交。
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
SQL> select * from t_monitor_log;
LOGTEXT
----------------------------------------
insert test 1
insert test 2
(3)在PL/SQL匿名块中,自治事务必须完成(提交或回滚),主事务没有要求
SQL> create or replace procedure p_autonomous is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into t_monitor_log values('insert test 3');
5 end;
6 /
Procedure created.
SQL> create or replace procedure p_test is
2 begin
3 insert into t values(3,'c');
4 p_autonomous;
5 end;
6 /
Procedure created.
这里创建2个都没有commit和rollback的procedure,测试运行一下:
SQL> exec p_test;
BEGIN p_test; END;
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "TEST.P_AUTONOMOUS", line 5
ORA-06512: at "TEST.P_TEST", line 4
ORA-06512: at line 1
出现报错,autonomous transaction被回滚。
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
SQL> select * from t_monitor_log;
LOGTEXT
----------------------------------------
insert test 1
insert test 2
稍作修改autonomous transaction,加上commit
SQL> create or replace procedure p_autonomous is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into t_monitor_log values('insert test 3');
5 commit;
6 end;
7 /
Procedure正常完成
SQL> exec p_test;
PL/SQL procedure successfully completed.
所以在自治事务编写时,事务必须完成,不过主事务没这个要求。
虽然没这个要求,但并不代表主事务已正常完成。
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
3 c
SQL> select * from t_monitor_log;
LOGTEXT
----------------------------------------
insert test 1
insert test 2
insert test 3
SQL> rollback;
Rollback complete.
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
SQL> select * from t_monitor_log;
LOGTEXT
----------------------------------------
insert test 1
insert test 2
insert test 3
当前的事务被回滚了,这也说明了procedure执行完成时默认是不会结束事务的。
(4)自锁造成ORA-60报错
这是一个很有趣的现象
SQL> create or replace procedure p_autonomous is
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into t_monitor_log values('insert test 4');
5 update t set name='aaa' where id=1;
6 commit;
7 end;
8 /
Procedure created.
SQL> create or replace procedure p_test is
2 begin
3 update t set name='aa' where id=1;
4 p_autonomous;
5 commit;
6 end;
7 /
Procedure created.
SQL> exec p_test;
BEGIN p_test; END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST.P_AUTONOMOUS", line 5
ORA-06512: at "TEST.P_TEST", line 4
ORA-06512: at line 1
出现死锁了,不过这次是自己锁自己,alert_log信息:
Sun Apr 1 14:55:13 2012
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/admin/TESTDB/udump/testdb_ora_909412.trc.
看看trace中的Deadlock graph
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00060013-00001d58 13 35 X 13 35 X
session 35: DID 0001-000D-000005EB session 35: DID 0001-000D-000005EB
要是不看实验过程,这个自己锁自己的现象,还真让人摸不着头脑呢。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-720170/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20750200/viewspace-720170/