Leer en inglés

Compartir a través de

分析和防止 Azure SQL 数据库中的死锁

适用于:Azure SQL 数据库

本文介绍如何识别死锁,使用死锁图和查询存储来识别死锁中的查询,以及规划和测试更改以防止重复发生死锁。

本文重点介绍如何识别和分析锁争用导致的死锁。 详细了解可能发生死锁的资源中的其他类型的死锁。

死锁的发生方式

默认情况下,Azure SQL 数据库中的每个新数据库都启用了 读取提交的快照隔离(RCSI) 数据库设置。 RCSI 使用行版本控制提高并发。在启用了 RCSI 的情况下,读取数据的会话与写入数据的会话之间的阻塞可最大程度地减轻。 但是,Azure SQL 数据库中的数据库仍可能发生阻塞和死锁,因为:

  • 修改数据的查询可能会相互阻止。

  • 查询可能在导致更高阻塞的隔离级别下运行。 可以通过 Transact-SQL 中的客户端库方法、 查询提示SET TRANSACTION ISOLATION LEVEL 指定隔离级别。

  • RCSI 可能会禁用,导致数据库使用共享 (S) 锁来保护SELECT在已提交读取隔离级别下运行的语句。 这可能会增加阻塞和死锁。

示例死锁

当两个或多个任务因为每个任务都锁住了另一个任务试图锁定的资源而永久互相阻塞时,就会发生死锁。 死锁也称为循环依赖关系:在两任务死锁的情况下,事务 A 依赖于事务 B,事务 B 又依赖于事务 A,从而达成闭环。

例如:

  1. 会话 A 开始一个显式事务并运行一条 update 语句,该语句获取对表 SalesLT.Product 中一行的更新 (U) 锁,此锁将转换为排他 (X) 锁

  2. 会话 B 运行一条 update 语句,该语句修改 SalesLT.ProductDescription 表。 该更新语句连接到SalesLT.Product表,以查找要更新的正确行。

    • 会话 B 获取对 SalesLT.ProductDescription 表中 72 行的更新 (U) 锁。

    • 会话 B 需要对表 中的行(包括被会话 A 锁定的行)使用共享锁。会话 B 在 SalesLT.Product 上被阻塞。

  3. 会话 A 会继续其事务,并且现在会对 SalesLT.ProductDescription 表运行更新。 会话 A 被会话 B 在 SalesLT.ProductDescription 上阻塞。

显示两个会话处于死锁状态的图示。每个会话都拥有对方进程需要才能继续的资源。

死锁中的所有事务将无限期等待,除非其中一个参与的事务被回滚,例如,因为其会话已终止。

数据库引擎死锁监视器定期检查陷入死锁的任务。 如果死锁监视器检测到循环依赖项,它将选择其中一个任务作为牺牲品,并终止其事务,并出现错误 1205:Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. 以这种方式中断死锁可让死锁中的其他任务完成其事务。

Nota

请在本文的死锁进程列表部分中详细了解选择死锁牺牲品的标准。

两个会话之间的死锁的关系图。一个会话被选为死锁牺牲品。

被选为死锁牺牲品的事务所属的应用程序应当重试该事务,该事务通常会在死锁中涉及的其他事务完成后成功执行。

最好在重试前引入一个简短的随机延迟,以避免再次遇到相同的死锁。 请详细了解如何设计针对暂时性错误的重试逻辑

Azure SQL 数据库中的默认隔离级别

默认情况下,Azure SQL 数据库中的新数据库会启用读取已提交的快照 (RCSI)。 RCSI 将“读取已提交的内容”隔离级别的行为更改为使用行版本控制来提供语句级一致性,而无需对 SELECT 语句使用共享 (S) 锁。

在启用了 RCSI 的情况下:

  • 读取数据的语句不会阻止修改数据的语句。
  • 修改数据的语句不会阻止读取数据的语句。

对于 Azure SQL 数据库中的新数据库,默认还会启用快照隔离级别。 快照隔离是一个附加的基于行的隔离级别,它为数据提供事务级一致性,并使用行版本来选择要更新的行。 若要使用快照隔离,查询或连接必须将其事务隔离级别显式设置为 SNAPSHOT。 只能在为数据库启用了快照隔离时执行此操作。

可以使用 Transact-SQL 确定是否启用了 RCSI 和/或快照隔离。 连接到 Azure SQL 数据库中的数据库并运行以下查询:

SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

如果启用了 RCSI,则 is_read_committed_snapshot_on 列将返回值 1。 如果启用了快照隔离,则 snapshot_isolation_state_desc 列将返回值 ON

