SQL Server 使用扩展事件跟踪 Deadlock 死锁问题

小渣渣

需求：SQL Server 数据库造成死锁的原因有很多，在生产业务中，大家都可能会遇到死锁问题，单凭应用程序死锁的异常信息，不一定能够定位出具体原因，所以，需要在 SQL Server 使用扩展事件跟踪 Deadlock 死锁问题，记录死锁发生时的具体详情。

扩展事件概述

扩展事件是一种轻型性能监视功能，它允许用户收集监视和解决问题所需的数据。 借助此功能，可以出于性能监视和解决问题的相关目的，查看数据引擎内部运行的详细信息。

使用扩展事件 (XEvents) 体系结构，用户可根据监视、识别或故障排除 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例的性能所需的尽可能多或尽可能少的数据。扩展事件可进行高度可配置、体量轻，并可以很好地缩放。

参考：
https://learn.microsoft.com/zh-cn/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/extended-events/extended-events?view=sql-server-ver16


死锁的原因

死锁是由数据库中的竞争的并发锁引起的，通常是在多步骤事务中。每个用户会话可能有一个或多个代表它运行的任务，其中每个任务可能获取或等待获取各种资源。以下类型的资源可能会造成阻塞，并最终导致死锁。

• 锁：等待获取资源（如对象、页、行、元数据和应用程序）的锁可能导致死锁。 例如，事务 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 下交叉执行多个活动请求。
  

参考：
https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver16

死锁扩展事件记录

创建扩展事件，来记录死锁信息，命令如下：

登录可见。

开启死锁事件会话

登录可见。

停止事件会话

登录可见。

删除事件会话

登录可见。

查询事件会话数据

登录可见。

测试死锁

新建 Tab1 表来进行测试，并且新建两个执行窗口，分别执行如下命令：

登录可见。

发生死锁，如下：

事务(进程 ID 68)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。
Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

查看事件会话数据，如下图：



详细 XML 数据如下：

登录可见。

（完）

小渣渣

扩展事件文件默认路径：

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\deadlock_monitor_0_133538477770860000.xel

小渣渣

新建 tab1 表测试 sql 脚本

登录可见。