我们有下面三种收集死锁信息的方式:
1, 使用SQL Server默认记录的死锁信息
第一步 收集deadlock的信息
在SQL Server 2008之前,默认不会记录死锁的详细信息。我们可以在error log中看到一行简单的记录,告诉你发生了死锁,但是我们无从分析。从2008开始,SQL Server error log中不再会记录相关事件,但SQL Server默认会记录死锁的详细信息,使用所谓的default extended events trace。
因此如果有用户报告发生了死锁,即使没有enable任何trace,你仍然可以运行下面的语句来查询:
注意死锁是数据库级别的信息,所以当前数据库是哪个都一样。
DECLARE @xml XML
SELECT @xml = target_data
FROM sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
SELECT CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML)
FROM (SELECT @xml AS TargetData) AS Data
CROSS APPLY
TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
<deadlock>
<victim-list>
<victimProcess id="process27aee8988" />
</victim-list>
<process-list>
<process id="process27aee8988" taskpriority="5" logused="0" waitresource="PAGE: 9:1:18003772" waittime="523" ownerId="2366350030" transactionname="ReorderPages" lasttranstarted="2014-04-07T03:34:23.950" XDES="0x1efdcb9b0" lockMode="X" schedulerid="10" kpid="7700" status="suspended" spid="94" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2014-04-07T03:30:00.263" lastbatchcompleted="2014-04-07T03:30:00.263" clientapp="SQLAgent - TSQL JobStep (Job 0x625D9DBA55512D4B88FFF7319AB19B14 : Step 1)" hostname="BODCPRODVSQL128" hostpid="3152" loginname="PROD\s-sqlsvc" isolationlevel="read committed (2)" xactid="2366171639" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" sqlhandle="0x0100090063145a17a05accfb0e0000000000000000000000" />
<frame procname="" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000" />
<frame procname="" line="72" stmtstart="2810" stmtend="2878" sqlhandle="0x03000900786d180956027501e8a100000100000000000000" />
<frame procname="" line="1" stmtstart="870" stmtend="958" sqlhandle="0x02000000f567521a6df66dae81cbb0d6082497432728a2cd" />
<frame procname="" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000" />
<frame procname="" line="73" stmtstart="5694" stmtend="5798" sqlhandle="0x03000d00b3de6e27052c100197a200000100000000000000" />
<frame procname="" line="1" sqlhandle="0x01000d00fa28f31a505d5db40a0000000000000000000000" />
</executionStack>
<inputbuf>exec LEOBuildIndexesUpdateStats </inputbuf>
</process>
<process id="process438ce08" taskpriority="0" logused="1318872" waitresource="PAGE: 9:1:22550156" waittime="469" ownerId="2366349365" transactionname="UpdateAccountTransferTable" lasttranstarted="2014-04-07T03:34:23.877" XDES="0x25cb0a790" lockMode="IX" schedulerid="7" kpid="11068" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-07T03:34:23.460" lastbatchcompleted="2014-04-07T03:34:23.460" clientapp=".Net SqlClient Data Provider" hostname="BODCPRODVSQL128" hostpid="10088" loginname="PROD\s-propdata" isolationlevel="read committed (2)" xactid="2366349365" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="13" stmtstart="534" stmtend="1920" sqlhandle="0x03000900e59af62809a91f01aba200000100000000000000" />
<frame procname="" line="99" stmtstart="4332" stmtend="4448" sqlhandle="0x03000900386b574b3582eb00c5a200000100000000000000" />
<frame procname="" line="50" stmtstart="2692" stmtend="2922" sqlhandle="0x03000900941f284ed5929e00aba200000100000000000000" />
<frame procname="" line="9" stmtstart="464" stmtend="642" sqlhandle="0x03000a006502e0715df5af00aba200000100000000000000" />
<frame procname="" line="4" stmtstart="224" stmtend="420" sqlhandle="0x01000a00b6fca934509742900b0000000000000000000000" />
</executionStack>
<inputbuf>
DECLARE @logText NVARCHAR(MAX)
EXEC IntegratedService_ProcessLatestCommand @logText OUTPUT
SELECT @logText </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="18003772" dbid="9" objectname="" id="lock4d18c2880" mode="IX" associatedObjectId="72057597662003200">
<owner-list>
<owner id="process438ce08" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process27aee8988" mode="X" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="22550156" dbid="9" objectname="" id="lock30cc99e80" mode="X" associatedObjectId="72057597662003200">
<owner-list>
<owner id="process27aee8988" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process438ce08" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
第二步 分析
SQL Server中有一个叫deadlock graph的东西,是一种xdl类型的文件,可以用图形化的方式展示deadlock中涉及的多个process以及冲突的原因。我们可以在SSMS中直接打开该类文件,并简单直观的明白死锁的原因。
然而只有使用SQL Server profiler (见第三种方法)时,才会输出这种文件,而我们现在使用的这种方式输出的xml,无法自动转化成xdl文件并自动用deadlock graph展示出来。因此我们只能手工分析deadlock。如何分析请参考另外一篇文章:
http://blog.csdn.net/onlyqi/article/details/23357207
2, 或者使用Trace flag 1204和1222
如果当前deadlock发生的很频繁,我们则完全可以打开trace以得到更实时,详细的信息。
另外一个troubleshooting deadlock的有效办法是使用trace flag T1204和T1222。下面的链接详细介绍了如何使用这两个trace flag以及用实际例子说明如何分析trace的结果,很实用。
注意这两个trace的输出信息在SQL Server error log中。
http://www.mssqltips.com/sqlservertutorial/252/tracing-a-sql-server-deadlock/
3, 使用SQL Server profiler
这种方法同样是针对当前正在发生的deadlock。
http://technet.microsoft.com/en-us/library/ms188246.aspx
具体的操作方法:
http://msdn.microsoft.com/en-us/library/ms190465.aspx
最后是一篇介绍如何在死锁发生时,自动发送邮件给DBA的文章:
http://blog.csdn.net/onlyqi/article/details/17246595