SQL Server 死鎖根因與解決方案：資源競爭、存取順序與 UPDLOCK 實戰

死鎖本質

所有死鎖最深層原因：**資源競爭**。兩類典型表現：

表現一：多資源循環等待 (程式邏輯 Bug)

-- 用戶 A
BEGIN TRAN
UPDATE TableA SET ... WHERE ...  -- 鎖 TableA
UPDATE TableB SET ... WHERE ...  -- 等待 TableB
COMMIT

-- 用戶 B (同時)
BEGIN TRAN
UPDATE TableB SET ... WHERE ...  -- 鎖 TableB
UPDATE TableA SET ... WHERE ...  -- 等待 TableA
COMMIT

雙方各持一鎖、互待對方 → 死鎖。

解決

1. **避免同時鎖定多資源**
2. **必須鎖定多資源時，全域統一存取順序** (如按表名字母序)

表現二：讀後寫升級鎖衝突 (隱性高發)

-- 用戶 A (交易)
SELECT * FROM Table WHERE id = 1;  -- 取得共享鎖 (S)
-- ... 業務邏輯 ...
UPDATE Table SET col = val WHERE id = 1;  -- 嘗試 S → X 升級

-- 用戶 B (同時)
UPDATE Table SET col = val2 WHERE id = 1;  -- 直接取得 X，因 A 持有 S 而等待

A 等 B 釋放 X 以升級，B 等 A 釋放 S 以取得 X → **死鎖**。

解決：SELECT 時直接取得更新鎖 (U)

SELECT * FROM Table WITH (UPDLOCK) WHERE id = 1;

• U 鎖與 S 相容、與 U/X 不相容
• 同一時間僅一交易可取得資源的 U 鎖
• 後續 UPDATE 時 U → X 自然轉換，避免 S→X 升級衝突

補充防範清單

• 縮短交易範圍，儘量在單一陳述式完成
• 設定 SET LOCK_TIMEOUT 避免無限等待
• 啟用追蹤旗標 1222/1204 記錄死鎖圖 (SQL 2000/2005)
• 使用 sys.dm_os_waiting_tasks + sys.dm_tran_locks 即時診斷 (SQL 2005+)
• 考慮 READ_COMMITTED_SNAPSHOT (RCSI) 降低讀寫衝突 (需評估 tempdb 壓力)