SQL Server 技术公告-How to 解决死锁

简介

<script type="text/javascript"></script>

这篇文章检查死锁状态并提供了解决死锁的步骤。 每个死锁可能会不同，可能引起几个不同的环境变量。 这篇文章中提供该信息可以帮助您识别和解决死锁。

回到顶端

案例研究

<script type="text/javascript"></script>

in case study，we examine 911 system that has six operators。 during peak activity，are using Microsoft Visual Basic front-end application experiences broken 的 connections。 because of broken connections，operators must re-input data。 for 911 system that operates day 24 hours，week，seven days this behavior is unacceptable。

回到顶端

什么是死锁？

<script type="text/javascript"></script>

当两个时，发生死锁系统服务器进程 ID (SPID) 正在等待资源和既不过程可以提前，因为其他进程使其无法获取该资源。

锁管理器的线程检查死锁。 当一个锁管理器的死锁检测算法检测到死锁时，锁管理器选择其中一个 SPID 为牺牲品。 锁管理器启动的发送给客户端，1205 错误邮件并锁管理器删除 SPID。 取消 SPID 释放资源并允许继续其他 SPID。 取消，它是死锁牺牲品 SPID 是何而起 Visual Basic 前端应用程序遇到断开的连接。

在设计良好应用程序，前端应用程序应 1205 错误的补漏白、 重新连接到 SQL Server，然后 re-submit 该事务。

尽管可以最小化死锁，它们无法完全避免。 这就是原因前端应用程序应被设计为处理死锁。

回到顶端

如何识别死锁

<script type="text/javascript"></script>

第 1 步

若要标识死锁，您必须首先获取日志信息。 如果您怀疑死锁，您必须收集有关 (SPID) 和死锁中涉及该资源的信息。 为此，添加-T1204 和到 SQL Server-T3605 启动参数。 要添加这两个启动参数，请按照下列步骤操作：

•	启动 SQL Server 企业管理器。
•	选择，右键单击该服务器。
•	单击 属性 。
•	单击 启动参数 。
•	在 启动参数 对话框，键入 -T1204 参数 文本中框，然后再单击 添加 。
•	在 参数 文本框中，键入 -T3605 ，然后单击 添加 。
•	单击 确定 。

startup parameters will take effect when SQL Server is stopped and then re-started。

-T1204 启动参数收集关于该进程和死锁检测算法遇到死锁时资源的信息。 -T3605 启动参数将此信息写入 SQL Server 错误日志。

-T1205 启动参数收集信息每次死锁算法检查为死锁，不当遇到死锁时。 您不必使用-T1205 启动参数。

if do use-T1205 startup parameter，following is sample of that will be in SQL Server error log output:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 1
2003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4     
2003-05-14 11:46:26.76 spid4     End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     Starting deadlock search 2

有时，您可能无法停止并重新启动 SQL Server。 在这种情况下，您可以使用查询分析器运行以下命令以便死锁跟踪标志。

注意 这种方式可以收集有关死锁信息立即。 "-1"表示所有 SPID。

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

步骤 2

接下来，您必须收集 SQL 事件探查器跟踪。 如果您打开死锁跟踪标志，您将获得大多数必需的信息，但不是总。 例如，一个案例研究中跟踪标志输出标识在 sp_cursoropen 系统存储过程和"UPDATE tblQueuedEvents 设置 notifyid = 3，ResynchDate"语句参与死锁。 unfortunately，do not know of sp_cursoropen system stored procedure definition。 also do not have complete UPDATE statement because was truncated。

SQL Profiler can obtain in addition to of statements execution plans full statements。 SQL Profiler trace also has for"deadlock"and"deadlock chain"for lock event。 "deadlock"corresponds to-T1204 flag，and"deadlock chain"corresponds to-T1205 flag。 打开死锁跟踪标志和死锁的匹配项时运行 SQL 事件探查器跟踪应提供您 must have to 疑难解答死锁的数据。 在这种情况下，并在其他，运行 SQL 事件探查器更改，以防止死锁执行足够的时间。 因此，您将通常捕获使用跟踪标志，死锁信息，然后在运行 SQL 事件探查器。

回到顶端

疑难解答死锁

<script type="text/javascript"></script>

死锁之后，您可以通过使用 sqldiag 实用程序以及使用 SQL 事件探查器收集有关死锁的信息。 在 SQLDiag.txt 文件的输出，查找一个"等待--图形"项。 A"等待-为图形"项表示已遇到死锁。

以下是当您使用时，可能看见在 SQL Server 错误日志中输出的一个示例-T1205 启动参数。

2003-05-05 15:11:50.80 spid4    Wait-for graph
2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4    Victim Resource Owner:
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4    Requested By: 
2003-05-05 15:11:50.80 spid4    Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4    SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1937f2a0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4    Grant List 0::
2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0
 
2003-05-05 15:11:50.80 spid4    Node:2
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4    Requested By: 
2003-05-05 15:11:50.80 spid4    Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4    SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1936e420 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4    Grant List 0::
2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0

在"等待--图形"项，必须节点 1 和节点 2。 在每个节点，您可以在授予部分和请求部分。 授予部分是"允许列表"，请求部分是"请求按"。
每个节点中, 可以识别以下：

•	SPID。
•	SPID 正在执行该命令。
•	该资源。
•	该资源上的锁模式。

