SQL Server死锁排查的实战指南

IT 文章6分钟前更新小编

0 0 0

本文主要讲解关于SQL Server死锁排查的实战指南相关内容，由优网导航（www.uonce.com）提供，欢迎关注收藏本站！

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

程序员导航

优网导航旗下整合全网优质开发资源，一站式IT编程学习与工具大全网站

立即访问

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;

结果集说明

列名	数据类型	说明
DeadlockDateTime	datetime	死锁事件发生的准确日期和时间。
DeadlockGraph	xml	死锁图，以 XML 格式描述死锁的完整信息。这是分析死锁的最关键信息。

如何分析死锁图 (DeadlockGraph)

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

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

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

AI 工具导航

优网导航旗下AI工具导航，精选全球千款优质 AI 工具集

立即访问

查询脚本

SELECT
    s.session_id,
    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 database_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**

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

字段	来源	说明
session_id	s	会话 ID (SPID)。
login_name / host_name / program_name	s	帮助定位问题来源：哪个用户、从哪台机器、通过什么程序连接的。
status	s	会话状态（如 running, sleeping）。
command	r	当前正在执行的命令类型（如 SELECT, UPDATE, INSERT）。
wait_type / wait_time	r	如果会话被阻塞，这里会显示它正在等待的资源类型（如 LCK_M_X）和已等待时间（毫秒）。NULL 表示未被阻塞。
wait_resource	r	会话正在等待的具体资源（如 KEY: 5:72057594048872448 (xxxxxxxx)）。
blocking_session_id	r	至关重要！表示是哪个 SPID 阻塞了当前会话。如果 > 0，则说明此会话正被另一个会话阻塞。这是排查阻塞链的核心字段。
last_request_start_time / last_request_end_time	s	会话最后一次请求的开始和结束时间。

总结与排查步骤建议

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

到此这篇关于SQL Server死锁排查的实战指南的文章就介绍到这了,更多相关SQL Server死锁排查内容请搜索优网导航以前的文章或继续浏览下面的相关文章希望大家以后多多支持优网导航！

您可能感兴趣的文章:

SQLServer中排查死锁及死锁问题解决
SQL Server死锁问题的排查和解决方法
sql server排查死锁优化性能
sql server中死锁排查的全过程分享

相关推荐: Flutter学习笔记(三)RowColum布局

本文主要讲解关于Flutter学习笔记(三)RowColum布局相关内容，由优网导航（www.uonce.com）提供，欢迎关注收藏本站！主题本文将介绍，flutter中的row，colum的用法。通俗来说，就是横向布局和纵向布局的用法。开发环境 win…
免费在线工具导航
优网导航旗下整合全网优质免费、免注册的在线工具导航大全
立即访问