SQL Server死锁排查的实战指南
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. 查询 system_health 会话中的历史死锁信息</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">查询脚本</a></li><li><a href="#_lab2_0_1">结果集说明</a></li><li><a href="#_lab2_0_2">如何分析死锁图 (DeadlockGraph)</a></li></ul><li><a href="#_label1">2. 查询特定会话 (SPID) 的详细信息</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">查询脚本</a></li><li><a href="#_lab2_1_4">关键字段说明 (用于死锁/阻塞分析)</a></li></ul><li><a href="#_label2">总结与排查步骤建议</a></li><ul class="second_class_ul"></ul></ul></div><p>本文档介绍了两项关键的 T-SQL 查询，用于排查 Microsoft SQL Server 中的死锁问题。</p><p class="maodian"><a name="_label0"></a></p><h2>1. 查询 system_health 会话中的历史死锁信息</h2>
<p><code>system_health</code> 是 SQL Server 默认启用的扩展事件 (Extended Events) 会话。它会自动捕获包括死锁 (<code>xml_deadlock_report</code>) 在内的多种系统事件，是排查近期发生的死锁的首选之地。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><p class="maodian"><a name="_lab2_1_3"></a></p><h3>查询脚本</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
XEventData.XEvent.value('(@timestamp)', 'datetime') AS DeadlockDateTime,
XEventData.XEvent.query('(data/value/deadlock)') 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;
</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>结果集说明</h3>
<table><thead><tr><th>列名</th><th>数据类型</th><th>说明</th></tr></thead><tbody><tr><td>DeadlockDateTime</td><td>datetime</td><td>死锁事件发生的准确日期和时间。</td></tr><tr><td>DeadlockGraph</td><td>xml</td><td><strong>死锁图</strong>，以 XML 格式描述死锁的完整信息。这是分析死锁的<strong>最关键信息</strong>。</td></tr></tbody></table>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>如何分析死锁图 (DeadlockGraph)</h3>
<ol><li>在 SSMS (SQL Server Management Studio) 中执行上述查询。</li><li>点击结果集中 <code>DeadlockGraph</code> 列的 XML 链接。</li><li>SSMS 会在一个新的窗口中以图形化的方式展示死锁关系图，非常直观。<ul><li><strong>椭圆</strong>：表示参与死锁的进程（SPID）。</li><li><strong>矩形</strong>：表示被争抢的资源（如键、页、表、行）。</li><li><strong>箭头</strong>：表示进程对资源的请求和等待关系。</li></ul></li><li>在图形化界面中，可以清楚地看到：<ul><li>哪些两个（或多个）会话被卷入死锁。</li><li>它们各自持有(<code>owner</code>)什么资源，又在等待(<code>waiter</code>)什么资源。</li><li>它们当时正在执行的 T-SQL 语句（<code>inputbuf</code>）。</li></ul></li></ol>
<p class="maodian"><a name="_label1"></a></p><h2>2. 查询特定会话 (SPID) 的详细信息</h2>
<p>当从死锁图或其它途径（如监控、错误日志）获知一个具体的会话 ID (<code>SPID</code>) 后，可以使用以下查询来获取该会话的详细实时状态和资源使用情况。</p>
<h3>查询脚本</h3>
<div class="jb51code"><pre class="brush:sql;">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**
</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>关键字段说明 (用于死锁/阻塞分析)</h3>
<table><thead><tr><th>字段</th><th>来源</th><th>说明</th></tr></thead><tbody><tr><td>session_id</td><td>s</td><td>会话 ID (SPID)。</td></tr><tr><td>login_name / host_name / program_name</td><td>s</td><td>帮助定位问题来源：哪个用户、从哪台机器、通过什么程序连接的。</td></tr><tr><td>status</td><td>s</td><td>会话状态（如 running, sleeping）。</td></tr><tr><td>command</td><td>r</td><td>当前正在执行的命令类型（如 SELECT, UPDATE, INSERT）。</td></tr><tr><td>wait_type / wait_time</td><td>r</td><td><strong>如果会话被阻塞，这里会显示它正在等待的资源类型（如 LCK_M_X）和已等待时间（毫秒）。NULL 表示未被阻塞。</strong></td></tr><tr><td>wait_resource</td><td>r</td><td>会话正在等待的具体资源（如 KEY: 5:72057594048872448 (xxxxxxxx)）。</td></tr><tr><td><strong>blocking_session_id</strong></td><td><strong>r</strong></td><td><strong>至关重要！表示是哪个 SPID 阻塞了当前会话。如果 > 0，则说明此会话正被另一个会话阻塞。这是排查阻塞链的核心字段。</strong></td></tr><tr><td>last_request_start_time / last_request_end_time</td><td>s</td><td>会话最后一次请求的开始和结束时间。</td></tr></tbody></table>
<p class="maodian"><a name="_label2"></a></p><h2>总结与排查步骤建议</h2>
<ol><li><strong>发现死锁</strong>：通过应用程序错误日志、SQL Server 错误日志或监控工具发现死锁错误（错误号 1205）。</li><li><strong>获取死锁图</strong>：使用<strong>第一个查询</strong>从 <code>system_health</code> 会话中提取最近的死锁图。</li><li><strong>分析死锁图</strong>：在 SSMS 中图形化查看死锁图，确定涉及的主要会话 (<code>SPID</code>) 和争抢的资源。</li><li><strong>调查会话详情</strong>：将图形中发现的 <code>SPID</code> 代入<strong>第二个查询</strong>，了解这些会话的详细信息（谁发起的、从哪里来、在做什么操作），特别是 <code>blocking_session_id</code> 字段可以帮助确认阻塞关系。</li><li><strong>解决问题</strong>：根据分析结果，通常的解决方案包括：<ul><li><strong>优化查询/索引</strong>：确保事务尽可能短小，为经常查询的字段添加索引，避免表扫描。</li><li><strong>调整事务隔离级别</strong>：在必要时使用更低的隔离级别（如 <code>READ COMMITTED</code>）。</li><li><strong>调整访问顺序</strong>：在应用层代码中，确保对不同资源的访问顺序在所有事务中都保持一致。</li><li><strong>使用提示</strong>：在极少数情况下，考虑使用锁提示（如 <code>NOLOCK</code>, <code>UPDLOCK</code>, <code>ROWLOCK</code>），但需谨慎评估其副作用。</li></ul></li></ol>
頁:
[1]