微軟工程師深度講解：SQL Server 阻塞機制、常見成因與避免策略

阻塞是資料庫並發環境中不可避免的現象，但過度或長時間的阻塞會嚴重拖垮系統效能。本文彙整微軟工程師的官方技術白皮書核心內容，系統梳理阻塞的定義、五大典型成因、避免方法與應用程式設計準則，助您從根源上治理阻塞問題。

什麼是阻塞？

當連線 A 持有某資源的鎖，而連線 B 請求相衝突的鎖類型時，連線 B 被迫等待，此即「阻塞」。阻塞不分應用程式來源，同一應用或不同客戶端的連線皆可互相阻塞。值得注意的是，某些操作（如系統目錄表、索引維護）隱含鎖需求，極易被忽略。

大多數嚴重阻塞源於「單一進程持鎖時間過長」，引發連鎖反應，形成漫長的阻塞鏈。

五大常見阻塞情境

1. 長時間運行的查詢

影響大量資料列的 DELETE、UPDATE 操作會獲取海量鎖，無論是否升級為表鎖，皆會阻塞其他查詢。
對策：嚴禁將 OLAP 決策支援查詢與 OLTP 交易查詢混跑；優化索引、拆解複雜查詢、離峰執行或隔離至專用伺服器。

2. 不當使用游標

游標雖便於逐列處理，但效能遠遜於集合導向的集合操作。
對策：能用集合操作解決的，堅決不用游標。

3. 取消查詢卻未提交/回滾交易

應用程式呼叫 SQLCancel 取消查詢，若未同步發送足夠的 ROLLBACK/COMMIT，交易內持有的鎖將殘留。
對策：應用程式必須嚴格管理交易嵌套層級，確保取消操作後正確清理交易狀態。

4. 應用程式未提取所有結果集

查詢送達伺服器後，若應用程式未立即讀取完所有資料列，鎖可能持續佔用資料表。
對策：強制應用程式立即提取所有結果；若開發框架不支援，將此類應用限制於報表/決策支援資料庫。

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

SQL Server 2000 無法自動偵測此類死鎖。當應用程式開啟多連線並非同步提交查詢時，可能發生：連線 A 等待連線 B 的結果，連線 B 等待應用程式處理連線 A 的結果，雙方形成應用層死結。
對策：避免單一執行緒管理多個非同步連線；使用查詢/鎖逾時機制。

避免阻塞的五大核心方法

1. 設定查詢逾時：防止失控查詢無限期佔用資源。
2. 設定鎖定逾時：參考 SET LOCK_TIMEOUT，讓等待超時的陳述式自動失敗。
3. 使用繫結連線：讓多連線共享交易空間，減少跨連線鎖衝突。
4. 直指應用端：SQL Server 鎖管理器受客戶端查詢型態與結果處理方式直接驅動，解決阻塞的鑰匙在應用程式碼而非伺服器設定。
5. 雙軌檢查：同步審視 SQL 陳述式本身與連線管理、結果提取等應用行為；若開發工具不允許顯式控制連線/逾時/結果處理，阻塞問題可能無解。

設計應用程式以避免阻塞的八條鐵律

1. 禁止設計讓使用者輸入通配符、留空欄位而觸發長查詢的編輯介面。
2. 嚴禁在交易內等待使用者輸入（思考時間不應在交易中）。
3. 提供查詢取消功能，並正確處理取消後的交易清理。
4. 全域套用查詢或鎖定逾時，防禦失控查詢與分散式死鎖。
5. 查詢送出後，立即完成所有結果列的提取。
6. 交易保持最短，越短越好。
7. 顯式控制連線管理（開啟、關閉、交易邊界），不依賴隱式行為。
8. 上線前，在預期併發滿載下進行壓力測試，暴露潛在阻塞熱點。

阻塞治理無銀彈，唯有「優化查詢、縮短交易、規範應用行為、壓測驗證」四步走，方能構建高併發下的穩定系統。