Deadlock的产生与分析

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/xxzhaobb/article/details/80382756
参考文档:
https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF024

10.3.6.1 Finding Locks and Lock Holders

-- 模拟deadlock  -略

查看v$Lock表  

SYS@test>select * from v$Lock where request>0
  2  ;

ADDR                 KADDR                       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------------------- -------------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000090D902C8     0000000090D90320             45 TX      65567       1267          0          6        778          0

SYS@test>

查看谁阻止了谁

SYS@test>SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request;  2    3    4    5

SESS              ID1        ID2      LMODE    REQUEST TY
---------- ---------- ---------- ---------- ---------- --
Holder: 1       65567       1267          6          0 TX
Waiter: 45      65567       1267          0          6 TX

SYS@test>

End


阅读更多
换一批

查询数据产生deadlock的问题,急。

02-24

下面是一段查询:rn[code=SQL]rnselect aa.Company, aa.MeterBookNumber,aa.CopyUserName,aa.TotalMoney,aa.RemainMoney,aa.cur,rnbb.YearTotalMoney,bb.YearRemainMoney,bb.Yearcurrn from rn(rnselect info.Company,c.MeterBookNumber,c.CopyUserName,TotalMoney=sum(b.TotalMoney),rn RemainMoney=sum(b.RemainMoney),rncur=rncase when sum(b.TotalMoney)=0 then Null rn else convert(decimal(10,2),(1-sum(b.RemainMoney)/sum(b.TotalMoney))*100)endrnfromrnwcCopyMeterPlanEntry c rnleft join wcCopyMeterPlan d on c.PlanID=d.ID rnleft join wcCopyNoteData a on a.MeterBookID=c.MeterBookIDrnleft joinrn(rnselect a.* from rn[color=#FF0000]wcComputeChargeRecord[/color] a inner join wcCopyNoteData b on a.CopyNoteDataID=b.ID rnwhere b.CopyYear=2009 and b.CopyMonth=12rn)rnb on a.ID = b.CopyNoteDataIDrnleft join wcUserBaseInfo info on info.ID=a.CustomerIDrnwhere info.Company='**公司'rnand d.PlanYear=2009 and d.PlanMonth=12rngroup by info.Company,c.CopyUserName,c.MeterBookNumberrn) aa rninner join rn(rnselect info.Company,c.MeterBookNumber,YearTotalMoney=sum(b.TotalMoney),YearRemainMoney=sum(b.RemainMoney),rnYearcur=rncase when sum(b.TotalMoney)=0 then Null rn else convert(decimal(10,2),(1-sum(b.RemainMoney)/sum(b.TotalMoney))*100)endrn from rnwcCopyMeterPlanEntry c rnleft join wcCopyMeterPlan d on c.PlanID=d.ID rnleft join wcCopyNoteData a on a.MeterBookID=c.MeterBookIDrnleft join rn(rn select a.* from [color=#FF0000]wcComputeChargeRecord[/color] a inner join wcCopyNoteData b on a.CopyNoteDataID=b.ID rn where b.CopyYear=2009 and b.CopyMonth<=12rn)rnb on a.ID = b.CopyNoteDataIDrnleft join wcUserBaseInfo info on info.ID=a.CustomerIDrnwhere info.Company='**公司' rnand d.PlanYear=2009 and d.PlanMonth=12 rngroup by info.Company,c.MeterBookNumber rn) bb on aa.MeterBookNumber=bb.MeterBookNumberrn[/code]rnrn查询需要比较长时间,在查询之后,执行此操作:rn[code=SQL]rnupdate [wcComputeChargeRecord] set [PayStateID]='2',[PayState]='585',[RemainWater]=0.0,[RemainMoney]=0.0 where [ID]=3374297rn[/code]rn这个时候形成deadlock,必须等查询完成后,update才生效,rn我对这个的原因不是特别明白,一般的简单查询是不会出现这样的问题的,是不是因为嵌套的原因呢?rn请大虾们指点。

没有更多推荐了,返回首页