什麼是 Deadlock#

Deadlock 是 blocking 的特殊形式：兩個（或多個）transaction 各自持有對方需要的鎖——誰都不能完成、誰都不能釋放。

flowchart LR
    S1[Session 1<br/>持有 A 的 X 鎖<br/>想要 B] -->|等待| B[Resource B]
    S2[Session 2<br/>持有 B 的 X 鎖<br/>想要 A] -->|等待| A[Resource A]
    A -.被 S1 持有.-> S1
    B -.被 S2 持有.-> S2

Figure 17-1: A graphical representation of a deadlock

Deadlock 是 效能問題，不只是錯誤訊息。它意味著：

  • 一個 transaction 必須回滾（rollback 本身耗資源）
  • 使用者通常會 重新提交（再吃一次資源）
  • 短時間內 deadlock 多 → 系統一直在做白工

Lock Monitor#

SQL Server 內部有 lock monitor 持續掃描，發現循環等待就選一個 victim——通常是估算成本較低的那個——回滾它，讓另一個繼續。

死結錯誤的標準訊息：

Msg 1205: Transaction (Process ID xx) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim.
Rerun the transaction.

Deadlock 的三種典型成因#

  • 循環依賴（cycle deadlock）：A 拿 R1 → 想要 R2；B 拿 R2 → 想要 R1
  • 鎖升級衝突（conversion deadlock）：A 與 B 各持 R 的 S 鎖，同時 想升級為 X
  • 平行查詢內 deadlock：同一查詢的多個 thread 互相等待——較罕見，新版多已修復

影響 Victim 選擇#

預設規則：成本較低的 transaction 被選為 victim。

SET DEADLOCK_PRIORITY LOW;          -- 字串值
SET DEADLOCK_PRIORITY -5;            -- 數值（-10 到 10）
SET DEADLOCK_PRIORITY HIGH;

一般不建議調整。只在某個高價值流程「絕對不能被回滾」時才設 HIGH——但仍然不保證 100% 不會被選中。

改變優先順序不能解決 deadlock，只能改變誰被犧牲。根因仍要修。

擷取 Deadlock 資訊#

四種方法，按推薦順序：

  1. Extended Events（首選）
  2. Trace flag 1222（落地到 SQL Server error log）
  3. Trace Events（成本高）
  4. Trace flag 1204（資訊最少）

內建 system_health Session#

SQL Server 從 2008 起就 預設開啟 system_health Extended Events session，自動記錄 deadlock graph——你什麼都不用做。

DECLARE @path NVARCHAR(260);
SELECT @path = path FROM sys.dm_os_server_diagnostics_log_configurations;
SELECT @path = @path + N'system_health_*';

WITH fxd AS (
    SELECT CAST(event_data AS XML) AS Event_Data
    FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL)
)
SELECT dl.deadlockgraph
FROM (
    SELECT dl.query('.') AS deadlockgraph
    FROM fxd
    CROSS APPLY Event_Data.nodes('(/event/data/value/deadlock)') AS d(dl)
) AS dl;

system_health 用固定大小、固定數量的檔案，舊資料會被覆寫。要長期保存或抓特定 deadlock，建議自建 XE session。

Figure 17-3: Results from querying the system_health session

自訂 XE Session#

兩個常用事件：

  • xml_deadlock_report：完整 deadlock graph（含查詢文字）
  • xml_deadlock_report_filtered：移除查詢文字以符合 GDPR / CPRA 等隱私規範——但少了 query 較難找根因

Figure 17-4: Information captured by the xml_deadlock_report event

Trace Flag 1222#

DBCC TRACEON (1222, -1);

-1 表示套用到所有 session。重啟後失效——要永久啟用須在 SQL Server Configuration Manager 加 -T1222 啟動參數。資訊輸出到 SQL Server error log。

Figure 17-2: SQL Server Configuration Manager window

解讀 Deadlock Graph#

三件必須拿到的資訊#

  • Sessions：誰跟誰打架
  • Resources：競爭哪些鎖
  • Queries：實際執行的 SQL

Graph 的視覺呈現#

flowchart LR
    P1[Process 1<br/>VICTIM ✗] -.etc.-> R1[Resource A<br/>Owner: Process 2]
    P2[Process 2] -.etc.-> R2[Resource B<br/>Owner: Process 1]
    R1 --> P1
    R2 --> P2

被打 X 的那個 process 即 victim。中間的方框是被爭奪的物件——每個方框內含 HoBT ID、index 名稱等識別資訊。

Figure 17-5: The graphical representation of the deadlock graph

Figure 17-6: The locks and objects involved in the deadlock

用 SSMS 開啟 deadlock graph，hover 在 process 上會顯示完整 query。XML 的 <inputbuf> 區塊是使用者送出的查詢；<executionStack> 顯示真正觸發鎖的內部呼叫——例如 trigger。

Figure 17-7: The query for the session involved in the deadlock

常見「看似簡單實則 trigger 作祟」#

書中的範例：

  • Session 1：UPDATE PurchaseOrderHeader → UPDATE PurchaseOrderDetail
  • Session 2：UPDATE PurchaseOrderDetail（觸發 trigger 也會 UPDATE PurchaseOrderHeader）

兩個 session 的 input 看似只動兩張表中的一張，但 trigger 偷偷加了第二張表的 UPDATE——就成了循環。

Deadlock 報告中若 <executionStack> 含 trigger 名稱，trigger 通常是真兇。檢查 trigger 邏輯能否簡化或拆出 transaction。

XML 關鍵節點#

  • <victim-list> / <victimProcess>：誰被回滾
  • <process-list>：每個牽涉的 process 細節（spid、isolation level、xactid、<inputbuf>、<executionStack>、<sqlhandle>）
  • <resource-list>：被鎖的物件（keylock / pagelock / objectlock 等），含 owner 與 waiter

Figure 17-8: A subset of the XML describing the deadlock

處理 Deadlock 的程式碼#

DECLARE @retry INT = 5;
WHILE @retry > 0
BEGIN
    BEGIN TRY
        BEGIN TRAN;
        -- ... do work ...
        COMMIT;
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205   -- deadlock victim
        BEGIN
            ROLLBACK;
            SET @retry -= 1;
            WAITFOR DELAY '00:00:00.100';   -- 小退避
        END
        ELSE
        BEGIN
            ROLLBACK;
            THROW;
        END
    END CATCH
END;

Deadlock 是可預期的執行時錯誤——程式應該重試。但只能重試有限次（避免錯誤被無限放大），且重試前小退避，避開立即再次衝突。

預防 Deadlock 的核心原則#

1. 以一致順序存取資源#

Deadlock 的根因是 存取順序不一致。讓所有 transaction 都以「先表 A、再表 B、再表 C」的固定順序操作 → 永遠不會循環。

2. 縮短 Transaction#

  • 不要把使用者輸入、外部 API 包進 transaction
  • 拆批
  • 把昂貴的查詢放在 transaction 外

3. 用合適的索引#

scan 全表會鎖過多資料 → 提高碰撞機率。設計索引讓 query 只鎖到該鎖的列。

4. 用較低 Isolation Level（在合理範圍內）#

  • RCSI（Read Committed Snapshot Isolation）：讀者不阻擋寫者 → 大幅減少 read/write 衝突
  • 注意：RCSI 不能完全消除 deadlock，但能消除許多 read-vs-write 類型

5. 簡化 Trigger 邏輯#

trigger 經常是 deadlock 隱藏的元兇。檢查：

  • trigger 內是否還動其他大表？
  • 能否改用其他機制（service broker、預先計算）？

6. 啟用 Optimized Locking（SQL Server 2025）#

XACT lock 機制減少了多種傳統 row/page lock 的競爭——詳第 16 章。

7. 避免長交易裡的 cursor / RBAR#

逐列鎖定 + 慢 = deadlock 工廠。改 set-based。

本章定調#

  • Deadlock = blocking 的循環版本，必有人被犧牲
  • 要解必先取得 graph：sessions、resources、queries 三件齊全
  • system_health 是免費好用的第一站；XE 自訂 session 是長期觀測
  • trigger / 隱性鎖升級 / 不一致存取順序是最常見三大根因
  • 程式應內建重試；但重試是補丁，不是修法
  • 預防 deadlock 的根本仍是縮小 transaction、固定順序、好索引、合適 isolation

下一章將進入 逐列處理（Row-by-Row Processing）——cursor 與其他逼迫 SQL Server 用非 set-based 方式處理的反模式。

Backward 封鎖與被封鎖行程 逐列處理：游標與其他成因 Forward