SQL Server 死鎖防範與排查實戰：從原理到 12 招降龍伏虎技巧

死鎖成因

兩行程分別鎖定資源 A、B，又嘗試鎖定對方持有的資源 → 循環等待。SQL Server 自動選擇犧牲者 (成本較低者) 終止並回滾，回傳錯誤 1205。

應用程式設計四大原則

1. 固定存取順序：所有行程以相同順序存取資料表/資源
2. 交易前收集輸入：使用者互動在交易外完成
3. 交易短小精悍：最小化鎖定持有時間
4. 適當降低隔離等級：如 Read Committed Snapshot (RCSI) 減少封鎖

12 招實戰防範技巧

1. Profiler 追蹤死鎖根因

建立追蹤 →「識別死鎖原因」範本 → 擷取死鎖圖 (deadlock graph) 分析參與物件、SQL、索引。

2. 死鎖重試邏輯：隨機等待

-- 應用程式端 pseudocode
for retry = 1 to 3
    try execute transaction
    catch deadlock (1205)
        wait Random(50, 200) ms  -- 關鍵：隨機避免同步重試再死鎖
    end try
end for

3. 簡化 T-SQL 交易

拆分複雜交易、減少鎖類型/數量。能拆分就拆分。

4. 邏輯運算/變數指派移出交易

條件判斷、變數計算、使用者輸入全部在 BEGIN TRAN 前完成。

5. 交易封裝於預存程序

• 減少客戶端-伺服器網路往返
• 強制交易完成或中止，避免「未完成交易」殘留鎖定

6. 悲觀鎖改樂觀檢查模式

-- 讀取時不加鎖
SELECT * FROM table WHERE id = @id;

-- 更新時檢查版本/時間戳
UPDATE table SET col = @val, version = version + 1 
WHERE id = @id AND version = @old_version;

-- @@ROWCOUNT = 0 表示資料已被他人修改

7. 最低限制隔離等級

預設 Read Committed 易造成共享鎖衝突。評估 Read Committed Snapshot (RCSI) 或 Snapshot Isolation (需 ALLOW_SNAPSHOT_ISOLATION ON)。

8. 游標優化

• 唯讀 → READ_ONLY 游標
• 需更新 → OPTIMISTIC (樂觀並發)
• 避免 SCROLL_LOCKS (悲觀鎖定每列)

9. 監控鎖定等待時間

效能監視器 → SQL Server Locks Object: Average Wait Time (ms) 識別熱點鎖類型 → Profiler 追蹤具體交易。

10. sp_who / sp_who2 找阻塞源頭

sp_who2 未文件化但提供 BlkBy、CPU、DiskIO、LastBatch，極實用。

11. 避免阻塞的索引與寫入策略

• 高頻表加叢集索引
• 避免單陳述式影響大量列 (分批處理)
• UPDATE/DELETE 確保走索引求取
• 巢狀交易小心提交/回滾衝突

12. 定期維護索引與統計資料

碎片索引導致掃描範圍擴大 → 鎖定更多頁面 → 死鎖機率增。排程 DBCC INDEXDEFRAG/REBUILD + UPDATE STATISTICS。

總結

死鎖無法 100% 根除，但透過應用設計規範 + 資料庫端最佳化 + 監控與重試機制，可將影響降至最低。關鍵在於：鎖定時間最短、範圍最小、順序固定、隔離等級適當。