Share via

死锁指南

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

本文深入讨论 SQL Server 数据库引擎中的死锁。 死锁是由数据库中的竞争的并发锁引起的，通常是在多步骤事务中。 有关事务锁定的详细信息，请参阅事务锁定和行版本控制指南。

有关识别和防止 Azure SQL 数据库中的死锁的更多具体信息，请参阅分析和防止 Azure SQL 数据库中的死锁。

了解死锁

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

  • 事务 A 获取了行 1 的共享锁。
  • 事务 B 获取了行 2 的共享锁。
  • 现在，事务 A 请求行 2 的排他锁，但在事务 B 完成并释放其对行 2 持有的共享锁之前被阻止。
  • 现在，事务 B 请求行 1 的排他锁，但在事务 A 完成并释放其对行 1 持有的共享锁之前被阻止。

事务 B 完成之后事务 A 才能完成，但是事务 B 由事务 A 阻塞。该条件也称为循环依赖关系：事务 A 依赖于事务 B，事务 B 通过对事务 A 的依赖关系关闭循环。

除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。 SQL Server 数据库引擎死锁监视器定期检查陷入死锁的任务。 如果监视器检测到循环依赖关系，将选择其中一个任务作为牺牲品，然后终止其事务并提示错误。 这样，其他任务就可以完成其事务。 对于事务以错误终止的应用程序，它还可以重试该事务，但通常要等到与它一起陷入死锁的其他事务完成后执行。

死锁经常与正常阻塞混淆。 事务请求被其他事务锁定的资源的锁时，发出请求的事务一直等到该锁被释放。 默认情况下，除非设置了 LOCK_TIMEOUT，否则 SQL Server 事务不会超时。 因为发出请求的事务未执行任何操作来阻塞拥有锁的事务，所以该事务是被阻塞，而不是陷入了死锁。 最后，拥有锁的事务将完成并释放锁，然后发出请求底事务将获取锁并继续执行。 死锁几乎可以立即解决，而锁定在理论上可以无限期地持续。 死锁有时称为抱死。

不只是关系数据库管理系统，任何多线程系统上都会发生死锁，并且对于数据库对象的锁之外的资源也会发生死锁。 例如，多线程操作系统中的一个线程要获取一个或多个资源（例如，内存块）。 如果要获取的资源当前为另一线程所拥有，则第一个线程可能必须等待拥有线程释放目标资源。 这就是说，对于该特定资源，等待线程依赖于拥有线程。 在 SQL Server 数据库引擎的实例中，当获取非数据库资源（例如，内存或线程）时，会话可能会死锁。

Diagram showing a transaction deadlock.

在示例中，对于 Part 表锁资源，事务 T1 依赖于事务 T2。 同样，对于 Supplier 表锁资源，事务 T2 依赖于事务 T1。 因为这些依赖关系形成了一个循环，所以在事务 T1 和事务 T2 之间存在死锁。

当表进行了分区并且 ALTER TABLE 的 LOCK_ESCALATION 设置设为 AUTO 时也会发生死锁。 当 LOCK_ESCALATION 设为 AUTO 时，通过允许 SQL Server 数据库引擎在 HoBT 级别而非表级别锁定表分区会增加并发情况。 但是，当单独的事务在某个表中持有分区锁并希望在其他事务分区上的某处持有锁时，会导致发生死锁。 通过将 LOCK_ESCALATION 设置为 TABLE 可以避免这种类型的死锁，但此设置会因强制某个分区的大量更新以等待某个表锁而减少并发情况。

检测和结束死锁

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

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

Diagram showing the tasks in a deadlock state.

SQL Server 数据库引擎会自动检测 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）作为用户定义函数执行，分别获取 10 MB 和 20 MB 的内存。 如果每个查询需要 30 MB 而可用总内存为 20 MB，则 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 执行的存储过程已获取会话互斥体。 如果执行该存储过程花费了很长时间，SQL Server 数据库引擎会认为存储过程正在等待用户的输入。 用户等待 U2 的结果集时，用户请求 U2 正在等待会话互斥体，U1 正在等待用户资源。 死锁状态的逻辑说明如下：

    Diagram of the logical flow of a stored procedure in MARS.

死锁检测

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

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

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

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

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

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

使用 CLR 时，死锁监视器将自动检测托管过程中访问的同步资源（监视器、读取器/编写器锁和线程联接）的死锁。 但是，死锁是通过在已选为死锁牺牲品的过程中引发异常来解决的。 因此，请务必理解异常不会自动释放牺牲品当前拥有的资源；必须显式释放资源。 用于标识死锁牺牲品的异常与异常行为一样，也会被捕获和解除。

