SQL Server 死鎖核心成因與兩大解決模式：資源競爭、鎖升級與 UPDLOCK 實戰

所有死鎖的根本原因只有一個：**資源競爭**。本文歸納兩大典型死鎖模式，並給出針對性解決方案，核心在於「統一順序」與「UPDLOCK 避免共享鎖升級」。

模式一：跨表交互等待 (邏輯 Bug 型)

場景

• 交易 A：鎖定表 A → 嘗試鎖定表 B
• 交易 B：鎖定表 B → 嘗試鎖定表 A

解決

1. **嚴格統一鎖定順序**：所有交易對多表操作必須遵循相同順序 (如永遠先 A 後 B)。
2. **縮小交易範圍**：僅在必要時開啟交易，減少持鎖時間。
3. **避免單一交易鎖定過多資源**：拆分業務邏輯，必要時用應用層協調。

此類死鎖純屬應用邏輯缺陷，唯有重構代碼徹底解決。

模式二：讀取後更新同一資料列 (隱蔽型)

場景

• 交易 A：SELECT * FROM T WHERE id=1 (取得共享鎖 S) → UPDATE T SET ... WHERE id=1 (嘗試升級排他鎖 X)
• 交易 B：UPDATE T SET ... WHERE id=1 (持有 X 鎖)

結果：A 持有 S 鎖等待 B 釋放 X 鎖；B 持有 X 鎖等待 A 釋放 S 鎖 → 死鎖。此模式在中大型專案極常見，且不易在靜態代碼審查中發現。

解決：讀取階段直接取得更新鎖 (UPDLOCK)

-- 方案 1：查詢提示 (推薦，影響範圍最小)
SELECT * FROM Table1 WITH (UPDLOCK, HOLDLOCK) WHERE ...;

-- 方案 2：交易隔離層級 (影響整個交易)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 仍用共享鎖，不解決升級問題
-- 僅 UPDLOCK 能在讀取階段取得 U 鎖，U 鎖與 S 相容、與 U/X 不相容，打破循環等待

為何 UPDLOCK 有效？

• 共享鎖 (S) 允許並發讀取，但升級為排他鎖 (X) 時需等待所有 S 鎖釋放 → 易死鎖。
• 更新鎖 (U) 與 S 相容 (允許並發讀取)，但**同一時間僅允許一個交易持有 U 鎖**。
• 交易 A 先取 U 鎖，交易 B 之後嘗試取 U/X 鎖會被阻塞，不會形成循環等待。
• 確定要更新時，U 鎖平滑升級為 X 鎖，完成寫入。

輔助最佳實踐

快速排查死鎖腳本

-- 即時查看封鎖鏈
SELECT
    blocking_session_id AS BlockerSPID,
    session_id AS VictimSPID,
    wait_type,
    wait_time_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id > 0;

-- 查看死鎖受害者最近 SQL (需啟用 trace flag 1222 或 Extended Events)
-- 建議部署 Extended Events session 捕捉 xml_deadlock_report

掌握兩大模式與 UPDLOCK 核心技巧，配合快照隔離與架構規範，即能將死鎖風險降至最低。