如果 Azure SQL 数据库中的某个数据库禁用 RCSI,请在重新启用 RCSI 之前调查禁用 RCSI 的原因。 应用程序代码可能期望读取数据的查询将被写入数据的查询阻塞,从而导致启用了 RCSI 时在争用条件下会产生错误结果。

解释死锁事件

Azure SQL 数据库中的死锁管理器检测到死锁并选择某个事务作为牺牲品后,会发出死锁事件。 换句话说,如果为死锁设置了警报,则会在解决单个死锁后触发通知。 无需为此死锁执行任何用户操作。 应用程序应具备重试逻辑,以便在收到错误 1205 后自动继续:Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

但是,设置警报很有用,因为死锁可能再次发生。 死锁警报使你能够调查数据库中是否发生了重复死锁模式,在这种情况下,可以选择采取措施来防止死锁重新出现。 请在本文的监视与警报部分了解更多关于死锁警报功能的信息。

防止死锁的最佳方法

防止死锁再次发生的最低风险方法通常是优化非聚集索引以优化死锁中涉及的查询。

  • 此方法的风险较低,因为优化非聚集索引不需要更改查询代码本身,因此在重新编写导致错误数据返回给用户的 Transact-SQL 时会降低用户错误的风险。

  • 有效的非聚集索引优化有助于查询更高效地查找要读取和修改的数据。 通过减少查询需要访问的数据量,可以减少阻塞的可能性,并且通常可以防止死锁。

在某些情况下,创建或优化聚集索引可以减少阻塞和死锁。 由于聚集索引包含在所有非聚集索引定义中,因此,创建或修改聚集索引在具有现有非聚集索引的大型表上可能是 IO 密集型的耗时操作。 请详细了解聚集索引设计指南

在索引优化无法成功防止死锁时,可以使用其他方法:

  • 如果死锁仅在为死锁中所涉的某个查询选择特定执行计划时才会发生,那么通过查询存储来强制执行查询计划可能会防止死锁再次出现。

  • 为死锁中涉及的一个或多个事务重新编写 Transact-SQL 也可有助于防止死锁。 将显式事务分解为较小的事务需要仔细编码和测试,以确保发生并发修改时的数据有效性。

请在本文的防止死锁再次发生部分详细了解这些方法中的每一个。

监视死锁并针对死锁发出警报

在本文中,我们使用 AdventureWorksLT 示例数据库为死锁设置警报,导致示例死锁,分析示例死锁图,并测试更改以防止死锁重新出现。

本文使用 SQL Server Management Studio (SSMS) 客户端,因为它包含在交互式视觉模式下显示死锁图形的功能。 可以使用其他客户端(如 Azure Data Studio )来遵循这些示例,但可能只能将死锁图形视为 XML。

创建 AdventureWorksLT 数据库

若要按照这些示例操作,请在 Azure SQL 数据库中创建新数据库,然后选择“示例”数据作为“数据源”。

如需有关如何使用 Azure 门户、Azure CLI 或 PowerShell 创建 AdventureWorksLT 的详细说明,请在快速入门:创建 Azure SQL 数据库单一数据库中选择你所选的方法。

在 Azure 门户中设置死锁警报

若要为死锁事件设置警报,请按照使用 Azure 门户为 Azure SQL 数据库和 Azure Synapse Analytics 创建警报一文中的步骤操作。

选择“死锁”作为警报的信号名称。 配置“操作组”以使用你选择的方法(例如“电子邮件/短信”操作类型)通知你。

使用扩展事件在 Azure SQL 数据库中收集死锁图

死锁图是有关死锁中涉及的进程和锁的丰富信息来源。 若要在 Azure SQL 数据库中使用扩展事件 (XEvents) 收集死锁图,请捕获 sqlserver.database_xml_deadlock_report 事件。

可以使用环形缓冲区目标事件文件目标通过 XEvents 收集死锁图。 下表汇总了有关选择适当目标类型的注意事项:

方法 优点 注意事项 使用场景
环形缓冲区目标 - 仅使用 Transact-SQL 进行简单设置。 - 当出于任何原因(例如脱机数据库或数据库故障转移)停止 XEvents 会话时,将会清除事件数据。

- 数据库资源用于维护环缓冲区中的数据和查询会话数据。
- 收集用于测试和学习的示例跟踪数据。

- 在无法立即使用事件文件目标设置会话时针对短期需求而创建。

