SqlServer死锁的监控分析解决思路

1 背景

1.1 报警情况

最近整理笔记，打算全部迁移到EVERNOTE。整理到锁这一部分，里边刚好有个自己记录下来的案例，重新整理分享下给大家。

某日中午，收到报警短信，DB死锁异常，单分钟死锁120个。

死锁的xml文件如下：

<deadlock-list>
<deadlock victim="process810b00cf8">
<process-list>
<process id="process810b00cf8" taskpriority="0" logused="0" waitresource="RID: 13:1:1541136:62" waittime="7682" ownerId="3396587959" transactionname="UPDATE" lasttranstarted="2016-01-08T12:03:51.067" XDES="0xa99746d08" lockMode="U" schedulerid="41" kpid="17308" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:51.067" lastbatchcompleted="2016-01-08T12:03:51.067" lastattention="1900-01-01T0.067" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396587959" currentdb="13" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="7" stmtstart="214" stmtend="484" sqlhandle="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000">
UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode </frame>
</executionStack>
<inputbuf>
declare @SeqCode varchar(60)
declare @ReturnNum bigint
set @SeqCode='CGJS20160106'
while(1=1)
begin
UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode
end </inputbuf>
</process>
<process id="process18fd5d8cf8" taskpriority="0" logused="248" waitresource="KEY: 13:72057594040090624 (b3ade7c5980c)" waittime="4" ownerId="3396522828" transactionname="user_transaction" lasttranstarted="2016-01-08T12:03:05.310" XDES="0x18c1db63a8" lockMode="U" schedulerid="57" kpid="16448" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:58.737" lastbatchcompleted="2016-01-08T12:03:33.847" lastattention="2016-01-08T12:03:33.850" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396522828" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="6" stmtstart="210" stmtend="400" sqlhandle="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000">
Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' </frame>
</executionStack>
<inputbuf>
declare @SeqCode varchar(60)
declare @ReturnNum bigint
set @SeqCode='CGJS20160106'
while(1=1)
begin
Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' 
end
</inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="1541136" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" id="lock51e8a3980" mode="X" associatedObjectId="72057594040025088">
<owner-list>
<owner id="process18fd5d8cf8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process810b00cf8" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
<keylock hobtid="72057594040090624" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" indexname="PK_FINANCERECEIPTNORULE" id="lock7b2c6bc80" mode="U" associatedObjectId="72057594040090624">
<owner-list>
<owner id="process810b00cf8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process18fd5d8cf8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list> 

查看更多关于SqlServer死锁的监控分析解决思路的详细内容...