如何在运行 SQL Server 的 Amazon RDS 数据库实例上捕获死锁信息？

解决方法

当两个或更多包含锁定资源的会话尝试访问另一个会话的锁定资源时，会发生死锁。这会产生“循环链”。此时所有会话停止运行，直到：

• 其中一个会话解锁。
• 这样，另一会话就可以访问锁定的资源。

SQL Server 的死锁探测器通过使用基于资源或基于成本的机制结束其中一个会话来解决这种情况。然后，探测器将回滚与此会话相关的所有修改。在其中一个会话结束后，该会话保持的锁定将被释放，另一个会话可以继续。有关更多信息，请参阅 Microsoft 关于死锁的文档。

您可以使用以下方法之一捕获数据库实例上的死锁事件信息。

激活跟踪标记

您可以激活死锁跟踪标记（1204、1222）。跟踪标记用于自定义 SQL Server 行为，例如通过在 SQL Server 错误日志中捕获死锁信息来进行额外的监控。

• 跟踪标记 1204 提供有关死锁所涉及的每个节点的死锁信息。
• 跟踪标记 1222 以 XML 格式提供比跟踪标记 1204 更详细的死锁信息。

您可以激活这两种跟踪标记，为每个死锁事件接收两种不同表达形式的通知。要进行设置，请参阅如何在 Amazon RDS SQL Server 数据库实例上发生死锁事件时接收通知？ 激活跟踪标记后，您可以查看 SQL Server 错误日志以了解有关死锁事件的详细信息。

使用 system_health 会话

扩展事件是一种轻量级性能监控系统，可帮助您收集数据以监控和排查 SQL Server 中的问题。system_health 扩展事件会话包含在 SQL Server 中，已默认激活。当 SQL Server 数据库引擎启动并收集基本服务器运行状况信息时，该会话会自动启动。您可以使用此信息对性能问题进行排查并监控数据库引擎中的死锁。

system_health 扩展事件会话使用两个目标来存储数据，分别为事件文件和环形缓冲区。

默认情况下，每个单独文件的大小为 5 MB，最大翻转文件数为 4 个。这意味着 system_health 扩展事件数据合计最多为 20 MB 。对于 SQL Server 2016、2017 和 2019，单个文件的大小为 100 MB，最大文件数增加到 10 个。这意味着数据合计最多为 1 GB。

环形缓冲区是内存中的一种特殊数据结构，以先进先出（FIFO）的方式存储数据。在适用于 SQL Server 的 Amazon RDS 中，环形缓冲区的目标内存不能超过 4 MB。因此，在繁忙的实例上，system_health 会话可能会轮换事件。

使用 system_health 会话可以使用 Microsoft SQL Server Management Studio (SSMS) 或 Transact-SQL (T-SQL) 检索有关数据库实例死锁的信息。

执行以下操作以使用 SSMS 检索死锁信息：

1. 打开 SSMS。
2. 在 Object Explorer（对象资源管理器）中，选择 Management（管理），然后选择 Extended Events（扩展事件）。
3. 选择 Sessions（会话）。
4. 找到 system_health 会话，然后选择（双击）package0.event_file 以打开扩展事件文件。
5. 加载文件内容后，在 SSMS 菜单上，选择 Extended Events（扩展事件）。
6. 选择 Filters（筛选条件）。
7. 在 Filters（筛选条件）窗口中，执行以下操作：
   对于 Field（字段），选择 name（名称）。
   对于 Operator（运算符），选择 Contains（包含）。
   对于 Value（值），选择 deadlock（死锁）。
8. 选择 OK（确定）。您可以查看发生死锁的事件。
9. 选择要查看的事件，然后选择 Deadlock（死锁）选项卡以查看图表。

-或者-

执行以下操作以使用 T-SQL 检索死锁信息：

1.    运行类似于以下内容的查询来查看死锁列表：

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT XEvent.query('.') AS XEvent
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s 
        ON s.address = st.event_session_address
        WHERE s.NAME = 'system_health'
        AND st.target_name = 'ring_buffer'
        ) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

2.    选择死锁 XML 输出以在新窗口中打开 XML 文件。

3.    使用 .xdl 文件扩展名保存 XML。这样做会将 XML 转换为图形格式。

4.    导航到文件位置，然后在 SSMS 中打开 .xdl 文件以查看死锁图表。

您可以运行 T-SQL 查询通过 ring_buffer 检索死锁信息。ring_buffer 目标将事件数据保存在内存中。只有在实例未重新启动的情况下才获取此类信息。重新启动时，此信息将被清除。

使用 xml_deadlock_report 扩展事件会话

您可以通过选择 xml_deadlock_report 事件来创建扩展事件会话，用于捕获死锁。选择事件文件作为目标会将事件保存到文件。日后可以对这个文件进行分析。可以使用 SSMS 或 T-SQL 创建扩展事件会话。创建会话后，您可以在数据库实例上检索有关死锁的信息。你可以使用 SSMS 或 T-SQL 来完成此操作。

执行以下操作以使用 SSMS 创建扩展事件会话：

1. 打开 SSMS。
2. 在 Object Explorer 中，选择 Management（管理），然后选择 Extended Events（扩展事件）。
3. 选择（右键单击）Sessions（会话），然后选择 New Session Wizard（新建会话向导）。
4. 对于 Session name（会话名称），输入会话的名称，然后选择“Next”（下一步）。
5. 在 Choose Template（选择模板）页面上，选择 Do not use a template（不使用模板）。
6. 选择 Next（下一步）打开 New Session Wizard（新建会话向导）页面。
7. 从 Event library（事件库）中，选择 xml_deadlock_report，然后选择 Next（下一步）。
8. 在 Capture Global Fields（捕获全局字段）页面中，选择所有事件通用的值。
   **注意：**选择 sql_text 字段可查看导致死锁的查询。
9. 选择 Next（下一步）。
10. 在 Set Session Event Filters（设置会话事件筛选条件）页面中，创建事件筛选条件来限制要捕获的数据。
11. 选择 Next（下一步）。
12. 在 Specify Session Data Storage（指定会话数据存储）页面中，选择 Save data to a file for later analysis（保存数据到文件以供日后分析）和 Work with only the most recent data（仅处理最新数据）。
13. 选择 Finish（完成）。

现在，您可以在 SSMS 的“Sessions”（会话）文件夹中看到新会话。选择（右键单击）会话，然后选择 Start session（启动会话）。

-或者-

运行类似于以下内容的查询以使用 T-SQL 创建扩展事件会话：

CREATE EVENT SESSION [Deadlock_detection] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\Deadlock',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO     
-- Start the event session  
ALTER EVENT SESSION Deadlock_detection ON SERVER  
STATE = start;  
GO

执行以下操作以使用 SSMS 检索死锁信息：

1. 打开 SSMS。
2. 在 Object Explorer 中，选择 Management（管理），然后选择 Extended Events（扩展事件）。
3. 选择 会essions（会话）。
4. 找到之前创建的扩展事件会话，然后选择（双击）package0.event_file 以打开扩展事件文件。
5. 在加载文件内容后，选择要查看的事件，然后选择 Deadlock（死锁）选项卡以查看图表。

-或者-

在 T-SQL 中运行类似于以下内容的查询来查看死锁列表：

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\deadlock*.xel', null, null, null)

---

相关信息

Microsoft 关于死锁信息工具的文档