- 在设置了自动化过程以将跟踪数据保存到表中时,用作跟踪数据的“着陆架”
事件文件目标 - 将事件数据保存到 Azure 存储中的 Blob,因此即使在会话停止后数据也可用。

- 可以从 Azure 门户或 Azure 存储资源管理器 下载事件文件并在本地进行分析,这不需要使用数据库资源来查询会话数据。
- 设置较为复杂,需要配置 Azure 存储容器和数据库范围凭证。 - 通常在以下情况下使用:你希望事件数据即使在事件会话停止后也仍保存。

- 你想要运行一个跟踪,该跟踪生成的事件数据量比要在内存中保留的要多。

选择要使用的目标类型:

事件文件目标功能将死锁图形持久化到文件中,因此即使在 XEvents 会话停止后,它们仍可用。 事件文件目标还让你可以捕获更多的死锁图而无需为环形缓冲区分配额外的内存。 事件文件目标适合长期使用,并且适用于收集较大数量的跟踪数据。

若要创建写入事件文件目标的 XEvents 会话,我们:

  1. 使用 Azure 门户配置 Azure 存储容器以保存跟踪文件。
  2. 使用 Transact-SQL 创建数据库范围的凭据。
  3. 使用 Transact-SQL 创建 XEvents 会话。

配置 Azure 存储容器

若要配置 Azure 存储容器,请先创建或选择现有的 Azure 存储帐户,然后创建容器。 为容器生成共享访问签名 (SAS) 令牌。 本部分介绍如何在 Azure 门户中完成此过程。

Nota

如果要使用 PowerShell 创建和配置 Azure 存储 blob 容器,请参阅 Azure SQL 数据库中扩展事件的事件文件目标代码。 或者,你可能会发现 使用 Azure 存储资源管理器 创建和配置 Azure 存储 Blob 容器而不是使用 Azure 门户会很方便。

创建或选择 Azure 存储帐户

可以使用现有 Azure 存储帐户或创建新的 Azure 存储帐户来承载跟踪文件的容器。

若要使用现有 Azure 存储帐户,请执行以下操作:

  1. 在 Azure 门户中导航到要使用的资源组。
  2. 在“ 概述 ”窗格的“ 资源”下,将 “类型 ”下拉列表设置为 “存储帐户”。
  3. 选择要使用的存储帐户。

若要创建新的 Azure 存储帐户,请按照创建 Azure 存储帐户中的步骤操作。 在最后一步中选择“转到资源”以完成该过程。

创建容器

在 Azure 门户中的存储帐户页内:

  1. 在“数据存储”下,选择“容器”。
  2. 选择“+ 容器”按钮来新建容器。 此时会显示“新建容器”窗格。
  3. 在“名称”下输入容器的名称。
  4. 选择“创建”。
  5. 创建容器后,从列表中选择该容器。

创建共享访问令牌

在 Azure 门户中的容器页内:

  1. 在“设置”下,选择“共享访问令牌” 。

  2. 将“签名方法”单选按钮保留设置为默认选择(“帐户密钥”)。

  3. “权限” 下拉列表下,选择“ 读取”、“ 写入”和 “列表 ”权限。

  4. 将“开始时间”设置为你希望能够写入跟踪文件的日期和时间。 (可选)在“ 开始”下面的下拉列表中配置时区。

  5. 将“到期”设置为你希望这些权限过期的日期和时间。 (可选)在 “到期时间”下面的下拉列表中配置时区。 如果愿意,你可以将此日期设置为将来的日期,例如 10 年。

  6. 选择“生成 SAS 令牌和 URL”。 Blob SAS 令牌和 Blob SAS URL 显示在屏幕上。

  7. 复制并保存 Blob SAS 令牌和 Blob SAS URL 值,以便在后续步骤中使用。

创建数据库范围的凭据

使用 SSMS 连接到 Azure SQL 数据库中的数据库以运行以下步骤。

若要创建数据库范围的凭据,必须首先在数据库中创建数据库主密钥 (DMK),如果该密钥不存在的话。

如不存在 DMK,请运行以下 Transact-SQL 来创建:

IF 0 = (SELECT COUNT(*)
        FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101
              AND name = N'##MS_DatabaseMasterKey##')
    BEGIN
        PRINT N'Creating master key';
        CREATE MASTER KEY;
    END
ELSE
    BEGIN
        PRINT N'Master key already exists, no action taken';
    END
GO

接下来,使用以下 Transact-SQL 创建数据库范围的凭据。 运行代码之前:

  • 修改 URL 以反映你的存储帐户名称和容器名称。 此 URL 存在于创建共享访问令牌时复制的 Blob SAS URL 的开头。 你只需获取字符串中第一个 ? 之前的文本。

  • 修改 SECRET 以包含你在创建共享访问令牌时复制的 Blob SAS 令牌值。

