SQL Server 死锁故障排除，第二部分

jiabanl

SQL Server 死锁故障排除，第二部分
原文：Deadlock Troubleshooting, Part 2
在这篇博文中，将检视一个实际的死锁，然后使用在SQL Server 死锁故障排除，第一部分中描述的步骤解决它，以便你可以看到它们的实际效果。这是一个微软的内部客户要求协助解决的，死锁场景的简化版本。要建立此场景，运行下面的SQL：
　　
　　-- Batch #1
　　CREATEDATABASEdeadlocktest
　　GO
　　USEdeadlocktest
　　SETNOCOUNTON
　　DBCCTRACEON(1222,-1)
　　GO
　　IFOBJECT_ID('t1')ISNOTNULLDROPTABLEt1
　　IFOBJECT_ID('p1')ISNOTNULLDROPPROCp1
　　IFOBJECT_ID('p2')ISNOTNULLDROPPROCp2
　　GO
　　CREATETABLEt1(c1int,c2int,c3int,c4char(5000))
　　GO
　　DECLARE@xint
　　SET@x=1
　　WHILE(@x<=1000)BEGIN
　　INSERTINTOt1VALUES(@x*2,@x*2,@x*2,@x*2)
　　SET@x=@x+1
　　END
　　GO
　　CREATECLUSTEREDINDEXcidxONt1(c1)
　　CREATENONCLUSTEREDINDEXidx1ONt1(c2)
　　GO
　　CREATEPROCp1 @p1intAS
　　SELECTc2,c3FROMt1WHEREc2BETWEEN@p1AND@p1+1
　　GO
　　CREATEPROCp2 @p1intAS
　　UPDATEt1SETc2=c2+1WHEREc1=@p1
　　UPDATEt1SETc2=c2-1WHEREc1=@p1
　　GO
　　
现在从另外一个连接运行下面的SQL：
　　-- Batch #2
　　USEdeadlocktest
　　SETNOCOUNTON
　　WHILE(1=1)
　　EXECp2 4
　　GO
　　
最后，保持上面的脚本运行的同时，从第三个连接运行下面的SQL：
　　-- Batch #3
　　USEdeadlocktest
　　SETNOCOUNTON
　　CREATETABLE#t1(c2int,c3int)
　　GO
　　WHILE(1=1)BEGIN
　　INSERTINTO#t1EXECp1 4
　　TRUNCATETABLE#t1
　　END
　　GO
　　
这会引起一个死锁，你应该看见其中一个批处理被一个1205错误强制退出。现在我们有一个可复现的死锁，可遵循SQL Server 死锁故障排除，第一部分博文中解决问题的步骤。
　　

• 启用追踪标志 1222（译注：执行DBCC TRACEON (1222, -1)）该设置脚本已经为你作为一个全局标志打开追踪标志（在dbcc traceon命令中，这“-1”是关键）。
  
• 得到-T1222输出（译注：打开YourMSSQLInstallDir\MSSQL\Log下的ERRORLOG文件，搜索关键字“1222”）现在看看你的错误日志，你应该看到描述死锁的追踪标志1222输出。
  
• 解码-T1222输出再一次通读SQL Server 死锁故障排除，第一部分，如果你需要更多的有关如何解释-T1222或-T1204的输出。此处是你在筛选-T1222的细节后，最终抽取的最重要的花絮：
  
  Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
  SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
  Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
  UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
  
  Spid X is waiting for a Shared KEY lock on index t1.cidx.Spid Y holds a conflicting X lock.
  Spid Y is waiting for an eXclusive KEY lock on index t1.idx1.Spid X holds a conflicting S lock.
  
• 通过数据库引擎优化顾问运行查询（译注：打开待分析的批查询，在查询窗口右点打开快捷菜单，选择“数据库引擎优化顾问中的分析查询”菜单项，打开“数据库引擎优化顾问”实用程序，在“常规”选项卡的“选择要优化的数据库和表”区域，指定要优化的数据库，但后单击工具栏的“开始分析”按钮执行分析切换到“建议”选项卡可以看到推荐的建议，使用主菜单的“操作”-“应用建议”应用优化顾问建议，或“保存建议”将来使用）。-T1222输出告诉我们，在死锁的时候我们正在运行什么inputbuffer（输入缓存）（“EXECp1 4”和“EXEC p2 4”）。使用在SQL Server 死锁故障排除，第一部分中讨论的步骤，在DTA（database engine tuning advisor）中，调优每一个查询。DTA（databaseengine tuning advisor）会为Batch 3推荐一个新的索引。通过从动作下拉列表菜单，选择“Apply Recommendations”，创建这个索引。
  

　　
这时候，如果你重新运行批处理2和批处理3，你会发现死锁已经解决。你甚至不需要使用5-8步骤，或者我在该系列博文的第一部分的列表中地其它死锁规避策略。
　　
在随后的博文中，我会检视，在这个特殊的死锁中查询计划的细节，方便理解是什么原因引起死锁，以及为什么DTA（database engine tuning advisor）的建议索引修复了它。

SQL Server 死锁故障排除，第一部分
SQL Server 死锁故障排除，第二部分
SQL Server 死锁故障排除，第三部分