阻塞定義
連線 A 持有鎖，連線 B 請求相衝突的鎖類型 → B 等待 A 釋放鎖。阻塞可跨應用程式、跨用戶端發生。
五大常見阻塞情境
- 長時間執行查詢：大量 UPDATE/DELETE 取得大量鎖，甚至升級為資料表鎖。
→ 方案：優化索引、拆分大交易、離峰執行、隔離 OLTP/OLAP 工作負載。
- 不當使用游標：游標逐列處理慢於集合式操作，延長鎖持有時間。
→ 方案：改寫為集合式 SQL、若必須用游標設定 FAST_FORWARD、READ_ONLY。
- 取消查詢未提交/回滾：ODBC
SQLCancel 僅取消陳述式，交易仍開啟，鎖保留。
→ 方案：應用程式必須在取消後明確呼叫 ROLLBACK 或 COMMIT。
- 應用程式未讀取完所有結果：結果集未耗盡 → 鎖保留於資料表。
→ 方案：立即讀取完畢或使用伺服器端游標；無法修改應用程式時，限制其僅存取報表/決策支援資料庫。
- 分散式用戶端/伺服器死結：單一用戶端開多連線非同步提交，SQL Server 無法自動偵測。
→ 方案：避免單一執行緒多連線非同步；使用繫結連接 (sp_getbindtoken/sp_bindsession) 協調。
避免阻塞的五項原則
- 設定查詢逾時 (Command Timeout)。
- 設定鎖逾時 (
SET LOCK_TIMEOUT)。
- 善用繫結連接 協調多連線交易。
- 認清用戶端應用程式是鎖管理的真正主導者：查詢型態、結果處理方式直接決定鎖行為。
- 阻塞排查必須同時檢視 SQL 陳述式與應用程式行為 (連線管理、結果讀取、巢狀交易)。開發工具若不支援顯式控制，阻塞可能無解。
設計應用程式避免阻塞的準則
- 縮短交易範圍：
BEGIN TRAN 儘量晚開，COMMIT 儘量早關。
- 避免使用者互動在交易中 (等待輸入導致鎖長時間持有)。
- 使用
READ COMMITTED SNAPSHOT (RCSI) 或 SNAPSHOT 隔離等級，以版本存儲取代共享鎖，實現讀寫不阻塞 (需評估 tempdb 壓力)。
- 大量異動採用分批提交，避免單一巨大交易。
- 監控
sys.dm_exec_requests, sys.dm_tran_locks, sys.dm_os_waiting_tasks 即時偵測阻塞鏈。
結語
阻塞本質是資源爭用，解決之道在於縮短持有時間、降低衝突機率、提供替代路徑。DBA 需與開發人員共同審視應用程式邏輯，而非單純調整伺服器參數。