CREATE DATABASE SCOPED CREDENTIAL [https://yourstorageaccountname.blob.core.chinacloudapi.cn/yourcontainername]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=<sig>';
GO

创建 XEvents 会话

使用以下 Transact-SQL 创建并启动 XEvents 会话。 运行语句之前:

  • 替换 filename 值以反映你的存储帐户名称和容器名称。 此 URL 存在于创建共享访问令牌时复制的 Blob SAS URL 的开头。 您只需要获取字符串中第一个 ? 之前的文本。

  • (可选)更改已存储的文件名。 此处指定的文件名将是用于存储事件数据的 blob 的实际文件名的一部分:附加其他值,以便所有事件文件都具有唯一的名称。

  • (可选)将其他事件添加到会话。

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = 'https://yourstorageaccountname.blob.core.chinacloudapi.cn/yourcontainername/deadlocks.xel'
)
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

在 AdventureWorksLT 中造成死锁

Nota

当启用 RCSI 时,此示例在 AdventureWorksLT 数据库中使用默认架构和数据运行。 有关创建数据库的说明,请参阅创建 AdventureWorksLT 数据库

若要造成死锁,你需要将两个会话连接到 AdventureWorksLT 数据库。 我们将这些会话称为 会话 A会话 B

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

BEGIN TRANSACTION;

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
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

若要完成此更新,会话 B 需要对表SalesLT.Product上的行,包括由会话 A锁定的行,进行共享 (S) 锁。会话 BSalesLT.Product处被阻塞。

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

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

会话 A中的第二个更新语句被会话 BSalesLT.ProductDescription上阻止。

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

几秒钟后,死锁监视器确定 会话 A会话 B 中的事务相互阻止,并且两者都无法取得进展。 你应会看到发生了死锁,其中会话 A 已选为死锁牺牲品。 会话 A 中会显示一条错误消息,其文本类似于以下内容:

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

会话 B 成功完成。

如果在 Azure 门户中设置了死锁警报,应在死锁发生后不久收到通知。

查看 XEvents 会话中的死锁图表

如果设置了 XEvents 会话以收集死锁,并且在会话启动后发生了死锁,则可查看以交互式图形显示的死锁图以及该死锁图的 XML。

可以使用不同的方法来获取环形缓冲区目标和事件文件目标的死锁信息。 选择已用于 XEvents 会话的目标:

如果设置了要写入事件文件的 XEvents 会话,则可以从 Azure 门户下载文件并在本地查看这些文件,也可以使用 Transact-SQL 查询事件文件。

建议从 Azure 门户下载文件,因为此方法不需要使用数据库资源来查询会话数据。

(可选)重启 XEvents 会话

如果“扩展事件”会话当前正在运行并写入到事件文件目标,则写入到的 Blob 容器将在 Azure 门户中具有“已租赁”租约状态。 大小是文件的最大大小。 若要下载较小的文件,可以在下载文件之前停止并重启扩展事件会话。 这会导致文件将其 租约状态 更改为 “可用”,文件大小将是文件中事件使用的空间。

若要停止并重启 XEvents 会话,请连接到数据库并运行以下 Transact-SQL。 在运行代码之前,请将 XEvents 会话的名称替换为相应的值。

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = STOP;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

从 Azure 门户下载跟踪文件

若要跨多个文件查看已收集的死锁事件,请将事件会话文件下载到本地计算机,并在 SSMS 中查看这些文件。

Nota

还可以使用 Azure 存储资源管理器快速方便地从 Azure 存储中的 blob 容器下载事件会话文件。

若要从 Azure 门户下载文件,请执行以下操作:

  1. 在 Azure 门户中导航到承载你的容器的存储帐户。
  2. 在“数据存储”下,选择“容器”。
  3. 选择包含 XEvent 跟踪文件的容器。
  4. 对于要下载的每个文件,请选择“...”,然后选择“下载”。

在 SSMS 中查看 XEvents 跟踪文件

如果已下载多个文件,则可以在 SSMS 的 XEvents 查看器中将所有文件中的事件一起打开。 为此,请执行以下操作:

  1. 打开 SSMS。
  2. 依次选择“文件”、“打开”、“合并扩展事件文件...”
  3. 选择 添加
  4. 导航到下载了文件的目录。 使用 Shift 键选择多个文件。
  5. 选择“打开”。
  6. 在“合并扩展事件文件”对话框中选择“确定”。

