快捷導(dǎo)航

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

更新時(shí)間：2025年09月12日 09:04:16 作者：當(dāng)歸1024

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

1. 查詢 system_health 會(huì)話中的歷史死鎖信息

system_health 是 SQL Server 默認(rèn)啟用的擴(kuò)展事件 (Extended Events) 會(huì)話。它會(huì)自動(dòng)捕獲包括死鎖 (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é)果集說(shuō)明

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

如何分析死鎖圖 (DeadlockGraph)

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

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

當(dāng)從死鎖圖或其它途徑（如監(jiān)控、錯(cuò)誤日志）獲知一個(gè)具體的會(huì)話 ID (SPID) 后，可以使用以下查詢來(lái)獲取該會(huì)話的詳細(xì)實(shí)時(shí)狀態(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)鍵：阻塞此會(huì)話的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)鍵字段說(shuō)明 (用于死鎖/阻塞分析)

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

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

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