SQL Server 阻塞與死鎖深度解析：成因、排查與預防全攻略

阻塞是資料庫並發控制的必然產物，但過度阻塞會嚴重拖垮系統回應。本文系統梳理阻塞定義、五大常見成因場景、排查工具鏈、應用層預防準則，助您從根源消除阻塞隱患。

核心概念：阻塞 vs 死鎖

• 阻塞：Session A 持有鎖，Session B 請求相衝突的鎖被迫等待。單向等待，最終 A 釋放鎖後 B 繼續。
• 死鎖：Session A 持有資源 1 等資源 2，Session B 持有資源 2 等資源 1。循環等待，引擎自動選擇犧牲者回滾。
• 分散式死鎖：跨多連線/多伺服器的死鎖，引擎無法自動偵測，需應用層超時機制介入。

五大高發阻塞場景與對策

1. 長時間運行查詢佔用大量鎖

徵兆：大量 UPDATE/DELETE 無 WHERE 或全表掃描，升級為資料表鎖 (TAB)，阻塞所有讀寫。
對策：優化索引消除掃描；拆分大批次操作 (分塊提交)；錯峰執行維護作業；啟用 RCSI (Read Committed Snapshot Isolation) 讓讀不阻寫。

2. 不當使用游標

徵兆：游標逐行處理長時間持有共享鎖，阻塞並發寫入。
對策：改用集合式操作 (JOIN/APPLY/窗口函數)；必須用游標時指定 FAST_FORWARD (唯讀、順向) 減少鎖持有時間。

3. 取消查詢未正確回滾交易

徵兆：應用呼叫 SQLCancel() 取消查詢，但未發送 ROLLBACK/COMMIT，交易保持開啟持續持有鎖。
對策：應用程式務必在 catch/finally 區塊中顯式 ROLLBACK；連線池啟用 Connection Reset (預設開啟) 自動清理。

4. 應用未讀取完整結果集

徵兆：中途關閉 DataReader/DataSet，留下未讀資料流導致共享鎖殘留。
對策：using 區塊確保 Close() 執行；或一次性讀入記憶體 (DataTable.Load()) 再處理業務邏輯。

5. 分散式客戶端/伺服器死鎖

徵兆：單一應用執行緒開多連線非同步提交，結果處理順序導致循環等待。
對策：單執行緒串行化多連線操作；使用繫結連線 (sp_getbindtoken) 共享交易空間；設定查詢/鎖逾時中斷死循環。

排查工具鏈

1. 即時視覺化：Activity Monitor / sp_who2 / sp_whoisactive (推薦) 查看阻塞鏈。
2. 歷史分析：sys.dm_exec_requests、sys.dm_tran_locks、sys.dm_os_waiting_tasks 聯查鎖資源與等待類型。
3. 根因定位：DBCC INPUTBUFFER(spid) 取得阻塞頭 SQL 文本；結合 Query Store / Extended Events 分析計畫。

應用層預防十大鐵律

1. 禁止 UI 直接拼接萬用字元查詢 (如 WHERE name LIKE '%input%')。
2. 嚴禁在使用者互動過程中開啟交易 (Think Time 導致鎖長持有)。
3. 所有查詢設定 CommandTimeout / SET LOCK_TIMEOUT。
4. 啟用樂觀並發 (RCSI / SNAPSHOT ISOLATION) 讓讀寫不互阻。
5. 立即讀取完整結果集，避免流式處理中斷。
6. 交易範圍最小化：只包含必要的寫操作，讀操作移至交易外。
7. 顯式管理連線生命週期，避免連線洩漏導致孤立交易。
8. 全負載壓測驗證並發行為，而非單用戶功能測試。
9. 長運行報表查詢導向唯讀副本/資料倉庫，隔離 OLTP。
10. 建立阻塞告警機制 (封鎖 > 30 秒自動通知 DBA)。