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

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

原文：Deadlock Troubleshooting, Part 1

死锁是两个或多个线程彼此诸塞，以至于任何一个线程都不能继续的循环诸塞链。当SQL Server 的死锁监视器线程检测到一个循环诸塞链，会选择参与者中的一个作为牺牲品，取消那个spid的当前批处理，并回滚它的事务，以便让其它的spid可以继续工作。死锁的牺牲品会得到一个1205错误：

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
事务(进程 ID 52)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

死锁是一个特殊类型的诸塞情况，但诸塞和死锁不是同一个东西。有时候人们报告他们遇到了“死锁”，他们实际上看到的是诸塞。

除了极少的例外，死锁是诸塞的自然的副作用，而非一个 SQL Server 的bug。典型的死锁解决方案，要么是优化存储过程或应用程序代码，或者改变构架或索引。

下面是如何解决死锁问题的方法。这些步骤适用于绝大多数的死锁问题，并且它们允许你，甚至无需深入探究查询计划或其它的繁琐细节，就可以解决许多死锁问题。什么？你喜欢深入探究查询计划，并且每天的早餐都吃燕麦片？OK，那么我们就在稍后从里到外地了看看死锁的情形。但首先，让我们看看这些基础知识：

1. 启用追踪标志 1222用“DBCC TRACEON(1222, -1)”，或者在SQL启动参数中增加”-T1222“，打开追踪标志1222。在SQL 2005中，该追踪标志是一个新的追踪标志，是经过充分检验的-T1204的更好的完善版本。如果你正在运行SQL 2005，你应该使用1222代替1204，除非你有深度的受虐狂倾向。作为1222的替代：
   • 如果你正在 使用SQL 2000或SQL 7.0，你将没有选择，必须使用-T1204
   • 有一个“Deadlock graph”性能分析器追踪事件，提供了与-T1222相同的信息。如果你使用SQL 2005，可以使用这个代替-T1222。但不要浪费你的时间在SQL 2000的“Lock:Deadlock”和“Lock:Deadlock Chain”追踪事件上，因为它们提供了一个不可接受并且不完全的死锁描述。
2. 获取-T 1222的输出 在死锁发生后，从SQL错误日志得到-T1222输出。你看到的输出会是像这样：

deadlock-list

deadlock victim=processdceda8

process-list

process id=processdceda8taskpriority=0 logused=0 waitresource=KEY: 2:72057594051493888 (0400a4427a09) waittime=5000 ownerId=24008914 transactionname=SELECT lasttranstarted=2006-09-08T15:54:22.327 XDES=0x8fd9a848 lockMode=S schedulerid=1 kpid=4404 status=suspendedspid=54sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2006-09-08T15:54:22.293 lastbatchcompleted=2006-09-08T15:54:22.293 clientapp=OSQL-32 hostname=BARTD2 hostpid=3408 loginname=bartd isolationlevel=read committed (2) xactid=24008914 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

executionStack

frame procname=tempdb.dbo.p1line=2 stmtstart=60 sqlhandle=0x03000200268be70bd

SELECT c2, c3 FROM t1 WHERE c2 = @p1

frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000008a4df52d3

EXEC p1 3

inputbuf

EXEC p1 3

process id=process3c54c58taskpriority=0 logused=16952 waitresource=KEY: 2:72057594051559424 (0900fefcd2fe) waittime=5000 ownerId=24008903 transactionname=UPDATE lasttranstarted=2006-09-08T15:54:22.327 XDES=0x802ecdd0 lockMode=X schedulerid=2 kpid=4420 status=suspendedspid=55sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2006-09-08T15:54:22.327 lastbatchcompleted=2006-09-08T15:54:22.310 clientapp=OSQL-32 hostname=BARTD2 hostpid=2728 loginname=bartd isolationlevel=read committed (2) xactid=24008903 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

executionStack

frame procname=tempdb.dbo.p2line=2 stmtstart=58 sqlhandle=0x030002005fafdb0c

UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1

frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000006f878816

EXEC p2 3

inputbuf

EXEC p2 3

resource-list

keylockhobtid=72057594051559424 dbid=2objectname=tempdb.dbo.t1 indexname=idx1id=lock83642a00 mode=S associatedObjectId=72057594051559424

owner-list

owner id=processdceda8 mode=S

waiter-list

waiter id=process3c54c58 mode=XrequestType=wait

keylockhobtid=72057594051493888 dbid=2objectname=tempdb.dbo.t1 indexname=cidxid=lock83643780 mode=X associatedObjectId=72057594051493888

owner-list

owner id=process3c54c58 mode=X

