Troubleshooting deadlock in SQL Server

来源：互联网发布：arm linux gcc -v 编辑：程序博客网时间：2024/05/21 17:15

我们有下面三种收集死锁信息的方式：

1，使用SQL Server默认记录的死锁信息

第一步收集deadlock的信息

在SQL Server 2008之前，默认不会记录死锁的详细信息。我们可以在error log中看到一行简单的记录，告诉你发生了死锁，但是我们无从分析。从2008开始，SQL Server error log中不再会记录相关事件，但SQL Server默认会记录死锁的详细信息，使用所谓的default extended events trace。

因此如果有用户报告发生了死锁，即使没有enable任何trace，你仍然可以运行下面的语句来查询：

注意死锁是数据库级别的信息，所以当前数据库是哪个都一样。

DECLARE @xml XMLSELECT @xml = target_dataFROM   sys.dm_xe_session_targets       JOIN sys.dm_xe_sessions         ON event_session_address = addressWHERE  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)

得到是一个当前SQL Server中deadlock的历史记录。一行xml是一次deadlock的信息，使用其中的lastbatchstarted tag来大致定位你想要分析的死锁，格式化成更容易看的样子：

<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

0 0