数据库频繁发生死锁,最后导致数据库死机,请求帮助!急**

jiahaiminghr

把自己加的索引去掉，
另外给任务、任务消息表加上行级锁。

changansxy

我们加的索引已经去掉了,但加行级锁,是否能一定解决这个问题?如果可以的话,起步发布版本的时候,怎么没有加?现在又死机了几次,都不知道该怎么做?"

changansxy

select JBMXB.SQBH, JBMXB.XM, JBMXB.GH, JBMXB.JB, JBMXB.JBRQ, JBMXB.KSSJ, JBMXB.JSSJ, JBMXB.SJHJ, JBMXB.JBLY, JBMXB.CLFS, JBMXB.JBLX, JBMXB.CFBZ, JBMXB.BH from JBMXB
cpu  719     read   4496    write  0    time 4406

exec sp_execute 9, '09 17 2007  2:50:30:000PM', '09 17 2007  2:50:30:000PM', N'tmsFinished', NULL, N'E51317CA174E42EA8630AD72C7B8AE12', N'PERFECT.COM', N'-1', N'110DD99A74454B13909B957365C0331A', N'XJFGS', N'CN', N'031203', N'\ROOT\WMFGS.OGN\XJFGS.DPT\CN.PTM\031203.PSM', N'tmsSend', N'新疆分公司', N'出纳', N'李丽', N'WMFGS', N'分公司'

exec sp_execute 24, '09 17 2007  2:51:29:000PM', '09 17 2007  2:51:29:000PM', N'tmsFinished', NULL, N'DEF3892B24E8409284B776D0F860FC17', N'PERFECT.COM', N'-1', N'058C2926270F40EC8CCBC6813B8B1998', N'HJB', N'PTZY', N'021112', N'\ROOT\JG1.OGN\CWB.DPT\HJB.DPT\PTZY.PTM\021112.PSM', N'tmsSend', N'会计部', N'普通职员', N'周带群', N'JG1', N'总公司各部门'

cpu  2687    read   22938    write 1    time 6640

SELECT FTYPENAME, FPRIORITY, FSUBJECT, FFLOWGUID, FBizID, FSPERSON, FSPERSONNAME, FRPERSONNAMES, FSTARTTIME, FFINISHTIME, FLIMITTIME, FPARTYNAME, FPLANNAME, FPLAN, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME, FSTATE, FLASTCHANGETIME, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FDISTRIBUTETIME, TTASK.FPARTY, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FDESCRIPTION, FISANONYMITY
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLIENTACCOUNT = 'PERFECT.COM')) and ((TTASK.FGUID IN(SELECT TTASKMESSAGE.FTASKGUID FROM TTASKMESSAGE WHERE (TTASKMESSAGE.FSTATE IN('tmsSend', 'tmsReceived', 'tmsProcessing')) AND (TTASKMESSAGE.FRORGURL IN('\ROOT\WMFGS.OGN\SHFGS.DPT\YZBG.PTM\990702.PSM' , '\ROOT\WMFGS.OGN\SHFGS.DPT\YZBG.PTM' , '\ROOT\WMFGS.OGN\SHFGS.DPT' , '\ROOT\WMFGS.OGN' , '\ROOT\WMFGS.OGN\SHFGS.DPT\CWZG.PTM\990702.PSM' , '\ROOT\WMFGS.OGN\SHFGS.DPT\CWZG.PTM' , '\ROOT\WMFGS.OGN\SHFGS.DPT' , '\ROOT\WMFGS.OGN') OR (TTASKMESSAGE.FRPERSON = '990702') )))))
  ORDER BY FCREATETIME DESC

cpu  875    read   4059   write 1    time 4046

exec sp_execute 23, NULL, '09 17 2007  2:50:13:000PM', N'fsFinished', N'74E1B3C04CDB4DCEB086BA918DF04FB9', N'CA73100F4FF94CD6AF40E3A8555B0F74', N'9727185CA0D04B168BD3CDD0DBF94B8F'
cpu  157  read   18595  write 1    time 11173



SELECT FSUBJECT, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FSTARTTIME, FFINISHTIME, FSTATE, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FSPERSONNAME, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLIENTACCOUNT = 'PERFECT.COM')) and ((TTASK.FGUID IN(SELECT TTASKMESSAGE.FTASKGUID FROM TTASKMESSAGE WHERE (TTASKMESSAGE.FSTATE IN('tmsSend', 'tmsReceived', 'tmsProcessing')) AND (TTASKMESSAGE.FRORGURL IN('\ROOT\JG1.OGN\SCB.DPT\PZBZB.DPT\PB.PTM\040370.PSM') OR ((TTASKMESSAGE.FRDEPT = 'PZBZB') AND (TTASKMESSAGE.FRPOSITION = 'PB') AND (TTASKMESSAGE.FRPERSON = '040370')))))))
  ORDER BY FCREATETIME DESC

