SQL Server检测和结束死锁

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。下图清楚地显示了死锁状态，其中：

• 任务 T1 具有资源 R1 的锁（通过从 R1 指向 T1 的箭头指示），并请求资源 R2 的锁（通过从 T1 指向 R2 的箭头指示）。
  
  
• 任务 T2 具有资源 R2 的锁（通过从 R2 指向 T2 的箭头指示），并请求资源 R1 的锁（通过从 T2 指向 R1 的箭头指示）。
  
  
• 因为这两个任务都需要有资源可用才能继续，而这两个资源又必须等到其中一个任务继续才会释放出来，所以陷入了死锁状态。
  
  

 

SQL Server 数据库引擎自动检测 SQL Server 中的死锁循环。数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务（出现错误）来打断死锁。

可以死锁的资源

每个用户会话可能有一个或多个代表它运行的任务，其中每个任务可能获取或等待获取各种资源。以下类型的资源可能会造成阻塞，并最终导致死锁。

• 锁。等待获取资源（如对象、页、行、元数据和应用程序）的锁可能导致死锁。例如，事务 T1 在行 r1 上有共享锁（S 锁）并等待获取行 r2 的排他锁（X 锁）。事务 T2 在行 r2 上有共享锁（S 锁）并等待获取行 r1 的排他锁（X 锁）。这将导致一个锁循环，其中，T1 和 T2 都等待对方释放已锁定的资源。
  
  
• 工作线程。排队等待可用工作线程的任务可能导致死锁。如果排队等待的任务拥有阻塞所有工作线程的资源，则将导致死锁。例如，会话 S1 启动事务并获取行 r1 的共享锁（S 锁）后，进入睡眠状态。在所有可用工作线程上运行的活动会话正尝试获取行 r1 的排他锁（X 锁）。因为会话 S1 无法获取工作线程，所以无法提交事务并释放行 r1 的锁。这将导致死锁。
  
  
• 内存。当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。例如，两个并发查询（Q1 和 Q2）作为用户定义函数执行，分别获取 10MB 和 20MB 的内存。如果每个查询需要 30MB 而可用总内存为 20MB，则 Q1 和 Q2 必须等待对方释放内存，这将导致死锁。
  
  
• 并行查询执行的相关资源。通常与交换端口关联的处理协调器、发生器或使用者线程至少包含一个不属于并行查询的进程时，可能会相互阻塞，从而导致死锁。此外，当并行查询启动执行时，SQL Server 将根据当前的工作负荷确定并行度或工作线程数。如果系统工作负荷发生意外更改，例如，当新查询开始在服务器中运行或系统用完工作线程时，则可能发生死锁。
  
  
• 多个活动的结果集 (MARS) 资源。这些资源用于控制在 MARS 下交叉执行多个活动请求（请参阅批处理执行环境和 MARS）。
  
  
  • 用户资源。线程等待可能被用户应用程序控制的资源时，该资源将被视为外部资源或用户资源，并将按锁进行处理。
    
    
  • 会话互斥体。在一个会话中运行的任务是交叉的，意味着在某一给定时间只能在该会话中运行一个任务。任务必须独占访问会话互斥体，才能运行。
    
    
  • 事务互斥体。在一个事务中运行的所有任务是交叉的，意味着在某一给定时间只能在该事务中运行一个任务。任务必须独占访问事务互斥体，才能运行。
    
    
  任务必须获取会话互斥体，才能在 MARS 下运行。如果任务在事务下运行，则它必须获取事务互斥体。这将确保在某一给定会话和给定事务中一次仅有一个任务处于活动状态。获取所需互斥体后，任务就可以执行了。任务完成或在请求过程中生成时，它将按获取的相反顺序先释放事务互斥体，然后释放会话互斥体。但是，这些资源可能导致死锁。在下面的代码示例中，两个任务（用户请求 U1 和用户请求 U2）在同一会话中运行。
  
  

  	复制代码
  U1: Rs1=Command1.Execute("insert sometable EXEC usp_someproc"); U2: Rs2=Command2.Execute("select colA from sometable");

  用户请求 U1 执行的存储过程已获取会话互斥体。如果执行该存储过程花费了很长时间，则数据库引擎会认为存储过程正在等待用户的输入。用户等待 U2 的结果集时，用户请求 U2 正在等待会话互斥体，U1 正在等待用户资源。死锁状态的逻辑说明如下：
  
  

 

