減少 SQL Server 死鎖的 15 條實戰鐵律與進階技巧

死鎖不可完全避免，但可將機率壓至極低

死鎖發生時 SQL Server 犧牲犧牲者 (Victim) 回滾事務，頻繁死鎖會嚴重拖垮吞吐量。以下 15 條鐵律涵蓋應用設計、事務編碼、索引維護、監控診斷全生命週期。

應用與事務設計鐵律 (1-7)

1. 統一資源存取順序：所有事務按相同表/資源順序加鎖（如先 `Customer` 再 `Order`），打破循環等待條件。
2. 事務前收集使用者輸入：絕不在事務中等待使用者輸入（`Console.ReadLine`、Web 表單提交），輸入收集完成再 `BEGIN TRAN`。
3. 事務極小化：只包含必要的 DML，邏輯判斷、變數賦值、複雜計算全移到事務外。
4. 降低隔離等級：評估後在安全前提下用 `READ COMMITTED SNAPSHOT` (RCSI) 或 `SNAPSHOT` 隔離，減少共享鎖持有時間。
5. 封裝於預存程序：`BEGIN/COMMIT/ROLLBACK` 內建於 SP，減少 Client-Server 往返、防止 Client 中斷留下孤立事務。
6. 樂觀鎖取代悲觀鎖：`SELECT ...` 不加 `UPDLOCK`/`HOLDLOCK`；更新時 `WHERE RowVersion = @OldVersion`，失敗則重試。
7. 避免長時間掃描鎖定：先 `SELECT` 無鎖讀取 → 用戶確認 → 再 `UPDATE WHERE Key = @Key AND RowVersion = @Old`。

進階技巧 (8-15)

8. 死鎖重試邏輯**：捕捉錯誤 1205 → `WAITFOR DELAY '00:00:00.' + CAST(RAND()*100 AS VARCHAR(3))` (隨機毫秒) → 重試，**隨機延遲防止活鎖**。
9. 簡化/拆分複雜事務**：大事務拆為多個小事務，減少鎖資源數量與持有時間。
10. 所有預備操作移出事務**：條件判斷、變數賦值、暫存表填充、游標開啟 — 全在 `BEGIN TRAN` 前完成。
11. 游標選項優化**：唯讀用 `READ_ONLY`，更新用 `OPTIMISTIC`，**禁用 `SCROLL_LOCKS`** (會持有鎖至游標關閉)。
12. 鎖等待監控**：`sys.dm_os_wait_stats` 關注 `LCK_M_*` 平均等待時間；`sp_who2`/`sp_WhoIsActive` 即時定位阻塞鏈。
13. 索引支援 UPDATE/DELETE**：確保 `WHERE` 條件欄位有索引，避免全表掃描升級為表鎖。
14. 避免大批次單一陳述句大量 `INSERT/UPDATE/DELETE` 分批 (如 5000 筆/批) 提交，減少鎖升級與記錄檔衝擊。
15. 嵌套事務謹慎用**：SQL Server 不支援真正嵌套，內層 `ROLLBACK` 會回滾外層；改用 `SAVE TRANSACTION` + 條件判斷。
16. 定期跑 Profiler/Extended Events 死鎖圖**：`Deadlock Graph` 事件輸出 `.xdl`，用 SSMS 開啟視覺化分析受害者/贏家/資源/陳述句。

死鎖圖快速閱讀指南

• victim-process：被選中犧牲的 SPID。
• resource-list → keylock/objectlock/pagelock：爭用資源 (索引/表/頁)。
• process → executionStack → frame：具體 T-SQL 陳述句與隔離等級。
• 找出 **共同資源 + 相反順序** → 調整存取順序或加索引消除掃描。

結語

**死鎖 = 循環等待 + 不可搶佔 + 請求保持 + 互斥**。打破任一條件即可預防。將 15 條鐵律內化為團隊編碼規範，配合自動化死鎖監控與重試框架，讓死鎖從「頻繁事故」變為「極罕見意外」。