SELECT FTYPENAME, FPRIORITY, FSUBJECT, FFLOWGUID, FBizID, FSPERSON, FSPERSONNAME, FRPERSONNAMES, FSTARTTIME, FFINISHTIME, FLIMITTIME, FPARTYNAME, FPLANNAME, FPLAN, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME, FSTATE, FLASTCHANGETIME, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FDISTRIBUTETIME, TTASK.FPARTY, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FDESCRIPTION, FISANONYMITY
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLIENTACCOUNT = 'PERFECT.COM')) and ((TTASK.FGUID IN(SELECT T1.FGUID FROM TTASK T1 WHERE (T1.FSTATE IN('tsStarted', 'tsProcessing')) AND (T1.FSORGURL IN('\ROOT\JG1.OGN\DNB.DPT\ZLCL.PTM\0508017.PSM') OR (T1.FSPERSON = '0508017'))))))
  ORDER BY FCREATETIME DESC


cpu  2234 read   131564 write 1    time 143843

SELECT * FROM TFLOW WHERE FCLIENTACCOUNT = 'PERFECT.COM'  AND (FCLIENTACCOUNT = 'PERFECT.COM') AND (FGUID IN('25662AAFF2B04666B3475B8E535E0CC0' , 'E5B31190E9FC4B3193D68C9B2B88034B' , 'A160453E78904F6E85E633E64D81877A' , '50E88472CF31444A957845EA32B197E6' , '71CDCC34E26D4C9AA0D44B99420A9571' , '4453FCE47CA94F038FF4A68C41331034' , 'FCA74C8F54E346578AB028A3D7FE5CF3' , 'FB53CFFB57F24C2186FD4F74497C136B' , '18E78BEE99A24C96A2528C76D396D104' , 'F36E306B138F43B2AB92C85D43A2ABA3' , '98BCAD1F007B470F94F4F2D783645ECD' , 'D19E8C2D77AB4DEB867185DAC5D3EDAC' , '23A1DC82D37347609D8C881C080A0172' , '85368EA20C7C4F9EA99BF0E62A9A522F' , '58DC64AD84D9418C94B53826825446BA' , 'E6A72BD8152B4C3CA04BB06D69896452' , '6C231CDDBA0A41C9AD9854BFDE0C0E97' , '65DD26EE963443859ABF003EF0A01F0E' , '9EC4A7FEE2204517B33326A0706E811A' , '7A9E0CC59A5641DEB77B57200DDF798B' , 'C5A4703A7D1C40CB9EB6CA888E2139E4' , 'F0D6840D68F340B995C570A749232AD0' , '2995691890F64DEAB6CA9FD12FB85CC4' , 'E9C473C2C2A5425B81FBFDF07643094F' , '228E30FE923143BCAE7650BEE7DDC85D' , 'C0375D3993434F51B612381B708CCA00' , 'B901520F571B4276A0A934C8A9F25826' , '9D268CAF11B94D4E8220D64B1D7A5234' , '0796668C05AF4AF4819B03F860F1BFA8' , '4801DBB025FF4FDE8D4196EA0D36086B' , '8FBE5D429A3745CABE14364B197A2E4E' , 'BD8D81BEE1894AD383CDED9ED23A44F4' , '8D8EF144F23D41578A71769D87447C9A' , '4B8159069A364DEA88840D17B06E0A8E' , '143DB512173240E3B8DCC3509DA4BFE2' , '960CC32AA97C43929C7B2E98739FC7EF' , '14259DE7A4974004A759CC5ED921B4AE' , '0431DB1610994F46B8821D8F0E440577' , '092707A33FA04D23932A0F762BBF4C8C' , '4B78C435F1A94941BF416A4383D02B06' , '0DA8618AF4724A76BB91A514CB496F6F' , '18C6BD0DBFF94E60950B55221F5E0E50' , '0F518ABF0B714E5091A2ECAD668A50C9' , 'E122B98BA83942E8A0E2F4002433FBEF' , 'DD1C23CED2EB4B378B5C5F5E8F8431BC' , '0E9D580AF8404C51B0ECCFBE323F2DED' , 'DCBF3F265D3B44D4B04F5C6A60564281' , '32BBE9054C6740A39389E0961F546F87' , '2DD3CAC1268141C09F81551DB997BAF1' , '59C0983CF2F34199B8635F279B662144' , '22073BB7ECB0480C83EB81BD42948F19' , '07467EF1A44943F5A7A6FF8558CD1BC9' , '7AF7CA9A37DB40F18893AC937F646BB8' , 'D84B19B0F4CF40BEBFC3AE79B1C7BC1C')) AND (FSTATE IN('fsProcessing')) AND FGUID IN(SELECT TASK.FFLOWGUID FROM TTASK TASK WHERE ((TASK.FCLIENTACCOUNT = 'PERFECT.COM') AND (TASK.FSTATE IN('tsStarted', 'tsProcessing'))))
cpu  234read   2273 write 1    time 11843