死锁检测

上面列出的所有资源均参与数据库引擎死锁检测方案。死锁检测是由锁监视器线程执行的，该线程定期搜索数据库引擎实例的所有任务。以下几点说明了搜索进程：

• 默认时间间隔为 5 秒。
  
  
• 如果锁监视器线程查找死锁，根据死锁的频率，死锁检测时间间隔将从 5 秒开始减小，最小为 100 毫秒。
  
  
• 如果锁监视器线程停止查找死锁，数据库引擎将两个搜索间的时间间隔增加到 5 秒。
  
  
• 如果刚刚检测到死锁，则假定必须等待锁的下一个线程正进入死锁循环。检测到死锁后，第一对锁等待将立即触发死锁搜索，而不是等待下一个死锁检测时间间隔。例如，如果当前时间间隔为 5 秒且刚刚检测到死锁，则下一个锁等待将立即触发死锁检测器。如果锁等待是死锁的一部分，则将会立即检测它，而不是在下一个搜索期间才检测。
  
  

通常，数据库引擎仅定期执行死锁检测。因为系统中遇到的死锁数通常很少，定期死锁检测有助于减少系统中死锁检测的开销。

锁监视器对特定线程启动死锁搜索时，会标识线程正在等待的资源。然后，锁监视器查找特定资源的所有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个循环。用这种方式标识的循环形成一个死锁。

检测到死锁后，数据库引擎通过选择其中一个线程作为死锁牺牲品来结束死锁。数据库引擎终止正为线程执行的当前批处理，回滚死锁牺牲品的事务并将 1205 错误返回到应用程序。回滚死锁牺牲品的事务会释放事务持有的所有锁。这将使其他线程的事务解锁，并继续运行。1205 死锁牺牲品错误将有关死锁涉及的线程和资源的信息记录在错误日志中。

默认情况下，数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品。此外，用户也可以使用 SET DEADLOCK_PRIORITY 语句指定死锁情况下会话的优先级。可以将 DEADLOCK_PRIORITY 设置为 LOW、NORMAL 或 HIGH，也可以将其设置为范围（-10 到 10）间的任一整数值。死锁优先级的默认设置为 NORMAL。如果两个会话的死锁优先级不同，则会选择优先级较低的会话作为死锁牺牲品。如果两个会话的死锁优先级相同，则会选择回滚开销最低的事务的会话作为死锁牺牲品。如果死锁循环中会话的死锁优先级和开销都相同，则会随机选择死锁牺牲品。

使用 CLR 时，死锁监视器将自动检测托管过程中访问的同步资源（监视器、读取器/编写器锁和线程联接）的死锁。但是，死锁是通过在已选为死锁牺牲品的过

 

死锁信息工具

为了查看死锁信息，数据库引擎提供了监视工具，分别为两个跟踪标志以及 SQL Server Profiler 中的死锁图形事件。

跟踪标志 1204 和跟踪标志 1222

发生死锁时，跟踪标志 1204 和跟踪标志 1222 会返回在 SQL Server 2005 错误日志中捕获的信息。跟踪标志 1204 会报告由死锁所涉及的每个节点设置格式的死锁信息。跟踪标志 1222 会设置死锁信息的格式，顺序为先按进程，然后按资源。可以同时启用这两个跟踪标志，以获取同一个死锁事件的两种表示形式。

除了定义跟踪标志 1204 和 1222 的属性之外，下表还显示了它们之间的相似之处和不同之处。