死锁信息工具

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

注意

本部分包含有关扩展事件、跟踪标志和跟踪的信息，但死锁扩展事件是捕获死锁信息的建议方法。

死锁扩展事件

自 SQL Server 2012 (11.x) 起，应使用 xml_deadlock_report 扩展事件 (xEvent)，而不使用 SQL 跟踪或 SQL Profiler 中的死锁图事件类。

此外，自 SQL Server 2012 (11.x) 起，当发生死锁时，system_health 会话已捕获所有包含死锁图的 xml_deadlock_report xEvent。 由于 system_health 会话默认情况下处于启用状态，因此不需要将单独的 xEvent 会话配置为捕获死锁信息。 无需执行任何其他操作即可使用 xml_deadlock_report xEvent 捕获死锁信息。

捕获的死锁图通常具有三个不同的节点：

  • 牺牲品列表。 死锁牺牲品进程标识符。
  • 进程列表。 死锁中涉及的全部进程的信息。
  • 资源列表。 死锁中涉及的资源的信息。

如果记录 system_health xEvent，打开 xml_deadlock_report 会话文件或环形缓冲区，Management Studio 会显示死锁中涉及的任务和资源的图形描述，如以下示例所示：

A screenshot from SSMS of a XEvent Deadlock Graph visual diagram.

以下查询可以查看 system_health 会话环形缓冲区捕获的所有死锁事件：

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

下面是结果集。

A screenshot from SSMS of the system_health xEvent query result.

以下示例显示了选择以上结果的第一个链接之后的输出：

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

有关详细信息，请参阅 使用 system_health 会话

跟踪标志 1204 和跟踪标志 1222

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

重要

避免在导致死锁的工作负载密集型系统上使用跟踪标志 1204 和 1222。 使用这些跟踪标志可能会导致性能问题。 请改用死锁扩展事件捕获必要的信息。

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

properties 跟踪标志 1204 和跟踪标志 1222 仅跟踪标志 1204 仅跟踪标志 1222
输出格式 在 SQL Server 错误日志中捕获输出。 主要针对死锁所涉及的节点。 每个节点都有一个专用部分，并且最后一部分说明死锁牺牲品。 返回采用不符合 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。

模式。 指定线程所请求的、获得的或等待的特定资源的锁的类型。 模式可以为 IS（意向共享）、S（共享）、U（更新）、IX（意向排他）、SIX（意向排他共享）和 X（排他）。

行编号（对于跟踪标志 1222 为行）。 列出发生死锁时当前批处理中正在执行的语句的行数。

Input Buf（对于跟踪标志 1222 为 inputbuf）。 列出当前批处理中的所有语句。		 Node。 表示死锁链中的项数。

List。 锁所有者可能属于以下列表：

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 (Transact-SQL)）。 如果任务为无法解析的死锁，则它可能为 0（零）。 不能选择正在回滚的任务作为死锁牺牲品。

executionstack。 表示发生死锁时正在执行的 Transact-SQL 代码。

Priority。 表示死锁优先级。 在某些情况下，SQL Server 数据库引擎可能在短时间内改变死锁优先级以更好地实现并发。

logused。 任务使用的日志空间。

所有者 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（索引键哈希值）。 例如 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=2022-02-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=2022-02-05T11:22:42.733   
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=2022-02-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=2022-02-05T11:22:44.077   
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=AdventureWorks2022.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=AdventureWorks2022.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 Profiler 中表示死锁所涉及的任务和资源的图形描述的事件。 以下示例显示启用死锁图形事件时 SQL Profiler 的输出。

重要

SQL Profiler 创建跟踪，该跟踪已于 2016 年弃用并由扩展事件替换。 与跟踪相比，扩展事件的性能开销要少得多，并且可配置性要高得多。 请考虑使用扩展事件死锁事件而不是跟踪。

A screenshot from SSMS of the visual deadlock graph from a SQL trace.

有关死锁事件的详细信息，请参阅 Lock:Deadlock 事件类。 有关运行 SQL Profiler 死锁图的详细信息，请参阅保存死锁图 (SQL Server Profiler)。

扩展事件中 SQL 跟踪事件类具有等效项，请参阅与 SQL 跟踪事件类等效的扩展事件。 建议通过 SQL 跟踪使用扩展事件。

处理死锁