例如，在节点 1，授予列表，SPID 55 已被授予的更新锁，模式： U，资源 KEY 上的： 8:1653632984:2。 8 = DBID，1653632984 = ObjectID 和 2 = Indid。 若要获取数据库标识号，运行 sp _ helpdb 存储过程。 要获取表，运行下面的代码：

select * from sysobjects where id = 1653632984

要获得索引，运行下面的代码：

select * from sysindexes where indid = 2 and id = 1653632984

IndexId is equal to 2，if know index is nonclustered index。 that SPID 55 was executing command was sp_cursoropen stored procedure。

在节点 2，授予列表，SPID 60 已被授予的排他锁，模式： X，资源 KEY 上的： 8:1653632984:1。 8 = DBID，1653632984 = ObjectID，1 = Indid。 这是在同一表上但 index 1 是聚集的索引。 SPID 60 正在执行的命令是:

Update tblQueuedEvents Set NotifyID = 2, ResynchDate

等于 1 的一个 IndexId 是聚集的索引。

等于 2 的 IndexId 是一个非聚集索引。

注意 死锁是很时间敏感的。

接下来，在节点 1，请求，SPID 55 请求共享的锁，模式： S，IndexId 上 = 1。 在节点 2，请求、 SPID 60 请求的排他锁，模式： X，IndexId 上的 = 2。 因为这些锁请求发生在同一时间，发生死锁。 每个 SPID 授予锁防止继续请求的锁定。

following table shows lock compatibility chart。 for more information about lock compatibility，see in SQL Server 2000 Books Online"Lock Compatibility"topic。

锁兼容性图表

请求模式	IS		S		U		IX		SIX		X
意向共享 (IS)	是		是		是		是		是		否
共享 (S)	是		是		是		否		否		否
更新 (U)	是		是		否		否		否		否
意向排他 (IX)	是		否		否		是		否		否
与意向排他 (六） 共享	是		否		否		否		否		否
排他 (X)	否		否		否		否		否		否

next，by looking at output，identify as tblQueuedEvents table，ObjectId 1653632984 and obtain for table output sp_help stored procedure。 there were two indexes on table。 two indexes were ix_tblQueuedEvents and PK_tblQueuedEvent 。 ix_tblQueuedEvents is on ResynchDate，clustered 的 index and PK_tblQueuedEvent is primary key，on EventSID unique nonclustered index。

SQL 事件探查器跟踪不能捕获死锁匹配项。 请记住，死锁是很时间相关。 SQL 事件探查器的系统开销可能添加一段时间之一进程的执行并的阻止在死锁情况下获取 SQL 事件探查器。 但是，它提供了可用于解决此问题信息。 您找到完全更新 tblQueuedEvents 语句为与以下内容类似：

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023

您还找到执行计划。 您还没有完全 sp_cursoropen 存储过程语句，但您没有足够的信息，可以建议一个解决方案，将解决死锁。

下面是执行计划。

注意 is read this particular execution plan right to left and bottom to top.

StmtText                                                      
				                                                                           
				   
				                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
				
Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'
				where eventSID = 73023                                                     
				   
				                    
|--Clustered Index
				Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),
				SET:([tblQueuedEvents].[NotifyID]=[@1],
				[tblQueuedEvents].[ResynchDate]=[Expr1004]))  
     |--Top(1)                                                                 
				                                                                           
				   
				               
           |--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))                
				                                                                           
				   
				                
                 |--Index
				Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),
				SEEK:([tblQueuedEvents].[EventSID]=[@3]) 

回到顶端

建议一个解决方案解决死锁

<script type="text/javascript"></script>

请注意在 UPDATE 语句聚集索引上执行一个"聚集的索引更新"。 因此，该非聚集索引和聚集的索引必须都进行更新。 非聚集的索引聚集的索引是 ix_tblQueuedEvents ， PK_tblQueuedEvents 。 要执行更新，UPDATE 语句必须获取这两个索引的排他锁。 这些两个索引是的索引所涉及到的死锁。 从审阅 SQL 事件探查器跟踪，您看不到在 WHERE 子句中使用 ResynchDate 的任何查询。 所有语句都是非常特定，以及它们在 WHERE 子句中使用 EventSID。 of clustered index better choice would be EventSID。 使用此信息和与客户讨论，我们发现 ResynchDate 索引是旧，而且它不必要的。 我们建议的客户放 ResynchDate， ix_tblQueuedEvents 索引并且它们使得 PK_tblQueuedEvent 一个聚集的索引。 这解决死锁情况。

this is only one of that involves locks deadlock case example。 deadlocks can also involve parallelism and involve threads。 can involve one，two，three，or more SPIDs，and resources。 with any deadlock case，must obtain –T1204 startup parameter output，and to identify，to troubleshoot，and to resolve deadlock SQL Profiler trace。 your deadlock situation will involve different processes and resources。 therefore，solutions will vary from case to case。 可用于解决死锁典型的方法包括：

•	adding and dropping indexes。
•	正在添加索引提示。
•	修改应用程序访问一种类似的模式中的资源。
•	从触发器像在事务中移除活动。 默认情况下触发器是事务性。
•	保持事务尽可能短。

回到顶端

其他阅读

<script type="text/javascript"></script>

有关死锁信息，请访问下面的 Microsoft Web 站点：

http://msdn2.microsoft.com/en-us/library/Aa213040 (http://msdn2.microsoft.com/en-us/library/Aa213040)

http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx)