本文檔介紹了兩項(xiàng)關(guān)鍵的 T-SQL 查詢，用于排查 Microsoft SQL Server 中的死鎖問題。

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é)果集說明

如何分析死鎖圖 (DeadlockGraph)

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

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

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

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

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

到此這篇關(guān)于SQL Server死鎖排查的實(shí)戰(zhàn)指南的文章就介紹到這了,更多相關(guān)SQL Server死鎖排查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論