避免 SQL Server 阻塞鎖死：從應用程式設計到資料庫調優的完整策略

阻塞的本質：應用程式才是鎖的真正控制者

SQL Server 提供完善的鎖管理機制，但鎖的取得與釋放時機幾乎完全由用戶端應用程式的查詢模式與結果處理方式決定。應用設計稍有不當，即引發嚴重阻塞。

三大常見阻塞成因

1. 用戶取消查詢未回滾交易： 用戶強制關閉查詢視窗，若應用未發送 ROLLBACK，已取得的鎖持續保留，導致後續查詢阻塞。
2. 未及時提取所有結果列： 應用提交查詢後未立即讀取完整結果集（如分頁只讀第一頁），鎖持續佔用資源。
3. 長時間執行的查詢/更新： 大量資料 UPDATE/DELETE、缺乏索引的掃描、決策支援查詢與 OLTP 混跑，鎖升級為表鎖封鎖所有存取。

五大應用層面預防策略

1. 查詢完成後務必提取所有結果列

切勿為「提高回應速度」而部分讀取。若需分頁，使用 OFFSET/FETCH 或 ROW_NUMBER() 在伺服器端完成，避免游標停留。

2. 交易執行期間禁止使用者互動

等待使用者輸入會成倍拉長交易持續時間。所有參數應在交易啟動前備妥（透過變數、暫存表預載）。

3. 將長交易拆解為短交易批次

例：200 萬筆產品資料需更新 150 萬筆。
❌ 單一交易全量更新（鎖定數小時）。
✅ 依產品分類分批更新，每批次交易秒級完成，間隔釋放鎖。

4. 避免決策支援查詢與 OLTP 共用資源

報表查詢導向唯讀複本、資料倉庫或快照隔離資料庫，隔離寫入負載。

5. 監控與調優工具鏈

• sys.dm_tran_locks、sys.dm_os_waiting_tasks 即時阻塞鏈分析。
• 擴充事件 blocked_process_report 持久化記錄。
• 查詢存放區識別高耗用計畫，針對性建立索引或重寫 SQL。

資料庫層面輔助設定

• 啟用 READ_COMMITTED_SNAPSHOT 減少讀寫衝突。
• 設定合理的 LOCK_TIMEOUT 避免無限等待。
• 大量作業使用 TABLOCK 提示主動升級表鎖，減少鎖升級開銷。