SQL Server 数据库引擎实例选择某事务作为死锁牺牲品时，将终止当前批处理，回退事务并将错误消息 1205 返回应用程序。

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

因为任何提交 Transact-SQL 查询的应用程序可被选为死锁牺牲品，应用程序应具有可处理错误消息 1205 的错误处理程序。 如果应用程序不处理该错误，可以继续操作，但是不知道自己的事务已回滚而且可能出错。

通过实现捕获 1205 号错误消息的错误处理程序，使应用程序得以处理该死锁情况并采取补救措施（例如，可以自动重新提交陷入死锁中的查询）。 通过自动重新提交查询，用户不必知道发生了死锁。

应用程序在重新提交其查询前应短暂暂停。 这样会给死锁涉及的另一个事务一个机会来完成并释放构成死锁循环一部分的该事务的锁。 这将把重新提交的查询请求其锁时，死锁重新发生的可能性降到最低。

将死锁减至最少

尽管死锁不能完全避免，但遵守特定的编码惯例可以将发生死锁的机会降至最低。 将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务：

  • 回滚，撤消事务执行的所有工作。
  • 由于死锁时回滚而由应用程序重新提交。

下列方法有助于将死锁减至最少：

  • 按同一顺序访问对象。
  • 避免事务中的用户交互。 - 保持事务简短并处于一个批处理中。
  • 使用较低的隔离级别。
  • 使用基于行版本控制的隔离级别。
    • 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 on，以使读取提交的事务可以使用行版本控制。
    • 使用快照隔离。
  • 使用绑定连接。

按同一顺序访问对象

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。 例如，如果两个并发事务先获取 Supplier 表上的锁，然后获取 Part 表上的锁，则在其中一个事务完成之前，另一个事务将在 Supplier 表上被阻塞。 当第一个事务提交或回滚之后，第二个事务将继续执行，这样就不会发生死锁。 将存储过程用于所有数据修改可以使对象的访问顺序标准化。

A diagram of a deadlock.

避免事务中的用户交互

避免编写包含用户交互的事务，因为没有用户干预的批处理的运行速度远快于用户必须手动响应查询时的速度（例如回复输入应用程序请求的参数的提示）。 例如，如果事务正在等待用户输入，而用户去吃午餐或甚至回家过周末了，则用户就耽误了事务的完成。 这将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚后才会释放。 即使不出现死锁的情况，在占用资源的事务完成之前，访问同一资源的其他事务也会被阻塞。

保持事务简短并处于一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常会发生死锁。 事务的运行时间越长，它持有排他锁或更新锁的时间也就越长，从而会阻塞其他活动并可能导致死锁。

保持事务处于一个批处理中可以最小化事务中的网络通信往返量，减少完成事务和释放锁可能遭遇的延迟。

有关死锁的详细信息，请参阅事务锁定和行版本控制指南。

使用较低的隔离级别

确定事务是否能在较低的隔离级别上运行。 实现已提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。 使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短。 这样就减少了锁争用。

使用基于行版本控制的隔离级别

READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，在读取操作期间，已提交读隔离级别下运行的事务使用行版本控制而不是共享锁。

注意

某些应用程序依赖于已提交读隔离的锁定和阻塞行为。 对于这些应用程序，要启用此选项必须进行一些更改。

快照隔离也使用行版本控制，该级别在读操作期间不使用共享锁。 必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，事务才能在快照隔离下运行。

实现这些隔离级别可使得在读写操作之间发生死锁的可能性降至最低。

使用绑定连接

使用绑定连接，同一应用程序打开的两个或多个连接可以相互合作。 可以像主连接获取的锁那样持有次级连接获取的任何锁，反之亦然。 这样它们就不会互相阻塞。

停止事务

在死锁方案中，受害者事务会自动停止并回滚。 无需在死锁方案中停止事务。

导致死锁

注意

此示例在启用 READ_COMMITTED_SNAPSHOT 时，在具有默认架构和数据的 AdventureWorksLT2019 示例数据库中运行。 要下载此示例，请访问 AdventureWorks 示例数据库。

若要造成死锁，需要将两个会话连接到 AdventureWorksLT2019 数据库。 我们将这些会话称为会话 A 和会话 B。你只需在 SQL Server Management Studio (SSMS) 中创建两个查询窗口即可创建这两个会话。

在会话 A 中，运行以下 Transact-SQL。 此代码开始一个显式事务，并运行用于更新 SalesLT.Product 表的单个语句。 为此，该事务将获取针对表 SalesLT.Product 中的一行的更新 (U) 锁，该锁会转换为排他 (X) 锁。 我们将该事务保持打开状态。

