應用程式防死鎖設計指南：從架構到代碼的系統性策略

徹底解決死鎖：應用層與資料庫層的系統性防禦體系

死鎖是鎖基礎並發系統的必然產物。當兩個連線互相持有對方需要的鎖且均不釋放時，SQL Server 會自動選擇犧牲者回滾。雖然重試機制能緩解，但頻繁死鎖會嚴重拖垮吞吐量。本文從設計階段著手，構建全方位防死鎖策略。

核心原則：縮短事務、固定順序、降低隔離

1. 事務極小化

• 只在事務中包含 必要的寫入操作；讀取、計算、外部 API 呼叫、使用者互動全部移至事務外
• 拆分大事務：批次更新改為分批小事務，配合冪等設計

2. 統一資源存取順序

• 全系統規定 表存取順序 (如：Parent → Child → Log)，所有模組嚴格遵守
• 單表內按 主鍵順序 更新多筆資料
• 避免交叉更新：Session A 更新 Table1→Table2，Session B 更新 Table2→Table1

3. 降低隔離等級 (視業務容許度)

• READ COMMITTED SNAPSHOT (RCSI)：讀取不加共享鎖，寫入不阻塞讀取，大幅減少死鎖機率
• SNAPSHOT ISOLATION：需應用層處理更新衝突，適合高併發低衝突場景

進階技巧：應用層與 T-SQL 層面

4. 封裝於預存程序

• 將 BEGIN/COMMIT TRAN 置於 SP 內部，減少網路往返導致的鎖持有時間
• SP 強制原子完成或回滾，杜絕「人為忘記提交」導致的長鎖持有

5. 樂觀鎖替代悲觀鎖

• 表加 RowVersion (timestamp) 欄位
• 更新時 WHERE ID=@id AND RowVer=@oldVer，@@ROWCOUNT=0 即觸發重試邏輯
• 完全避免 UPDLOCK/HOLDLOCK 等顯式鎖提示

6. 減少鎖粒度升級風險

• 確保更新/刪除有 覆蓋索引 支援 Seek，避免掃描大量資料導致頁鎖/表鎖升級
• 大批量刪除/更新分批次 (如每批 5000 筆)，配合 WAITFOR DELAY 釋放鎖

7. 遊標與鎖提示謹慎使用

• 唯讀遊標加 READ_ONLY，更新遊標用 OPTIMISTIC，禁用 SCROLL_LOCKS
• 除非極度確定，否則 不要手動加鎖提示 (UPDLOCK, HOLDLOCK, TABLOCKX)

監控與診斷體系

• Extended Events 捕捉 xml_deadlock_report，自動解析死鎖圖
• sys.dm_os_wait_stats 監控 LCK_M_* 等待趨勢
• 建立死鎖告警：單位時間死鎖數 > 閾值 → 觸發通知

重試機制最佳實踐

-- 應用層偽碼 (指數退避 + 抖動)
for attempt in 1..MaxRetries:
    try:
        ExecuteTransaction()
        break
    catch DeadlockException:
        if attempt == MaxRetries: throw
        wait = Random(2^attempt * BaseDelay * 0.5, 2^attempt * BaseDelay * 1.5)
        Sleep(wait)

關鍵：隨機抖動防止多個重試者同步再次衝突。

總結檢查清單

1. ☐ 事務是否只含寫操作？
2. ☐ 所有模組表存取順序一致？
3. ☐ 是否啟用 RCSI？
4. ☐ 更新語句是否有索引 Seek？
5. ☐ 是否移除不必要的鎖提示？
6. ☐ 是否有死鎖監控與告警？
7. ☐ 重試邏輯是否含指數退避+抖動？

死鎖不可完全消除，但透過系統性工程，可將其頻率壓至業務可接受極低水準。