Sqlserver 死锁指南

1、本文内容

• 了解死锁
• 检测和结束死锁
• 死锁检测
• 死锁信息工具
• 处理死锁
• 将死锁减至最少
• 导致死锁
• 优化的锁定和死锁

适用于：

• SQL Server
• Azure SQL 数据库
• Azure SQL 托管实例
• Azure Synapse Analytics
• Analytics Platform System (PDW)

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

2. 了解死锁

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

• 事务 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 数据库引擎的实例中，当获取非数据库资源（例如，内存或线程）时，会话可能会死锁。

显示事务死锁的关系图。

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

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

3. 检测和结束死锁

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

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

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

4. 可以死锁的资源

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

• 锁。 等待获取资源（如对象、页、行、元数据和应用程序）的锁可能导致死锁。 例如，事务 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 正在等待用户资源。 死锁状态的逻辑说明如下

5. 死锁检测

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

6. 死锁信息工具

要查看死锁信息，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 会显示死锁中涉及的任务和资源的图形描述，如以下示例所示：

以下查询可以查看 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;

下面是结果集：

以下示例显示了在结果第一行中选择 Event_Data 中的链接后的输出：

<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>

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

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

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

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

7. 处理死锁

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

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

8. 将死锁减至最少

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

9. 导致死锁

此示例在启用 READ_COMMITTED_SNAPSHOT 时，在具有默认架构和数据的 AdventureWorksLT2019 示例数据库中运行。

要下载此示例，请访问 AdventureWorks 示例数据库。
https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

若要造成死锁，需要将两个会话连接到 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 文件以获得可视死锁图。 死锁图应如下图所示。

10. 优化的锁定和死锁

适用于： Azure SQL 数据库

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

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

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 包含特定于优化锁定的元素和属性：