这是我参与8月更文挑战的第23天，活动详情查看：8月更文挑战

本部分主要参考翻译自How to resolve deadlocks in SQL Server。在了解死锁问题的时候看到的这篇文章。

对于死锁，SQL Server等数据库管理系统都会有自己的处理机制，但是在实际中，死锁被系统解决后，如何确认死锁问题是怎么发生的，以及处理后续（不再发生死锁），才是应该关注的。

原文中，看到可以从 system_health session 中获取曾经发生的死锁，这样就便于分析发生的问题。因此特地进行翻译和记录下。中间包含自己的小说明。

介绍

在本文中，我们将讨论 SQL Server 中的死锁，然后我们将分析一个真实的死锁场景并探索故障排除步骤。

一般来说，我们可以在网上找到关于死锁问题的各种理论建议和例子，但在本文中，我们将处理一个真实的死锁故事，并且有机会处理基于问题的案例，学习解决步骤。

首先，让我们解释一下死锁的概念。当两个（或两个以上）操作互相想要访问被另一个操作已经锁定的资源时，就会出现死锁问题。在这种情况下，数据库资源会受到负面影响，因为两个进程都在不断地等待对方。此争用（contention）问题由 SQL Server 干预终止。它从参与死锁的事务中选择一个受害者，强制它回滚所有操作。

从这个解释可以看出，SQL Server 中的死锁是一个特殊的争用问题；另外，每个死锁都有其独特的特点，因此解决方案根据问题的特点有不同的方法。

现在，让我们看一下问题场景。

问题场景

在这个真实场景中，内部应用程序向用户返回一个错误，用户将这个错误通知开发团队。

开发团队意识到这是一个死锁问题，但他们找不到问题的主要原因。在这种情况下，团队决定接受经验丰富的数据库管理员的咨询服务。在接下来的部分中，我们将学习数据库管理员如何分析和解决这个死锁问题。

先决条件

开发团队使用下表来存储订单号（order numbers），并使用以下查询来创建当天的第一行。

CREATE TABLE [TestTblCounter](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [SerialNumber] [int] NULL,
  [LogDate] [datetime] NULL)
  GO
     IF NOT EXISTS(SELECT Id
                   FROM TestTblCounter 
                   WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112))
         BEGIN
             INSERT INTO TestTblCounter
             VALUES
             ('1', 
              CONVERT(VARCHAR(100), GETDATE(), 112)
             )
     END

下表的数据结构与下图类似；

正在使用以下存储过程来创建一个新的订单号。

CREATE PROCEDURE CreateLogNo
AS
     DECLARE @LogNo AS VARCHAR(50), @LogCounter AS INT= 0;
     BEGIN TRAN;
 
     UPDATE TestTblCounter
       SET 
           SerialNumber = SerialNumber + 1
     WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
     SELECT @LogCounter = SerialNumber
     FROM TestTblCounter WITH(TABLOCKX)
     WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
     SELECT @LogCounter AS LogNumber;
     COMMIT TRAN

我们还将使用 SQLQueryStress 工具来生成类似生产系统的工作负载。

使用 system_health 会话监控 SQL Server 中的死锁

由于错误消息，数据库管理员决定研究死锁问题。错误消息显然表明存在死锁问题。

作为第一步，他决定检查用于死锁的 system_health 会话（system_health session）。

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

system_health 是 SQL Server 的默认扩展事件会话，它会在数据库引擎启动时自动启动。

system_health会话收集各种系统数据，其中之一是死锁信息。以下查询读取 system_health session 的 .xel 文件并提供有关发生的死锁问题的信息。

system_health会话可以是找出死锁问题的一个很好的起点。下面的查询有助于找出 system_health 会话捕获的死锁问题。

DECLARE @xelfilepath NVARCHAR(260)
SELECT @xelfilepath = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'
 DROP TABLE IF EXISTS  #TempTable
 SELECT CONVERT(XML, event_data) AS EventData
        INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)
         WHERE object_name = 'xml_deadlock_report'
SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime, 
            CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, 
      EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime, 
            EventData.query('event/data/value/deadlock') AS XmlDeadlockReport
     FROM #TempTable
     ORDER BY UtcTime DESC;

注：死锁信息在事务重启后就会被清除，也就是，如果发生重启，之前的死锁信息将不会被查到

当我们点击 XmlDeadlockReport 列的任意一行时，就会出现死锁报告。

使用扩展事件监视 SQL Server 中的死锁

数据库管理员通过system_health session捕获的数据发现了一些死锁问题的线索。但是，他认为system_health session因为文件大小限制，只显示了最近的事件，所以检测SQL Server中的所有死锁是不可靠的。因此，他决定创建一个可以捕获所有死锁的新扩展事件会话。

Extended Event 是一个系统监控工具，有助于从 SQL Server 收集事件和系统信息。借助 XEvent，我们还可以从 SQL Server 捕获死锁信息。