BEGIN TRAN

    UPDATE SalesLT.Product SET SellEndDate = SellEndDate + 1
        WHERE Color = 'Red';

现在，在会话 B 中，运行以下 Transact-SQL。 此代码不会显式开始一个事务， 而是在自动提交事务模式下运行。 此语句更新 SalesLT.ProductDescription 表。 该更新将获取一个针对 SalesLT.ProductDescription 表中 72 行的更新 (U) 锁。 查询将联接到其他表，包括 SalesLT.Product 表。

UPDATE SalesLT.ProductDescription SET Description = Description
    FROM SalesLT.ProductDescription as pd
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    JOIN SalesLT.Product as p on
        pm.ProductModelID=p.ProductModelID
    WHERE p.Color = 'Silver';

为了完成此更新，会话 B 需要对表 SalesLT.Product 中的行（包括被会话 A 锁定的行）使用共享锁。会话 B 将在 SalesLT.Product 上被阻塞。

返回到会话 A。运行以下 Transact-SQL 语句。 这会运行第二个 UPDATE 语句作为打开的事务的一部分。

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red';

会话 A 中的第二个 update 语句将被 SalesLT.ProductDescription 上的会话 B 阻塞。

会话 A 和会话 B 现在相互阻塞。 两个事务都无法继续，因为它们都需要另一个事务锁定的资源。

几秒钟后，死锁监视器将识别出会话 A 和会话 B 中的事务正在相互阻塞，并且两者都无法取得进展。 你应会看到发生了死锁，其中会话 A 已选为死锁牺牲品。 会话 B 将成功完成。 会话 A 中将显示一条错误消息，其文本类似于以下内容：

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

如果未引发死锁，请验证示例数据库中是否已启用 READ_COMMITTED_SNAPSHOT。 死锁可能发生在任何数据库配置中，但此示例要求启用 READ_COMMITTED_SNAPSHOT。

然后，你可以在 system_health 扩展事件会话的 ring_buffer 目标中查看死锁的详细信息，该会话在 SQL Server 中默认启用并处于活动状态。 请考虑下列查询：

WITH cteDeadLocks ([Deadlock_XML]) AS (
  SELECT [Deadlock_XML] = CAST(target_data AS XML) 
  FROM sys.dm_xe_sessions AS xs
  INNER JOIN sys.dm_xe_session_targets AS xst 
  ON xs.[address] = xst.event_session_address
  WHERE xs.[name] = 'system_health'
  AND xst.target_name = 'ring_buffer'
 )
SELECT 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks c
 CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)
) AS x
ORDER BY when_occurred desc;

你可以通过选择将显示为超链接的单元格来查看 SSMS 内 Deadlock_XML 列中的 XML。 将此输出保存为 .xdl 文件，关闭，然后在 SSMS 中重新打开 .xdl 文件以获得可视死锁图。 死锁图应如下图所示。

A screenshot of a visual deadlock graph in an .xdl file in SSMS.

优化的锁定和死锁

适用于： Azure SQL 数据库

优化锁定引入了一种不同的锁定机制方法，该方法更改了涉及独占 TID 锁的死锁的报告方式。 在死锁报告 <resource-list> 中的每个资源下，每个 <xactlock> 元素会报告死锁每个成员的锁的基础资源和特定信息。

考虑以下启用了优化锁定的示例：

CREATE TABLE t2 
(a int PRIMARY KEY not null 
,b int null); 

INSERT INTO t2 VALUES (1,10),(2,20),(3,30) 
GO

两个会话中的以下 TSQL 命令将在表 t2 上创建死锁：

在会话 1 中：

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1;

在会话 2 中：

--session 2:
BEGIN TRAN bar 
UPDATE t2 SET b = b+ 10 WHERE a = 2;

在会话 1 中：

--session 1:
UPDATE t2 SET b = b + 100 WHERE a = 2;

在会话 2 中：

--session 2:
UPDATE t2 SET b = b + 20 WHERE a = 1;

这种竞争 UPDATE 语句场景会导致死锁。 在本例中，是一个键锁资源，其中每个会话在自己的 TID 上持有 X 锁，并等待另一个 TID 上的 S 锁，从而导致死锁。 以下作为死锁报告捕获的 XML 包含特定于优化锁定的元素和属性：

A screenshot of the XML of a deadlock report showing the UnderlyingResource nodes and keylock nodes specific to optimized locking.

相关内容