如果您已下载单个文件,请右键单击该文件,选择“打开方式”,然后选择“SSMS”。 这会在 SSMS 中打开 XEvents 查看器。

通过选择相关时间戳在收集的事件之间导航。 若要查看死锁的 XML,请双击下侧窗格中的 xml_report 行。

使用 Transact-SQL 查询跟踪文件

Importante

不建议使用此方法查询大型(1 GB 和更大的)XEvents 跟踪文件,因为它占用数据库或弹性池中的大量内存。

若要使用 Transact-SQL 从Azure 存储容器查询 XEvents 跟踪文件,必须提供跟踪文件的确切文件名。 此外,还必须在具有访问存储凭据的数据库上下文中运行查询,也就是说,在创建 XEvents 文件的同一数据库中运行查询。

运行以下 Transact-SQL 以查询当前活动的 XEvents 跟踪文件。 在运行查询之前,请将 @tracename 替换为 xEvents 会话的名称。

DECLARE @tracename AS sysname = N'deadlocks_eventfile', @filename AS NVARCHAR (2000);

WITH eft
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'event_file')
SELECT @filename = ft.evtdata.value('(@name)[1]', 'nvarchar(2000)')
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') AS ft(evtdata);

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

若要查询非活动文件,请在 Azure 门户中导航到存储帐户和容器以确定文件名。

针对数据库运行以下 Transact-SQL 查询,以查询特定的 XEvents 文件。 在运行查询之前,请替换 @filename 的 URL 中的存储帐户名称、容器名称和文件名:

DECLARE @filename AS NVARCHAR (2000) = N'https://<storage-account>.blob.core.chinacloudapi.cn/yourcontainername/yourfilename.xel';

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

以 XML 格式查看和保存死锁图

以 XML 格式查看死锁图让你可以复制死锁中涉及的 Transact-SQL 语句的 inputbuffer。 你还可以用文本格式分析死锁。

如果已使用 Transact-SQL 查询返回死锁图信息,那么,若要查看死锁图 XML,请从任意行选择 deadlock_xml 列中的值,以在 SSMS 的新窗口中打开死锁图的 XML。

此示例死锁图的 XML 为:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

若要将死锁图保存为 XML 文件,请执行以下操作:

  1. 依次选择“文件”和“另存为...”。
  2. 将“保存类型”值保留为默认的“XML 文件(*.xml)”
  3. 将“文件名”设置为你选择的名称。
  4. 选择“保存” 。

将死锁图保存为可在 SSMS 中以交互方式显示的 XDL 文件

查看死锁图的交互式表示形式有助于快速概览死锁中涉及的进程和资源以及快速识别死锁牺牲品。

若要将死锁图保存为可由 SSMS 以图形方式显示的文件,请执行以下操作:

  1. 从任意行选择 deadlock_xml 列中的值,以在 SSMS 的新窗口中打开死锁图的 XML。

  2. 依次选择“文件”和“另存为...”。

  3. 将“保存类型”设置为“所有文件”

  4. 文件名 设置为所选名称,扩展名设置为 .xdl

  5. 选择“保存” 。

    将死锁图形 XML 文件保存到具有 xsd 扩展名的文件的 SSMS 中的屏幕截图。

  6. 通过选择窗口顶部选项卡上的“X”或依次选择“文件”、“关闭”来关闭文件。

  7. 通过依次选择“文件”、“打开”、“文件”,在 SSMS 中重新打开该文件。 选择使用 .xdl 扩展名保存的文件。

    死锁图现在显示在 SSMS 中,其中包含死锁中涉及的进程和资源的可视表示形式。

    SSMS中打开的XDL文件的屏幕截图。死锁图形通过图示方式展示,其中进程用椭圆表示,锁定资源用矩形表示。

分析 Azure SQL 数据库的死锁

死锁图通常具有三个节点:

  • 受害者名单。 死锁牺牲品进程标识符。

  • Process-list。 死锁中涉及的全部进程的信息。 死锁图使用术语“进程”来表示运行事务的会话。

  • 资源列表。 死锁中涉及的资源的信息。

在分析死锁时,逐步查看这些节点很有用。

死锁牺牲品列表

死锁牺牲品列表显示已选为死锁牺牲品的进程。 在死锁图的可视表示形式中,进程由椭圆表示。 表示死锁牺牲品进程的椭圆上绘制了一个“X”。

直观显示死锁的屏幕截图。椭圆代表被选为牺牲品的进程,上面绘制了一个 X。

