快捷導航

SQL Server死鎖排查的實戰(zhàn)指南

更新時間：2025年09月12日 09:04:16 作者：當歸1024

死鎖是指在一組進程中的各個進程均占有不會釋放的資源,但因互相申請被其他進程所站用不會釋放的資源而處于的一種永久等待狀態(tài),本文給大家詳細介紹了SQL Server死鎖排查的實戰(zhàn)指南,需要的朋友可以參考下

1. 查詢 system_health 會話中的歷史死鎖信息

system_health 是 SQL Server 默認啟用的擴展事件 (Extended Events) 會話。它會自動捕獲包括死鎖 (xml_deadlock_report) 在內(nèi)的多種系統(tǒng)事件，是排查近期發(fā)生的死鎖的首選之地。

查詢腳本

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;

結(jié)果集說明

列名	數(shù)據(jù)類型	說明
DeadlockDateTime	datetime	死鎖事件發(fā)生的準確日期和時間。
DeadlockGraph	xml	死鎖圖，以 XML 格式描述死鎖的完整信息。這是分析死鎖的最關(guān)鍵信息。

如何分析死鎖圖 (DeadlockGraph)

在 SSMS (SQL Server Management Studio) 中執(zhí)行上述查詢。
點擊結(jié)果集中 DeadlockGraph 列的 XML 鏈接。
SSMS 會在一個新的窗口中以圖形化的方式展示死鎖關(guān)系圖，非常直觀。
- 橢圓：表示參與死鎖的進程（SPID）。
- 矩形：表示被爭搶的資源（如鍵、頁、表、行）。
- 箭頭：表示進程對資源的請求和等待關(guān)系。
在圖形化界面中，可以清楚地看到：
- 哪些兩個（或多個）會話被卷入死鎖。
- 它們各自持有(owner)什么資源，又在等待(waiter)什么資源。
- 它們當時正在執(zhí)行的 T-SQL 語句（inputbuf）。

2. 查詢特定會話 (SPID) 的詳細信息

當從死鎖圖或其它途徑（如監(jiān)控、錯誤日志）獲知一個具體的會話 ID (SPID) 后，可以使用以下查詢來獲取該會話的詳細實時狀態(tài)和資源使用情況。

查詢腳本

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, -- **關(guān)鍵：阻塞此會話的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; -- **替換為你需要關(guān)注的 SPID**

關(guān)鍵字段說明 (用于死鎖/阻塞分析)

字段	來源	說明
session_id	s	會話 ID (SPID)。
login_name / host_name / program_name	s	幫助定位問題來源：哪個用戶、從哪臺機器、通過什么程序連接的。
status	s	會話狀態(tài)（如 running, sleeping）。
command	r	當前正在執(zhí)行的命令類型（如 SELECT, UPDATE, INSERT）。
wait_type / wait_time	r	如果會話被阻塞，這里會顯示它正在等待的資源類型（如 LCK_M_X）和已等待時間（毫秒）。NULL 表示未被阻塞。
wait_resource	r	會話正在等待的具體資源（如 KEY: 5:72057594048872448 (xxxxxxxx)）。
blocking_session_id	r	至關(guān)重要！表示是哪個 SPID 阻塞了當前會話。如果 > 0，則說明此會話正被另一個會話阻塞。這是排查阻塞鏈的核心字段。
last_request_start_time / last_request_end_time	s	會話最后一次請求的開始和結(jié)束時間。

總結(jié)與排查步驟建議

發(fā)現(xiàn)死鎖：通過應(yīng)用程序錯誤日志、SQL Server 錯誤日志或監(jiān)控工具發(fā)現(xiàn)死鎖錯誤（錯誤號 1205）。
獲取死鎖圖：使用第一個查詢從 system_health 會話中提取最近的死鎖圖。
分析死鎖圖：在 SSMS 中圖形化查看死鎖圖，確定涉及的主要會話 (SPID) 和爭搶的資源。
調(diào)查會話詳情：將圖形中發(fā)現(xiàn)的 SPID 代入第二個查詢，了解這些會話的詳細信息（誰發(fā)起的、從哪里來、在做什么操作），特別是 blocking_session_id 字段可以幫助確認阻塞關(guān)系。
解決問題：根據(jù)分析結(jié)果，通常的解決方案包括：
- 優(yōu)化查詢/索引：確保事務(wù)盡可能短小，為經(jīng)常查詢的字段添加索引，避免表掃描。
- 調(diào)整事務(wù)隔離級別：在必要時使用更低的隔離級別（如 READ COMMITTED）。
- 調(diào)整訪問順序：在應(yīng)用層代碼中，確保對不同資源的訪問順序在所有事務(wù)中都保持一致。
- 使用提示：在極少數(shù)情況下，考慮使用鎖提示（如 NOLOCK, UPDLOCK, ROWLOCK），但需謹慎評估其副作用。