SQL Server阻塞問題詳解

阻塞定義

當來自應用程式的第一個串連控制鎖而第二個串連需要相衝突的鎖類型時，將發生阻塞。其結果是強制第二個串連等待，而在第一個串連上阻塞。不管是來自同一應用程式還是另外一台客戶機上單獨的應用程式，一個串連都可以阻塞另一個串連。

說明：一些需要鎖保護的操作可能不明顯，例如系統目錄表和索引上的鎖。大多數阻塞問題的發生是因為一個進程式控制制鎖的時間過長，導致阻塞的進程鏈都在其它進程上等待鎖。

常見的阻塞情形

1. 提交執行時間長的查詢。

長時間啟動並執行查詢會阻塞其它查詢。例如，影響很多行的 DELETE 或 UPDATE 操作能擷取很多鎖，這些鎖不論是否升級到表鎖都阻塞其它查詢。因此，一般不要將長時間啟動並執行決策支援查詢和聯機交易處理(OLTP)查詢混在一起。解決方案是想辦法最佳化查詢，如更改索引、將大的複雜查詢分成簡單的查詢或在空閑時間或單獨的電腦上執行查詢。

2. 查詢不適當地使用遊標。遊標可能是在結果集中瀏覽的便利方法，但使用遊標可能比使用面向集合的查詢慢。

3. 取消沒有提交或復原的查詢。

如果應用程式取消查詢（如使用開放式資料庫連接 (ODBC) sqlcancel 函數）但沒有同時發出所需數目的 ROLLBACK 和 COMMIT 語句，則會發生這種情況。取消查詢並不自動復原或提交事務。取消查詢後，所有在事務內擷取的鎖都將保留。應用程式必須提交或復原已取消的事務，從而正確地管理事務嵌套級。

4. 應用程式沒處理完所有結果。

將查詢發送到伺服器後，所有應用程式必須立即完成提取所有結果行。如果應用程式沒有提取所有結果行，鎖可能會留在表上而阻塞其他使用者。如果使用的應用程式將Transact-SQL 陳述式透明地提交給伺服器，則該應用程式必須提取所有結果行。如果應用程式沒這樣做（如果無法配置它執行此操作），則可能無法解決阻塞問題。為避免此問題，可以將這些應用程式限制在報表或決策支援資料庫上。

5. 分布式用戶端/伺服器死結。

與常規死結不同，分布式死結無法由 Microsoft SQL Server 2000 自動檢測到。如果應用程式開啟多個與 SQL Server 的串連並非同步提交查詢，則可能會發生分布式用戶端/伺服器死結。

例如，一個用戶端應用程式線程有兩個開放式串連。該線程非同步啟動事務並在第一個串連上發出查詢。應用程式隨後啟動其它事務，在另一個串連上發出查詢並等待結果。當 SQL Server 返回其中一個串連的結果時，應用程式開始處理這些結果。應用程式就這樣處理結果，直到產生結果的查詢被另一個串連上執行的查詢阻塞而導致再沒有可用的結果為止。此時第一個串連阻塞，無限期等待處理更多的結果。第二個串連沒有在鎖上阻塞，但仍試圖將結果返回給應用程式。然而，由於應用程式阻塞而在第一個串連上等待結果，第二個串連的結果將得不到處理。

避免阻塞的方法

1. 對每個查詢使用查詢逾時。

2. 對每個查詢使用鎖定逾時。有關更多資訊，請參見自訂鎖逾時。

3. 使用綁定串連。有關更多資訊，請參見使用綁定串連。

4. SQL Server 本質上是受用戶端應用程式操縱的傀儡。用戶端應用程式對伺服器上擷取的鎖幾乎有完全的控制（並對鎖負責）。雖然 SQL Server 鎖管理器自動使用鎖保護事務，但這受用戶端應用程式發出的查詢類型和對結果的處理方式的直接鼓動。因此，大多數阻塞問題的解決方案都涉及檢查用戶端應用程式。

5. 阻塞問題常要求檢查應用程式提交的 SQL 陳述式本身，以及檢查與串連管理、所有結果行的處理等有關的應用程式行為本身。如果開發工具不允許顯式控制串連管理、查詢逾時、結果處理等，阻塞問題可能得不到解決。

設計應用程式以避免阻塞的準則

1. 不要使用或設計使使用者得以填寫編輯框的應用程式，編輯框會產生長時間啟動並執行查詢。例如，不要使用或設計提示使用者輸入的應用程式，允許某些欄位保留空白或允許輸入萬用字元。這可能導致應用程式提交已耗用時間過長的查詢，從而導致阻塞問題。

2. 不要使用或設計使使用者得以在事務內輸入內容的應用程式。

3. 允許取消查詢。

4. 使用查詢或鎖定逾時，防止失控查詢和避免分布式死結。

5. 立即完成提取所有結果行。

6. 使事務儘可能簡短。

7. 顯式控制串連管理。

8. 在所預計的並發使用者全負荷下對應用程式進行應力測試。