属性	跟踪标志 1204 和跟踪标志 1222	仅跟踪标志 1204	仅跟踪标志 1222
输出格式	在 SQL Server 2005 错误日志中捕获输出。	主要针对死锁所涉及的节点。每个节点都有一个专用部分，并且最后一部分说明死锁牺牲品。	返回采用不符合 XML 架构定义 (XSD) 架构的类 XML 格式的信息。该格式有三个主要部分。第一部分声明死锁牺牲品；第二部分说明死锁所涉及的每个进程；第三部分说明与跟踪标志 1204 中的节点同义的资源。
标识属性	SPID:<x> ECID:<x>。标识并行进程中的系统进程 ID 线程。项 SPID:<x> ECID:0（其中，<x> 将替换为 SPID 值）表示主线程。项SPID:<x> ECID:<y>（其中，<x> 将替换为 SPID 值，<y> 大于 0）表示具有相同 SPID 的子线程。 BatchID（对于跟踪标志 1222 为 sbid）。标识代码执行从中请求锁或持有锁的批处理。多个活动的结果集 (MARS) 禁用后，BatchID 值为 0。MARS 启用后，活动批处理的值为 1 到n。如果会话中没有活动的批处理，则 BatchID 为 0。 Mode。指定线程所请求的、获得的或等待的特定资源的锁的类型。模式可以为 IS（意向共享）、S（共享）、U（更新）、IX（意向排他）、SIX（意向排他共享）和 X（排他）。有关详细信息，请参阅锁模式。 Line #（对于跟踪标志 1222 为 line）。列出发生死锁时当前批处理中正在执行的语句的行数。 Input Buf（对于跟踪标志 1222 为 inputbuf）。列出当前批处理中的所有语句。	Node。表示死锁链中的项数。 Lists。锁所有者可能属于以下列表： Grant List。枚举资源的当前所有者。 Convert List。枚举尝试将其锁转换为较高级别的当前所有者。 Wait List。枚举对资源的当前新锁请求。 Statement Type。说明线程对其具有权限的 DML 语句的类型（SELECT、INSERT、UPDATE 或 DELETE）。 Victim Resource Owner。指定 SQL Server 选择作为牺牲品来中断死锁循环的参与线程。选定的线程和所有的现有子线程都将终止。 Next Branch。表示死锁循环中涉及的两个或多个具有相同 SPID 的子线程。	deadlock victim。表示选为死锁牺牲品的任务的物理内存地址（请参阅 sys.dm_os_tasks）。如果任务为无法解析的死锁，则它可能为 0（零）。不能选择正在回滚的任务作为死锁牺牲品。 executionstack。表示发生死锁时正在执行的 Transact-SQL 代码。 priority。表示死锁优先级。在某些情况下，数据库引擎可能在短时间内改变死锁优先级以更好地实现并发。 logused。任务使用的日志空间。 owner id。可控制请求的事务的 ID。 status。任务的状态。为下列值之一： pending。正在等待工作线程。 runnable。可以运行，但正在等待量程。 running。当前正在计划程序上运行。 suspended。执行已挂起。 done。任务已完成。 spinloop。正在等待自旋锁释放。 waitresource。任务需要的资源。 waittime。等待资源的时间（毫秒）。 schedulerid。与此任务关联的计划程序。请参阅 sys.dm_os_schedulers (Transact-SQL)。 hostname。工作站的名称。 isolationlevel。当前事务隔离级别。 Xactid。可控制请求的事务的 ID。 currentdb。数据库的 ID。 lastbatchstarted。客户端进程上次启动批处理执行的时间。 lastbatchcompleted。客户端进程上次完成批处理执行的时间。 clientoption1 和 clientoption2。此客户端连接上的 Set 选项。这是一个位掩码，包含有关 SET 语句（如 SET NOCOUNT 和 SET XACTABORT）通常控制的选项的信息。 associatedObjectId。表示 HoBT（堆或 b 树）ID。
资源属性	RID：标识持有锁或请求锁的表中的单行。RID 表示为 RID: db_id:file_id:page_no:row_no。例如，RID: 6:1:20789:0。 OBJECT：标识持有锁或请求锁的表。OBJECT 表示为 OBJECT: db_id:object_id。例如，TAB: 6:2009058193。 KEY：标识持有锁或请求锁的索引中的键范围。KEY 表示为 KEY: db_id:hobt_id (index key hash value)。例如，KEY: 6:72057594057457664 (350007a4d329)。 PAG：标识持有锁或请求锁的页资源。PAG 表示为 PAG: db_id:file_id:page_no。例如，PAG: 6:1:20789。 EXT：标识区结构。EXT 表示为 EXT: db_id:file_id:extent_no。例如，EXT: 6:1:9。 DB：标识数据库锁。DB 以下列方式之一表示： DB: db_id DB: db_id[BULK-OP-DB]，这标识备份数据库持有的数据库锁。 DB: db_id[BULK-OP-LOG]，这标识此特定数据库的备份日志持有的锁。 APP：标识应用程序资源持有的锁。APP 表示为 APP: lock_resource。例如，APP: Formf370f478。 METADATA：表示死锁所涉及的元数据资源。由于 METADATA 具有许多子资源，因此，返回的值取决于已发生死锁的子资源。例如，METADATA.USER_TYPE 将返回user_type_id = <integer_value>。有关 METADATA 资源和子资源的详细信息，请参阅 sys.dm_tran_locks (Transact-SQL)。 HOBT：表示死锁所涉及的堆或 b 树。	此跟踪标志没有任何排他。	此跟踪标志没有任何排他。