死锁图的 XML 视图中,victim-list 节点为已成为死锁牺牲品的进程提供了一个 ID。

在我们的示例死锁中,受害者进程 ID 为 process24756e75088。 在检查 process-list 和 resource-list 节点以了解有关牺牲品进程及其锁定或请求锁定的资源的详细信息时,我们可以使用此 ID。

死锁进程列表

死锁进程列表是有关死锁所涉及的事务的丰富信息来源。

死锁图的图形表示形式仅显示死锁图 XML 中包含的信息的子集。 死锁图中的椭圆表示进程,并显示包括以下内容的信息:

  • 会话 ID,也称为 SPID。

  • 会话的死锁优先级。 如果两个会话的死锁优先级不同,则会选择优先级较低的会话作为死锁牺牲品。 在此示例中,这两个会话具有相同的死锁优先级。

  • 会话使用的事务日志量(以字节为单位)。 如果两个会话的死锁优先级相同,则死锁监视器会将回滚的开销较低的会话选为死锁牺牲品。 该成本是通过比较各事务此时已写入的日志字节数来确定的。

    在我们的示例死锁中, session_id 89 使用了较少的事务日志,并被选为死锁受害者。

此外,将鼠标悬停在每个进程上,可以查看发生死锁前每个会话中运行的最后一个语句的输入缓冲区。 输入缓冲区显示在工具提示中。

在 SSMS 中直观显示死锁图的屏幕截图。两个椭圆表示进程。将显示一个进程的输入缓冲区。

还提供了有关死锁图的 XML 视图中的进程的其他信息,包括:

  • 会话的标识信息,例如客户端名称、主机名和登录名。

  • 发生死锁之前每个会话运行的最后一个语句的查询计划哈希。 查询计划哈希有助于从查询存储检索有关查询的详细信息。

在我们的示例死锁中:

  • 可以看到,这两个会话都是在 chrisqpublic 登录名下运行,并使用了 SSMS 客户端。

  • 在发生死锁之前,死锁牺牲品运行的最后一个语句的查询计划哈希是0x02b0f58d7730f798。 可以在输入缓冲区中看到此语句的文本。

  • 死锁中的另一个会话运行的最后一个语句的查询计划哈希也是0x02b0f58d7730f798。 可以在输入缓冲区中看到此语句的文本。 在这种情况下,这两个查询具有相同的查询计划哈希,因为除了用作相等谓词的文本值之外,这两个查询是相同的。

本文中稍后会使用这些值在查询存储中查找其他信息

死锁进程列表中的输入缓冲区的限制

对于死锁进程列表中的输入缓冲区信息,需要注意一些限制。

查询文本可能会在输入缓冲区中截断。 输入缓冲区限制为正在执行的语句的前 4000 个字符。

此外,死锁图中涉及的某些语句可能不包含在死锁图中。 在我们的示例中,会话 A 在单个事务中运行了两条 update 语句。 死锁图中仅包括第二条 update 语句(导致死锁的 update 语句)。 会话 A 运行的第一个更新语句通过阻止会话 B 在死锁中发挥了作用。死锁图中不包括会话 A 运行的第一个语句的输入缓冲区query_hash和相关信息。

若要识别死锁中涉及的多语句事务中完整运行的 Transact-SQL,可以在执行查询的存储过程或应用程序代码中找到相关信息,或当死锁发生时使用 扩展事件 运行追踪以捕获参与死锁事务的会话所运行的完整语句。 如果死锁中涉及的语句被截断,并且仅部分 Transact-SQL 显示在输入缓冲区中,你可以在查询存储中查找该语句的 Transact-SQL 及其执行计划

死锁资源列表

死锁资源列表显示死锁中的进程拥有和等待哪些锁资源。

资源由死锁的可视表示形式中的矩形表示:

在 SSMS 中直观显示的死锁图的屏幕截图。矩形显示死锁涉及的资源。

Nota

数据库名称在 Azure SQL 数据库中的数据库死锁图中表示为 GUID(uniqueidentifier)。 这是 sys.databasessys.dm_user_db_resource_governance 动态管理视图中列出的数据库的 physical_database_name

在此示例死锁中:

  • 死锁牺牲品(我们称之为会话 A):

    • SalesLT.Product 表的 PK_Product_ProductID 索引上的键拥有排他 (X) 锁。

    • SalesLT.ProductDescription 表的 PK_ProductDescription_ProductDescriptionID 索引上的键请求更新 (X) 锁。

  • 另一个进程(我们称之为会话 B):

    • SalesLT.ProductDescription 表的 PK_ProductDescription_ProductDescriptionID 索引上的键拥有更新 (X) 锁。

    • SalesLT.ProductDescription 表的 PK_ProductDescription_ProductDescriptionID 索引上的键请求共享 (S) 锁。

