[20140802] 自治事务与死锁.txt
--今天一上班就遇到用户报机器慢的问题，连过去看，实际上是弹出一个错误窗口.
$ grep -B1 "ORA-00060: Deadlock detected. More info in file" alert_xxxx.log | egrep '2014' | cut -c1-10 | uniq -c
1 Fri Jan 3
.......
1 Mon Jul 21
1 Tue Jul 22
61 Wed Jul 23
248 Thu Jul 24
306 Fri Jul 25
86 Sat Jul 26
108 Sun Jul 27
246 Mon Jul 28
130 Tue Jul 29
117 Wed Jul 30
149 Thu Jul 31
204 Fri Aug 1
30 Sat Aug 2
--很明显问题大量出现在7月23号，但是用户为什么很少电话来反应问题呢？查看跟踪对应跟踪文件，很容易确定是由于触发器里面使用了
--自治事务而导致自锁的情况。
--实际上以前我也遇到过，再次根据这个问题写一个例子。
http://blog.itpub.net/267265/viewspace-721262/
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table tt (id number not null,status varchar2(1),name varchar2(10));
create unique index i_tt_id on tt(id);
create or replace trigger tri_tt_status
before insert or update on tt
for each row
declare
pragma autonomous_transaction;
begin
if :new.status = '1' then
:new.name :='y' ;
else
:new.name :='n' ;
end if ;
update tt set name=:new.name where id=:new.id;
commit;
end tri_tt_status;
/
SCOTT@test> insert into tt (id,status) values (1,'0');
1 row created.
SCOTT@test> commit ;
Commit complete.
--插入没有问题，因为记录还不存在。一旦修改。
SCOTT@test> select * from tt;
ID S NAME
---------- - --------------------
1 0 n
SCOTT@test> update tt set status='1' where id=1;
update tt set status='y' where id=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SCOTT.TRI_TT_STATUS", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRI_TT_STATUS'
SCOTT@test> rollback ;
Rollback complete.
SCOTT@test> select * from tt;
ID S NAME
---------- - --------------------
1 0 n
--删除修改以及commit语句。真不知道开发如何测试的,测试没有，真可怕！也许仅仅测试了插入，没有测试修改。
create or replace trigger tri_tt_status
before insert or update on tt
for each row
declare
-- pragma autonomous_transaction;
begin
if :new.status = '1' then
:new.name :='y' ;
else
:new.name :='n' ;
end if ;
-- update tt set name=:new.name where id=:new.id;
-- commit;
end tri_tt_status;
/
SCOTT@test> update tt set status='1' where id=1;
1 row updated.
SCOTT@test> select * from tt;
ID S NAME
---------- - --------------------
1 1 y
SCOTT@test> commit ;
Commit complete.
--插入看看，没有问题。
SCOTT@test> insert into tt (id,status) values (2,'1');
1 row created.
SCOTT@test> select * from tt;
ID S NAME
---------- - --------------------
2 1 y
1 1 y