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

简短描述

当两个或更多会话（每个会话的资源均处于锁定状态）尝试访问另一个会话的锁定资源时，您的数据库实例会遇到死锁。发生死锁时，在其中一个会话解除锁定之前，任何会话都无法继续运行。

为了解决死锁，SQL Server 死锁检测器使用基于资源或基于成本的机制来结束其中一个会话。然后，检测器回滚与该会话相关的所有修改。默认情况下，SQL Server 数据库引擎会选择运行成本最低的事务的会话作为死锁牺牲品进行回滚。其中一个会话结束后，会话持有的锁将被释放，另一个会话可以继续。有关详细信息，请参阅 Microsoft 网站上的死锁。

解决方法

要捕获有关数据库实例上死锁事件的信息，您可以使用跟踪标志、system_health 会话或 xml_deadlock_report 扩展事件会话。

激活跟踪标志

您可以激活死锁跟踪标志 (1204,1222)。您可以使用跟踪标志自定义 SQL Server 行为，以捕获 SQL Server 错误日志中的死锁信息。

**注意：**最佳做法是避免将这些跟踪标志用于出现死锁的高性能工作负载实例，因为这可能会导致性能问题。请创建扩展事件会话来捕获死锁事件信息。

• 跟踪标志 1204 提供有关死锁中涉及的每个节点的死锁信息。
• 与 XML 格式的跟踪标志 1204 相比，跟踪标志 1222 提供了更详细的死锁信息。

有关详细信息，请参阅 Microsoft 网站上的跟踪标志 1204 和跟踪标志 1222。

激活这两个跟踪标志，通过 RDS for SQL Server 中的自定义参数组为每个死锁事件获取两种不同的表示形式。要进行此设置，请参阅我的 Amazon RDS for SQL Server 数据库实例发生死锁事件时如何收到通知？激活跟踪标志后，您可以查看 SQL Server 错误日志，以了解有关死锁事件的详细信息。

使用 system_health 会话

您可以将扩展事件用于 Amazon RDS for SQL Server 来收集数据，以监控和解决 SQL Server 问题。system_health 扩展事件会话包含在 SQL Server 中，默认情况下处于激活状态。当您的 SQL Server 数据库引擎启动并收集基本服务器运行状况信息时，会话会自动启动。您可以使用此信息来解决性能问题并监控数据库引擎中的死锁。有关详细信息，请参阅 Microsoft 网站上的使用 system_health 会话。

system_health 扩展事件会话使用两个目标（即事件文件和环形缓冲区）来存储数据。环形缓冲区以先进先出 (FIFO) 的方式存储数据。在 Amazon RDS for SQL Server 中，环形缓冲区的目标内存不能超过 4 MB。因此，在繁忙的实例上，system_health 会话可能会轮换事件。

默认情况下，每个文件的大小为 5 MB，最大翻转文件数为 4。这些加起来总共有 20 MB 的 system_health 扩展事件数据。对于 SQL Server 2016、2017 和 2019，单个文件的大小为 100 MB，最大文件数增加到 10 个。这些加起来总共有 1 GB 的数据。

使用 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. 在 Filter（筛选条件）窗口中，执行以下操作：
   对于 Field（字段），选择 name（名称）。
   对于 Operator（运算符），选择 Contains（包含）。
   对于 Value（值），选择 deadlock（死锁）。
8. 选择 OK（确定）。您可以使用死锁查看事件。
9. 选择要查看的事件，然后选择 Deadlock（死锁）选项卡以查看图表。

要使用 T-SQL 检索死锁信息，请完成以下步骤：

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

   SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraphFROM (
       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 文件以查看死锁图。

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

使用 xml_deadlock_report 扩展事件会话

要创建扩展事件会话，请选择 xml_deadlock_report 事件来捕获死锁。要将事件保存到文件中进行分析，请选择一个事件文件作为目标。要创建扩展事件会话，可以使用 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. 选择 Sessions（会话）。
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 网站上）

KB4541132 - 改进： 在 SQL Server 2016、2017 和 2019 中，默认 xEvent 跟踪 system_health 的大小和留存策略有所增加（在 Microsoft 网站上）

监控 Amazon RDS for SQL Server 中的死锁并使用 Amazon CloudWatch 设置通知