資料庫效能優化系列：死鎖成因與最小化策略

死鎖成因核心解析

死鎖由兩個互相阻塞的執行緒組成，雙方等待對方釋放資源。SQL Server 中常見於**兩個事務存在相反操作順序**：

• 事務 A：鎖資源 1 → 需資源 2
• 事務 B：鎖資源 2 → 需資源 1

死鎖監視器定期偵測，選擇「回滾成本最低」的犧牲者終止，拋出錯誤 1205 (舊版 1025)。

啟用快照隔離緩解讀寫死鎖

SQL Server 2005+ 支援 **快照隔離 (Snapshot Isolation / Read Committed Snapshot)**：

• 讀操作不加共享鎖，改讀版本鏈 (tempdb 存儲舊版本)
• 寫操作不阻塞讀、讀不阻塞寫
• 有效解決「讀/寫逆向阻塞」類死鎖

限制：寫/寫衝突 (雙方更新同一資源) 仍可能死鎖，需從業務邏輯層面避免逆向操作。

死鎖最小化核心原則

1. 寫/寫死鎖預防

• 統一存取順序：多表操作時，所有事務遵循相同表順序 (如先主表後從表)
• 縮小事務範圍：避免大批量更新；分批處理，減少排他鎖持有數量與時間
• 查詢前置、寫入後置：將 SELECT/計算移至事務前端，UPDATE/INSERT/DELETE 集中在尾部，縮短排他鎖窗口

2. 讀/寫死鎖預防 (同減少阻塞策略)

• 移除讀共享鎖：
  • 最佳：啟用資料庫級快照隔離 ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
  • 次佳：會話級 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 或查詢提示 (NOLOCK)
• 統一存取順序：查詢與修改遵循相同表/索引順序 (如先查主表再查從表，修改亦同序)
• 中間變數暫存查詢結果：減少事務中重複掃描與鎖持有

實務檢核清單

總結

死鎖理論上無法完全避免，但可透過「快照隔離解決讀寫衝突 + 統一順序/縮短事務/前置查詢解決寫寫衝突」將發生率壓至極低。關鍵在於**架構層面啟用快照隔離**與**開發規範強制統一存取順序**，並建立監控告警與自動重試機制。