ITPub博客

首页 > Linux操作系统 > Linux操作系统 > insert引起的死锁

insert引起的死锁

原创 Linux操作系统 作者:sanxiagirl 时间:2009-06-05 10:59:57 0 删除 编辑

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表有一个PKIDA2A2,还有一个UNIQUE INDEX(IDA3B5, VERSIONID, ONEOFFVERSIONID, VIEWID, UPPER("ADHOCSTRINGIDENTIFIER"),
"PDM8"."WIPPK"."GETNORMALIZEDWIPSTATE"("WIPSTATE"), SESSIONOWNER)

 

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

 

经查看,4次死锁都是两个session同时执行了上面列出的SQL。考虑是因为两个insert语句同时试图向一个表中插入PKunique值相同的数据,而造成其中会话被阻塞,等待其它会话提交或回滚,因而造成死锁。这种情况,只要其中任何一个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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: number(p,s)意义
请登录后发表评论 登录
全部评论

注册时间:2008-01-02

  • 博文量
    142
  • 访问量
    502667