可以在 resource-list 节点内的死锁图的 XML 中看到相同的信息。

在查询存储中查找查询执行计划

检查死锁涉及的语句的查询执行计划通常非常有用。 通常,可以使用死锁图进程列表 XML 视图中的查询计划哈希在查询存储中找到这些执行计划。

此 Transact-SQL 查询查找与已为示例死锁找到的查询计划哈希匹配的查询计划。 连接到 Azure SQL 数据库中的用户数据库并运行以下查询。

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

你可能无法从查询存储中获取查询执行计划,具体取决于你的查询存储 CLEANUP_POLICY 或 QUERY_CAPTURE_MODE 设置。 在这种情况下,通常可以通过显示查询的估计执行计划来获取所需的信息。

查找增加阻塞的模式

检查死锁中涉及的查询执行计划时,请注意可能造成阻塞和死锁的模式。

  • 表或索引扫描。 在启用了 RCSI 的情况下运行修改数据的查询时,选择要更新的行是通过阻塞性扫描完成的。在此过程中,当读取数据值时,会在数据行上施加更新 (U) 锁。 如果数据行不符合更新条件,则会释放更新锁,并锁定并扫描下一行。

    通过优化索引来帮助修改查询更有效地查找行,可以减少发出的更新锁数。 这将减少阻塞和死锁的可能性。

  • 引用多个表的索引视图。 当你修改索引视图中引用的表时,数据库引擎还必须维护索引视图。 这需要释放更多的锁,并可能导致阻塞和死锁的增加。 索引视图也可能致使更新操作在“读取已提交的内容”隔离级别下内部执行。

  • 对外键约束中引用的列的修改。 修改约束中 FOREIGN KEY 引用的表中的列时,数据库引擎必须查找引用表中的相关行。 行版本不可用于这些读取。 如果启用了级联更新或删除,则隔离级别可能会在该语句的持续时间内升级为可序列化,以防止虚拟插入。

  • 锁提示。 查找其中指定的隔离级别需要更多锁的表提示。 这些提示包括 HOLDLOCK(相当于可序列化)、SERIALIZABLEREADCOMMITTEDLOCK(用于禁用 RCSI)和 REPEATABLEREAD。 此外,PAGLOCKTABLOCKUPDLOCKXLOCK 等提示可能会增加阻塞和死锁的风险。

    如果这些提示已到位,请研究实现这些提示的原因。 这些提示可能防止出现争用条件并确保数据有效性。 可以保留这些提示,并在必要时使用“ 防止死锁” 部分中的备用方法阻止将来的死锁。

    Nota

    请在事务锁定和行版本控制指南中详细了解使用行版本控制修改数据时的行为。

检查某个事务的完整代码时(无论是在执行计划在还是在应用程序查询代码中),请查找其他有问题的模式:

  • 事务中的用户交互。 显式多语句事务中的用户交互会显著增加事务的持续时间。 这使得这些事务更有可能重叠,并且阻塞和死锁更有可能发生。

    同样,持有打开的事务以及在事务中查询不相关的数据库或系统会显著增加阻塞和死锁的可能性。

  • 按不同顺序访问对象的事务。 当并发显式多语句事务遵循相同的模式并按相同顺序访问对象时,死锁不太可能发生。

防止死锁再次发生