跟踪标志 1204 示例

下面的示例显示启用跟踪标志 1204 时的输出。在此示例中，节点 1 中的表为没有索引的堆，节点 2 中的表为具有非聚集索引的堆。节点 2 中索引键在发生死锁时正在进行更新。

复制代码

Deadlock encountered .... Printing deadlock information Wait-for graph Node:1 RID: 6:1:20789:0 CleanCnt:3 Mode:X Flags: 0x2 Grant List 0: Owner:0x0315D6A0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6 Input Buf: Language Event: BEGIN TRANSACTION    EXEC usp_p2 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868) Node:2 KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0 Grant List 0: Owner:0x0315D140 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4 SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6 Input Buf: Language Event: BEGIN TRANSACTION        EXEC usp_p1 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380) Victim Resource Owner: ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

跟踪标志 1222 示例

下面的示例显示启用跟踪标志 1222 时的输出。在此示例中，一个表为没有索引的堆，另一个表为具有非聚集索引的堆。在第二个表中，索引键在发生死锁时正在进行更新。

复制代码

deadlock-list deadlock victim=process689978 process-list process id=process6891f8 taskpriority=0 logused=868 waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 transactionname=user_transaction lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:42.733 lastbatchcompleted=2005-09-05T11:22:42.733 clientapp=Microsoft SQL Server Management Studio - Query hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user isolationlevel=read committed (2) xactid=310444 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 executionStack frame procname=AdventureWorks2008R2.dbo.usp_p1 line=6 stmtstart=202 sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000 UPDATE T2 SET COL1 = 3 WHERE COL1 = 1; frame procname=adhoc line=3 stmtstart=44 sqlhandle=0x01000600856aa70f503b8104000000000000000000000000 EXEC usp_p1 inputbuf BEGIN TRANSACTION        EXEC usp_p1 process id=process689978 taskpriority=0 logused=380 waitresource=KEY: 6:72057594057457664 (350007a4d329) waittime=5015 ownerId=310462 transactionname=user_transaction lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 lastbatchcompleted=2005-09-05T11:22:44.077 clientapp=Microsoft SQL Server Management Studio - Query hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user isolationlevel=read committed (2) xactid=310462 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 executionStack frame procname=AdventureWorks2008R2.dbo.usp_p2 line=6 stmtstart=200 sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000 UPDATE T1 SET COL1 = 4 WHERE COL1 = 1; frame procname=adhoc line=3 stmtstart=44 sqlhandle=0x01000600d688e709b85f8904000000000000000000000000 EXEC usp_p2 inputbuf BEGIN TRANSACTION         EXEC usp_p2 resource-list ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2008R2.dbo.T2 id=lock3136940 mode=X associatedObjectId=72057594057392128 owner-list owner id=process689978 mode=X waiter-list waiter id=process6891f8 mode=U requestType=wait keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2008R2.dbo.T1 indexname=nci_T1_COL1 id=lock3136fc0 mode=X associatedObjectId=72057594057457664 owner-list owner id=process6891f8 mode=X waiter-list waiter id=process689978 mode=U requestType=wait

事件探查器死锁图形事件

这是 SQL Server Profiler中表示死锁所涉及的任务和资源的图形描述的事件。下面的示例显示启用死锁图形事件时 SQL Server Profiler 的输出。