declare @id int exec sp_MSset_current_activity @id
cpu  10469   read   1881467 write 1641    time 12140


这些都是平台本身的一些sql,有些东西要花费几十万毫秒

changansxy

这些是在查看死锁的进程属性中查看的sql语句,可能不全
select FGUID from TORGSYSTEM where FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE

SELECT FTYPENAME, FPRIORITY, FSUBJECT, FFLOWGUID, FBizID, FSPERSON, FSPERSONNAME, FRPERSONNAMES, FSTARTTIME, FFINISHTIME, FLIMITTIME, FPARTYNAME, FPLANNAME, FPLAN, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME, FSTATE, FLASTCHANGETIME, FCREATETIME, FPLANSTARTTIME

sp_execute;1

SELECT *
  FROM TTASK
  WHERE (FGROUPGUID = '-1' AND FGUID in ('978831CAFEDF4C35B987FCF7A007BE95')) AND (((TTASK.FCLIENTACCOUNT = 'PERFECT.COM')) and ((TTASK.FGUID IN(SELECT T1.FGUID FROM TTASK T1 WHERE (T1.FSTATE IN('tsStarted', 'tsProcessing')) AND (T

SELECT FSUBJECT, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FSTARTTIME, FFINISHTIME, FSTATE, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FSPERSONNAME, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLI

SELECT * FROM TTASKBIZDATA WHERE FCLIENTACCOUNT = 'PERFECT.COM' and FTASKGUID IN('3BCC32584ECE470986472F504B747C21')

sp_statistics;1
SELECT * FROM TDOCRELEVANCYDEF WHERE UPPER(FFUNCURL) = UPPER('BIZ:\RCSWCL\CNFK.FUNC')

SELECT *
  FROM TTASK
  WHERE (FGROUPGUID = '-1' AND FGUID in ('0B00831968254CD79C4349A0D1BF81F7')) AND (((TTASK.FCLIENTACCOUNT = 'PERFECT.COM')) and ((TTASK.FGUID IN(SELECT TTASKMESSAGE.FTASKGUID FROM TTASKMESSAGE WHERE (TTASKMESSAGE.FSTATE IN('tmsSend

SELECT FTYPENAME, FPRIORITY, FSUBJECT, FFLOWGUID, FBizID, FSPERSON, FSPERSONNAME, FRPERSONNAMES, FSTARTTIME, FFINISHTIME, FLIMITTIME, FPARTYNAME, FPLANNAME, FPLAN, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME, FSTATE, FLASTCHANGETIME, FCREATETIME, FPLANSTARTTIME

SELECT FSUBJECT, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FSTARTTIME, FFINISHTIME, FSTATE, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FSPERSONNAME, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLI

SELECT TASK.* FROM TTASK TASK  WHERE (TASK.FCLIENTACCOUNT = 'PERFECT.COM') AND ((TASK.FSORGURL) IN ('\ROOT\JG1.OGN\CGB.DPT\PTZY.PTM\0510012.PSM'))

SELECT TFlow.FDESCRIPTION, TFlow.FSUBJECT, TFlow.FCREATETIME, TFlow.FLIMITTIME, TFlow.FFINISHTIME, TFlow.FSTATE, TFLOW.FGUID
  FROM TFlow
  WHERE ((TFlow.FCLIENTACCOUNT = 'PERFECT.COM')) and ( ( Upper(TFLow.FState) = 'FSPROCESSING')    and (TFLow.Fcreat

SELECT * FROM TDOCRELEVANCYDEF WHERE UPPER(FFUNCURL) = UPPER('BIZ:\COLLABORATION\MYTASKFUNC.FUNC')

SELECT FSUBJECT, FCREATETIME, FPLANSTARTTIME, FPLANFINISHTIME, FSTARTTIME, FFINISHTIME, FSTATE, FGUID, FTYPE, FFUNCURL, FUPDATEFLAG, FPROCURL, FSPERSONNAME, FSOGNNAME, FSDEPTNAME, FSPOSITIONNAME
  FROM TTASK
  WHERE (FGROUPGUID = '-1') AND (((TTASK.FCLI

select FGUID, FTASKGUID, FPARENTGUID, FRDEPT, FRDEPTNAME, FRPOSITION, FRPOSITIONNAME, FRPERSON, FRPERSONNAME, FRECEIVETIME, FFINISHTIME, FSTATE, FFLAG from TTASKMESSAGE where FTASKGUID = 'BD17B964AE644CF0BB1C8D563C9376F3'

changansxy

按照时间顺序,见附件

changansxy

第二从次

jiahaiminghr

楼主，对应的截图呢

changansxy

跟踪一

changansxy

在附

changansxy

大点的