waiter-list

waiter id=processdceda8 mode=SrequestType=wait

1. 解码-T1222输出为了更好地理解死锁的情形，“解码”-T1222的输出。“process-list”和“resource-list‘概括地描述了死锁。一个“process”是一个参与死锁的spid或工人线程。每个进程被分配一个标识符，像“processdceda8”。一个资源是一个参与者中的一个拥有（使用一个锁）而另一个参与者等待的资源。我喜欢使用下面的这个来概述死锁。如果你愿意可以跳过这个步骤，但我决不会这样；我发现它真的有助于我更加清晰地理解死锁的情况。我已经使用黄色高亮了在1222输出中的这些数据点，那个你可能需要用你自己的重现这个概述。
   
   Spid 54 is running this query (line 2 of proc [p1]):
   SELECT c2, c3 FROM t1 WHERE c2 = @p1
   Spid 55 is running this query (line 2 of proc [p2]):
   UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1
   
   Spid 54 is waiting for a Shared KEY lock on index t1.cidx.
   (Spid 55 holds a conflicting X lock.)
   Spid 55 is waiting for an eXclusive KEY lock on index t1.idx1.
   (Spid 54 holds a conflicting S lock.)
   
   
   对大多数的锁定类型（包括键锁，正如显示在此例），SQL会在输出中直接用名称标识。对某些锁定类型，尽管，你会得到一个“associatedObjectId”，但没有对象名称。如：
   
   
   

   pagelock fileid=1 pageid=95516dbid=9objectname="" id=lock177a9e280 mode=IXassociatedObjectId=72057596554838016

   
   该属性“associatedObjectId”不是可能你熟悉的对象ID类型；实际上它是一个分区ID。你可以决定该数据库名称，通过运行“SELECT DB_NAME(9)”，在这个事例中，“9”来自于用蓝色高亮的“dbid”属性。然后，你就可以在指定的数据库中，通过查找associatedObjectId/PartitionId来决定索引和数据表名称。
   
   SELECT OBJECT_NAME(i.object_id), i.name
   FROM sys.partitions AS p
   INNER JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
   WHERE p.partition_id =72057596554838016
   
   对于使用SQL2005的你来说，可能认为-T1222的输出有点让人吃不消，你是对的。但你也可能想要考虑一下你获得的好处，感谢不必通读-T1204的输出，那个比-T 1222更难以解释，并且几乎不提供更多有关死锁的信息。查看一下这篇帖子的注释-T 1204输出的附件：Decoding -T 1204 Outoput.htm
2. 通过数据库优化器运行死锁包含的查询。放置查询在Management Studio查询窗口，改变数据库上下文到正确的数据库，右点查询文本，并且选择“分析查询在DTA（database engine tuning advisor）”。不要跳过这个步骤；我们见过的超过半数的死锁问题，可以通过简单地增加适当的索引，以便让其中一个的查询的运行地更快，以及带有更少的锁定足迹来解决。如果DTA（database engine tuning advisor）推荐创建索引（它会说Estimated Improvement: <some non-zero>%“”），那么创建它们监控看看死锁是否会持续。你可以从（Action）动作下拉菜单中选择“Apply Recommendations”来立即创建索引，或者存储CREATE INDEX命令做一个脚本，然后在维护窗口去创建它们。确保分别地调优每一个查询。
   
3. 确信查询正在使用最少的必须的事务隔离级别。（-T1222会告诉你这个 -- 在输出搜索“isolationlevel”）通过事务COM+组件运行的查询默认是serializable，这通常具有过渡的杀伤力。这个可以通过查询提示（“...FROM tbl1 WITH (READCOMMITTED)...”），一个SET TRANSACTION ISOLATION LEVEL命令，或者在Windows 2003和稍后，通过在Component Services MMC插件中配置对象，来降低。
   
4. 确保你的事务在满足相关地业务约束的情况下，尽可能地简短。试验一下不使用隐含地事务，因为事务管理模型鼓励不必要的长事务。
   
5. 寻找其他的机会改善查询的效率，要么通过查询改变，或者通过索引改善。一个锁定最少资源的查询，与其他的查询发生死锁的可能性更少。在查询计划中的，全表扫描，索引扫描，以及大散列或大的排序，可能会为改善指示机会。
   
6. 如果一个或两个spid正运行一个多语句的事务，你可能需要去捕捉一个跨越整个死锁的分析器追踪，去识别死锁包含的全部查询。不幸地是，-T1204和-T1222只能打印出（“紧挨死锁循环”）两个查询，而一个诸塞的锁定可能是因为在一个相同的事务中一个较早的查询引起的。

