SQL Server死锁排查的实战指南

目录

• 1. 查询 system_health 会话中的历史死锁信息
• 查询脚本
• 结果集说明
• 如何分析死锁图 (DeadlockGraph)
• 2. 查询特定会话 (SPID) 的详细信息
• 查询脚本
• 关键字段说明 (用于死锁/阻塞分析)
• 总结与排查步骤建议

本文档介绍了两项关键的 T-SQL 查询，用于排查 Microsoft SQL Server 中的死锁问题。

1. 查询 system_health 会话中的历史死锁信息

system_health 是 SQL Server 默认启用的扩展事件 (Extended Events) 会话。它会自动捕获包括死锁 (XML_deadlock_report) 在内的多种系统事件，是排查近期发生的死锁的首选之地。

查询脚本

SELECT
    XEventData.XEvent.value('(@timestamp)[1]', 'datetime') AS DeadlockDateTime,
    XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadlockGraph
FROM
    (SELECT CAST(target_data AS XML) AS TargetData
     FROM sys.dm_xe_session_targets st
              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)
ORDER BY
    DeadlockDateTime DESC;

结果集说明

如何分析死锁图 (DeadlockGraph)

1. 在 SSMS (SQL Server Management Studio) 中执行上述查询。
2. 点击结果集中 DeadlockGraph 列的 XML 链接。
3. SSMS 会在一个新的窗口中以图形化的方式展示死锁关系图，非常直观。
   • 椭圆：表示参与死锁的进程（SPID）。
   • 矩形：表示被争抢的资源（如键、页、表、行）。
   • 箭头：表示进程对资源的请求和等待关系。
4. 在图形化界面中，可以清楚地看到：
   • 哪些两个（或多个）会话被卷入死锁。
   • 它们各自持有(owner)什么资源，又在等待(waiter)什么资源。
   • 它们当时正在执行的 T-SQL 语句（inputbuf）。

2. 查询特定会话 (SPID) 的详细信息

当从死锁图或js其它途径（如监控、错误日志）获知一个具体的会话 ID (SPID) 后，可以使用以下查询http://www.devze.com来获取该会话的详细实时状态和资源使用情况。

查询脚本

SELECT
    s.session_id,
  python  s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.reads,
    s.writes,
    s.logical_reads,
    s.cpu_time,
    s.memory_usage,
    DB_NAME(s.database_id) AS daphptabase_name,
    r.status AS request_status,
    r.command,
    r.start_time,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id, -- **关键：阻塞此会话的SPID**
    r.cpu_time AS request_cpu_time,
    r.total_elapsed_time,
    r.reads AS request_reads,
    r.writes AS request_writes,
    r.logical_reads AS request_logical_reads
FROM sys.dm_exec_sessions s
         LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id = 219; -- **替换为你需要关注的 SPID**

关键字段说明 (用于死锁/阻塞分析)

总结与排查步骤建议

1. 发现死锁：通过应用程序错误日志、SQL Server 错误日志或监控工具发现死锁错误（错误号 1205）。
2. 获取死锁图：使用第一个查询从 system_health 会话中提取最近的死锁图。
3. 分析死锁图：在 SSMS 中图形化查看死锁图，确定涉及的主要会话 (SPID) 和争抢的资源。
4. 调查会话详情：将图形中发现的 SPID 代入第二个查询，了解这些会话的详细信息（谁发起的、从哪里来、在做什么操作），特别是 blocking_session_id 字段可以帮助确认阻塞关系。
5. 解决问题：根据分析结果，通常的解决方案包括：
   • 优化查询/索引：确保事务尽可能短小，为经常查询的字段添加索引，避免表扫描。
   • 调整事务隔离级别：在必要时使用更低的隔离级别（如 READ COMMITTED）。
   • 调整访问顺序：在应用层代码中，确保对不同资源的访问顺序在所有事务中都保持一致。
   • 使用提示：在极少数情况下，考编程客栈虑使用锁提示（如 NOLOCK, UPDLOCK, ROWLOCK），但需谨慎评估其副作用。

到此这篇关于SQL Server死锁排查的实战指南的文章就介绍到这了,更多相关SQL Server死锁排查内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！