有多种方法可用于防止死锁再次发生,例如索引优化、使用查询存储强制执行计划以及修改 Transact-SQL 查询。

  • 查看表的聚集索引。 大多数表都可从聚集索引中受益,但表通常是意外被实现为的。

    检查聚集索引的一种方法是使用 sp_helpindex 系统存储过程。 例如,我们可以通过执行以下语句来查看 SalesLT.Product 表的索引摘要:

    EXECUTE sp_helpindex 'SalesLT.Product';
    GO
    

    查看index_description列。 一个表只能包含一个聚集索引。 如果为表实现了聚集索引,则 index_description 包含单词 clustered

    如果不存在聚集索引,则该表为堆。 在这种情况下,请检查该表是否是有意创建为堆,以解决特定的性能问题。 考虑根据聚集索引设计指南实现聚集索引。

    在某些情况下,创建或优化聚集索引可以减少或消除死锁中的阻塞。 在其他情况下,可以使用其他技术,例如此列表中的其他技术。

  • 创建或修改非聚集索引。 优化非聚集索引可以帮助你的修改查询更快地查找要更新的数据,从而减少所需的更新锁数。

    在我们的示例死锁中,在查询存储中找到的查询执行计划包含针对 PK_Product_ProductID 索引的聚集索引扫描。 死锁图指示此索引上的共享 (S) 锁等待是死锁中的组成部分。

    查询执行计划的屏幕截图。正在对 Product 表上的 PK_Product_ProductID 索引执行聚集索引扫描。

    正在执行此索引扫描,因为我们的更新查询需要修改名为 vProductAndDescription 的索引视图。 如本文 “查找增加阻塞的模式” 部分中所述,引用多个表的索引视图可能会增加阻塞和死锁的可能性。

    如果我们在 AdventureWorksLT 数据库中创建以下非聚集索引来“涵盖”索引视图引用的 SalesLT.Product 中的列,那么这会有助于查询更高效地查找行:

    CREATE INDEX IX_Product_ProductID_Name_ProductModelID
        ON SalesLT.Product(ProductID, Name, ProductModelID);
    GO
    

    创建此索引后,死锁不会再次发生。

    当死锁涉及对外键约束中引用的列的修改时,请确保FOREIGN KEY的引用表上的索引支持高效查找相关行。

    虽然索引在某些情况下可以显着提高查询性能,但索引也有开销和管理成本。 查看常规索引设计指南,以帮助在创建索引(尤其是宽索引和大型表上的索引)之前评估索引的好处。

  • 评估索引视图的值。 防止再次发生示例死锁的另一个选项是删除 SalesLT.vProductAndDescription 索引视图。 如果未使用该索引视图,这将减少一段时间内维护索引视图的开销。

  • 使用快照隔离。 在某些情况下,为死锁中涉及的一个或多个事务将事务隔离级别设置为快照可能会防止阻塞和死锁再次发生。

    在数据库中禁用了“读取已提交的快照”时,对 SELECT语句使用此方法最有可能成功。 禁用了“读取已提交的快照”时,使用“读取已提交的内容”隔离级别的SELECT查询需要共享 (S) 锁。 在这些事务上使用快照隔离可消除对共享锁的需求,这可以防止阻塞和死锁。

    在启用了“读取提交的快照隔离”的数据库中,SELECT查询不需要共享 (S) 锁,因此在修改数据的事务之间更有可能发生死锁。 在多个修改数据的事务之间发生死锁的情况下,快照隔离可能导致更新冲突而不是死锁。 这同样需要其中一个事务重试其操作。

  • 使用查询存储强制执行某个计划。 你可能会发现死锁中的某个查询具有多个执行计划,且仅在使用特定计划时才会发生死锁。 可以通过在查询存储中强制执行某个计划来防止死锁再次发生。

  • 修改 Transact-SQL。 可能需要修改 Transact-SQL,才能防止死锁再次发生。 应谨慎执行修改 Transact-SQL,并对更改进行严格测试,以确保在并发运行这些修改时数据正确。 重新编写 Transact-SQL 时,请考虑:

    • 在事务中对语句进行排序,以便它们按相同的顺序访问对象。

    • 尽可能将事务分解为较小的事务。

    • 如有必要,请使用查询提示来优化性能。 可以在不更改应用程序代码的情况下使用查询存储来应用提示。

死锁指南中找到最大程度地减少死锁的其他方法。

Nota

如果其中一个会话必须不重试就成功完成,或者当死锁涉及的某一查询无关紧要、且应始终选为牺牲品时,在某些情况下,你可以为一个或多个涉及死锁的会话调整死锁优先级。 虽然这不会阻止死锁重新出现,但它可能会减少未来死锁的影响。

删除 XEvents 会话

你可以让一个收集死锁信息的 XEvents 会话在关键数据库上长期运行。 如果你使用事件文件目标,这可能会导致产生大型文件(如果发生多个死锁)。 你可以从 Azure 存储中删除活动跟踪的 blob 文件(当前正在写入的文件除外)。

当你希望删除 XEvents 会话时,无论选择的目标类型如何,用于删除该会话的 Transact-SQL 都是相同的。

若要删除 XEvents 会话,请运行以下 Transact-SQL。 在运行代码之前,请把会话的名称替换为相应的值。

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

使用 Azure 存储资源管理器

Azure 存储资源管理器是一个独立的应用程序,用于简化对 Azure 存储内的 blob 中存储的事件文件目标的处理。 可以使用存储资源管理器实现以下目的:

下载 Azure 存储资源管理器