这些是全部一般性的推荐措施，你可以应用于任何死锁，而无需真正地卷起袖子搞脏自己。如果在所有这些方法实施后，还没有解决死锁问题，你必须深入进去，对特定的场景裁减特定的解决方案。这里是一些公共的、可供你选择的，决定如何更好地处理死锁的技术清单:

• 以相同的循序存取对象。考虑一下下面的两个批处理：

1. 开始事务	1. 开始事务
2. 更新Part数据表	2. 更新Supplier数据表
3. 更新Supplier数据表	3. 更新Part数据表
4. 提交事务	4. 提交事务

这两个批处理操作可能会频繁地死锁。如果两者都准备执行步骤3，他们每一个都可能被另外一个诸塞，因为两者都需要存取在步骤2中，被另外一个连接锁定的资源。

• 如果两个锁定的参与方使用相同的索引。考虑增加一个对spid中的一个，可以提供一个替代的存取路径的索引。例如，为一个在死锁中的SELECT增加一个方便的覆盖非聚集索引，可能会防止这个问题（假定覆盖索引键不会被另外一个死锁的参与者修改）。
• 换句话说，如果因为采用指向一个公共需求的数据行或页的候选路径（索引）而死锁，考虑是否移除其中一个索引，或者强制两个查询共享一个存取路径的索引提示。作为此方法的后果，小心潜在的性能冲击。
• 死锁是一个特殊类型的诸塞，在那里两个spids彼此诸塞。有时最好的阻止死锁的方法是强制诸塞发生在两个事务中的一个的较早的时点。例如，如果你强制spid A被spid B诸塞在事务A的最开始，它可能就没有机会获取稍后可能诸塞spid B的锁定资源。这是否意味着你应该故意引发诸塞？是的，但记住你已经有诸塞，或者你不会处在一个死锁状态。简单的诸塞是对一死锁的极大改善。一旦B提交它的事务，A能够继续。HOLDLOCK和UPDLOCK提示可以用于这个目的。
• 如果在一个与低优先级的进程的死锁中，一个高优先级的进程被选择作为一个牺牲品。这低优先级的进程可以修改为SET DEADLOCK_PRIORITY LOW。设置为此的spid会在任何它们遇到的死锁中提供自己作为牺牲品。
• 避免放置聚集索引在频繁更新的列上。更新集聚索引键列会要求锁定集聚索引（移动该行）和所有的非集聚索引（因为叶级的非聚集索引行是按聚集索引键值引用的）
• 在某些情况下，假定其中一个查询是SELECT语句，增加一个NOLOCK提示可能是一个方法。尽管这是一个诱人的方法，因为它可以快速和容易地解决许多死锁问题，但使用此方法需要小心，因为它带有所有的读未提交隔离级别的警告（一个查询可能返回事务不一致的数据视图）。如果你不熟悉这个风险，阅读一下SQL联机丛书的“SET TRANSACTION ISOLATION LEVEL”主题。
• 在SQL 2005中，你可以考虑使用新的SNAPSHOT隔离级别。这可以避免大多数的诸塞的同时避免NOLOCK的风险。一个甚至更酷的新特性，依我之见，是新的READ COMMITTED SNAPSHOT数据库选项（参见ALTER DATABASE），那个允许你使用不同的快照隔离级别，而无须改变你的应用程序。
• 如果在死锁中，有一个或两个诸塞是S/X表锁，可能涉及锁升级。你可以减少这种锁升级的可能性，通过使能追踪标志1224（SQL 2005及以上）或1211（参见KB323630）。注意这不适用于“intent”表锁，那个带有一个首字母“I”前缀（诸如，IS/IX表锁）。
• 如果死锁是间歇性的，有时最简单的解决方案是增加死锁重试逻辑。重试逻辑可以在T-SQL，只要（a）你在使用SQL 2005或稍后的版本，以便于你可以使用BEGIN TRY，并且（b）你的事务完全包含在一个单一的存储过程或批处理中。参见此篇文章详细了解。如果死锁事务跨越多个批处理，你仍然可以增加死锁重试逻辑，但它需要被移动到客户端应用程序代码。如果你只能增加死锁重试逻辑到死锁的其中一个参与者，你可以使用SET DEADLOCK_PRIORITY LOW以确保数据库引擎优先退出含有重试逻辑的事务。

在后续的帖子中，会详细地检视一个相当典型的死锁。以提供一个，如果8个高级别的步骤无效时，你必须做些什么的示例，强制你去理解更深程度的场景，以便你可以制定一个定制的解决方案。

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