insert引起的死锁

alert 日志中发现以下报错信息：

Wed Jun  3 11:02:14 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18786.trc.

 

文件/u01/product/admin/wind/udump/wind_ora_18786.trc中的信息：

 

*** 2009-06-03 11:01:08.476
*** SERVICE NAME:(SYS$USERS) 2009-06-03 11:01:08.379
*** SESSION ID:(1093.52999) 2009-06-03 11:01:08.379
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2) VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00020043-0000ee39        40    1093     X             36    1066           S
TX-00030015-0000f070        36    1066     X             40    1093           S
session 1093: DID 0001-0028-00011683    session 1066: DID 0001-0024-00000014
session 1066: DID 0001-0024-00000014    session 1093: DID 0001-0028-00011683
Rows waited on:
Session 1066: obj - rowid = 00017071 - AAAXBxABFAAADYjAAA
  (dictionary objn - 94321, file - 69, block - 13859, slot - 0)
Session 1093: obj - rowid = 000171FD - AAAXH9ABZAAAAM1AAA
  (dictionary objn - 94717, file - 89, block - 821, slot - 0)
Information on the OTHER waiting sessions:
Session 1066:
  pid=36 serial=1171 audsid=2795304 user: 40/PDM8
  O/S info: user: , term: , ospid: 1234, machine: pdm8ap1
            program:
  Current SQL Statement:

INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2) VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)
End of information on OTHER waiting sessions.

 

ControlBranch表有一个PK：IDA2A2，还有一个UNIQUE INDEX(IDA3B5, VERSIONID, ONEOFFVERSIONID, VIEWID, UPPER("ADHOCSTRINGIDENTIFIER"),
"PDM8"."WIPPK"."GETNORMALIZEDWIPSTATE"("WIPSTATE"), SESSIONOWNER)。

 

当ORACLE执行insert等DML语句时，会首先自动在所要操作的表上申请一个TM锁，当TM锁获得后，再自动申请TX类型的锁。当两个或多个会话在表的同一条记录上执行DML语言时，第一个会话在记录上加锁，其它的会话处于等待状态，一直到第一个会话提交后TX锁释放，其它的会话才可以加锁。

 

经查看，4次死锁都是两个session同时执行了上面列出的SQL。考虑是因为两个insert语句同时试图向一个表中插入PK或unique值相同的数据，而造成其中会话被阻塞，等待其它会话提交或回滚，因而造成死锁。这种情况，只要其中任何一个session提交 ，另外一个就会报出ORA-00001：违反唯一性约束条件，死锁终止；或者其中一个session回滚，另外一个即可正常执行。

 

出现这种问题，还是考虑应用设计的问题。

 

我们可以模拟出问题出现的场景：

create table t (id int primary key);
session 1:
insert into t values(1);
session 2:
insert into t values(2);
session 1:
insert into t values(2);
session 1:
insert into t values(1);

 

参考：http://mht19840918.垃圾广告.com/blog/165439

            http://gengyonghui.itpub.net/post/21516/299272

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10640532/viewspace-605235/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10640532/viewspace-605235/