首先，我们将启动 SQL Server Management Studio 并导航到位于 Management（管理） 文件夹下Extended Events（扩展事件）的 Session（会话）。右键单击“会话”文件夹并选择“新建会话”。

在“新建会话”截图中，为会话创建一个名称，并选中“会话创建后立即启动事件会话”（Start the event session immediately after the session creation）复选框；因此，会话将在创建过程完成后启动。

在“事件”（Events）选项卡上，我们选择要捕获的事件。对于本次会话，我们将选择以下事件：

database_xml_deadlock_report
xml_deadlock_report
xml_deadlock_report_filtered

我们将单击 “配置”（Configure） 按钮并选择会与事件一起捕获的全局事件：

客户端应用名称 - client app name
客户端连接ID - client connection id
客户端主机名 - client hostname
数据库id - database id
数据库名称 - database name
nt用户名 - nt username
用户名 - username
sql文本 - sql text

在 “数据存储”（Data Storage） 选项卡上，我们将选择 event_file 类型来存储捕获的数据，然后单击 OK 按钮。

会话将被创建，然后自动启动以捕获死锁事件。

分析和解决 SQL Server 中的死锁

在本节中，我们将首先模拟死锁问题，然后尝试找出导致死锁问题的主要原因。

使用以下参数启动 SQLQueryStress 并等待查询执行过程完成。

当我们打开异常的详细信息时，它会显示异常消息。

要了解有关死锁问题的更多详细信息，我们需要查看之前为捕获死锁事件而创建的扩展事件会话。

我们展开 MonitorDeadlock 会话并右键单击目标节点，然后选择查看目标数据（View Target Data）。捕获的死锁将显示在右侧窗格中。

xml_deadlllock_report 事件包含更多有关死锁的详细信息，我们还可以找到死锁图。

当我们解释死锁图时，SPID 65（受害者）已经获得了一个意图排他锁，并希望将更新锁放置到 TestTblCounter 表中。 SPID 64 已获取到 TestTblCounter 的排他锁，并且由于 TABLOCKX 提示想要将排他锁放置到同一个表。

TABLOCKX 提示有助于对表放置排它锁，直到选择语句完成或事务完成。 TABLOCKX 提示的缺点是降低了并发性，因此增加了锁定时间。当我们决定使用它时，我们需要考虑锁定和争用问题。尤其对于这种场景，这种提示使用逻辑是不合适的。当我们重新考虑查询时，update 语句修改了一些行，然后 select 语句获取了相同的修改行，但是由于 TABLOCKX 提示，它对整个表放置了一个排他锁，直到它完成。此查询最无意义的部分是将值分配给变量的那一行，因为对变量的数据分配是随机执行的。

TABLOCKX hint 表示向表放置一个排它锁！

如果要获取最后更新或插入的行，为什么需要对表的所有行设置排他锁？因此，我们可以删除导致查询死锁的 TABLOCKX 提示（TABLOCKX hint）。同时，可以使用 OUTPUT 子句获得最后插入或更新的行值。

ALTER PROCEDURE [dbo].[CreateLogNo]
AS
     DECLARE @LogNo AS VARCHAR(50), @LogCounter AS INT= 0;
    DECLARE @UptTable AS TABLE(SerNumber  VARCHAR(50));
     BEGIN TRAN;
 
   
IF NOT EXISTS(SELECT ID
              FROM TestTblCounter
              WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112))
    BEGIN
        INSERT INTO TestTblCounter
     OUTPUT INSERTED.SerialNumber  INTO @UptTable
        VALUES
        (1, 
         CONVERT(VARCHAR(100), GETDATE(), 112)
        );
 
END;
ELSE
BEGIN
 
     UPDATE TestTblCounter
       SET 
           SerialNumber = SerialNumber + 1
        OUTPUT INSERTED.SerialNumber  INTO @UptTable
     WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
 
   END
     SELECT @LogCounter = SerNumber FROM @UptTable ORDER BY SerNumber DESC
     SELECT @LogCounter AS LogNumber;
     COMMIT TRAN

当我们借助 SQLQueryStress 为更改后的存储过程，使用200个并发用户模拟新的工作负载时，将不会遇到任何死锁问题。

总结

在本文中，我们解释了SQL Server中的死锁，然后分析了一个开发团队所经历的真实故事。生成解决方案的重要一点是正确理解和解释死锁报告和图。否则，将很难找到问题的主要原因。

回顾解决步骤：

检查用于死锁的 system_health 会话
创建扩展事件会话以捕获死锁
分析死锁报告和图表以找出问题
是否可以改进或更改死锁中涉及的查询

优秀！| 数据库教程23：SQL Server中如何正确的处理确认死锁问题

介绍

问题场景

先决条件

使用 system_health 会话监控 SQL Server 中的死锁

使用扩展事件监视 SQL Server 中的死锁

分析和解决 SQL Server 中的死锁

总结