SQL Server 死鎖成因深度解析與解決策略

核心原因：資源競爭

所有死鎖的根本原因都是多個進程對有限資源的競爭性鎖定順序不一致。

典型場景一：多表交叉鎖定

-- 進程 A
SELECT * FROM TableA WHERE ...  -- 鎖定 TableA
SELECT * FROM TableB WHERE ...  -- 等待 TableB

-- 進程 B
SELECT * FROM TableB WHERE ...  -- 鎖定 TableB
SELECT * FROM TableA WHERE ...  -- 等待 TableA

解決：

1. 調整程式邏輯，避免同一事務中同時鎖定多個資源。
2. 若必須多資源操作，全域統一鎖定順序（如永遠先鎖 TableA 再鎖 TableB）。

典型場景二：鍵升級死鎖

-- 進程 A (事務)
SELECT * FROM Table WHERE ID=1  -- 獲得共享鎖 (S)
UPDATE Table SET ... WHERE ID=1  -- 嘗試升級為排他鎖 (X)，需等待 B 釋放 S

-- 進程 B (事務)
UPDATE Table SET ... WHERE ID=1  -- 直接獲得排他鎖 (X)，但被 A 的 S 鎖阻塞

雙方互相等待對方釋放相容性較低的鎖，形成死鎖。

解決：在進程 A 的 SELECT 中顯式指定更新鎖：

SELECT * FROM Table WITH (UPDLOCK) WHERE ID=1

UPDLOCK 提示讓讀取階段直接獲得更新鎖 (U)，避免後續升級時的衝突。

其他預防措施

• 縮短事務長度，盡早提交或回滾。
• 設定合理的 SET LOCK_TIMEOUT 避免無限等待。
• 優化索引減少掃描範圍，降低鎖升級機率。
• 避免在觸發器中執行耗時操作。
• 嚴格測試所有預存程序與批次腳本的併發行為。

行級鎖定語法速查

-- 鎖定特定行 (需搭配適當隔離級別)
SELECT * FROM Table WITH (ROWLOCK, UPDLOCK) WHERE ID = 1

-- 鎖定整張表
SELECT * FROM Table WITH (TABLOCKX, HOLDLOCK)

掌握鎖相容性矩陣與鎖提示，是寫出高併發 SQL Server 應用的關鍵。