SQL Server數(shù)據(jù)庫中避免阻塞的步驟
以下的文章主要向大家描述的是SQL Server數(shù)據(jù)庫在實(shí)際操作中避免阻塞的實(shí)際操作步驟，在實(shí)際操作中我們大家都知道當(dāng)來自應(yīng)用程序的第一個(gè)連接控制鎖而第二個(gè)連接需要相沖突的鎖類型時(shí)，將發(fā)生阻塞。
其結(jié)果是強(qiáng)制第二個(gè)連接等待，而在第一個(gè)連接上阻塞。不管是來自同一應(yīng)用程序還是另外一臺客戶機(jī)上單獨(dú)的應(yīng)用程序，一個(gè)連接都可以阻塞另一個(gè)連接。
說明一些需要鎖保護(hù)的操作可能不明顯，例如系統(tǒng)目錄表和索引上的鎖。
大多數(shù)阻塞問題的發(fā)生是因?yàn)橐粋€(gè)進(jìn)程控制鎖的時(shí)間過長，導(dǎo)致阻塞的進(jìn)程鏈都在其它進(jìn)程上等待鎖。
常見的阻塞情形包括：
提交執(zhí)行時(shí)間長的查詢。
長時(shí)間運(yùn)行的查詢會(huì)阻塞其它查詢。例如，影響很多行的 DELETE 或 UPDATE 操作能獲取很多鎖，這些鎖不論是否升級到表鎖都阻塞其它查詢。因此，一般不要將長時(shí)間運(yùn)行的決策支持查詢和聯(lián)機(jī)事務(wù)處理 (OLTP) 查詢混在一起。解決方案是想辦法優(yōu)化查詢，如更改索引、將大的復(fù)雜查詢分成簡單的查詢或在空閑時(shí)間或單獨(dú)的計(jì)算機(jī)上運(yùn)行查詢。
查詢運(yùn)行時(shí)間長并由此導(dǎo)致阻塞的一個(gè)原因是這些查詢不適當(dāng)?shù)厥褂糜螛?biāo)。游標(biāo)可能是在結(jié)果集中瀏覽的便利方法，但使用游標(biāo)可能比使用面向集合的查詢慢。
取消沒有提交或回滾的查詢。
如果應(yīng)用程序取消查詢（如使用開放式數(shù)據(jù)庫連接 (ODBC) sqlcancel 函數(shù)）但沒有同時(shí)發(fā)出所需數(shù)目的 ROLLBACK 和 COMMIT 語句，則會(huì)發(fā)生這種情況。取消查詢并不自動(dòng)回滾或提交事務(wù)。取消查詢后，所有在事務(wù)內(nèi)獲取的鎖都將保留。應(yīng)用程序必須提交或回滾已取消的事務(wù)，從而正確地管理事務(wù)嵌套級。
應(yīng)用程序沒處理完所有結(jié)果。
將查詢發(fā)送到服務(wù)器后，所有應(yīng)用程序必須立即完成提取所有結(jié)果行。如果應(yīng)用程序沒有提取所有結(jié)果行，鎖可能會(huì)留在表上而阻塞其他用戶。如果使用的應(yīng)用程序?qū)?Transact-SQL 語句透明地提交給服務(wù)器，則該應(yīng)用程序必須提取所有結(jié)果行。
如果應(yīng)用程序沒這樣做（如果無法配置它執(zhí)行此操作），則可能無法解決阻塞問題。為避免此問題，可以將這些應(yīng)用程序限制在報(bào)表或決策支持?jǐn)?shù)據(jù)庫上。
分布式客戶端/服務(wù)器死鎖。
與常規(guī)死鎖不同，分布式死鎖無法由 Microsoft® SQL Server™ 2000 自動(dòng)檢測到。如果應(yīng)用程序打開多個(gè)與 SQL Server數(shù)據(jù)庫 的連接并異步提交查詢，則可能會(huì)發(fā)生分布式客戶端/服務(wù)器死鎖。
例如，一個(gè)客戶端應(yīng)用程序線程有兩個(gè)開放式連接。該線程異步啟動(dòng)事務(wù)并在第一個(gè)連接上發(fā)出查詢。應(yīng)用程序隨后啟動(dòng)其它事務(wù)，在另一個(gè)連接上發(fā)出查詢并等待結(jié)果。當(dāng) SQL Server數(shù)據(jù)庫 返回其中一個(gè)連接的結(jié)果時(shí)，應(yīng)用程序開始處理這些結(jié)果。應(yīng)用程序就這樣處理結(jié)果，直到生成結(jié)果的查詢被另一個(gè)連接上執(zhí)行的查詢阻塞而導(dǎo)致再?zèng)]有可用的結(jié)果為止。此時(shí)第一個(gè)連接阻塞，無限期等待處理更多的結(jié)果。第二個(gè)連接沒有在鎖上阻塞，但仍試圖將結(jié)果返回給應(yīng)用程序。然而，由于應(yīng)用程序阻塞而在第一個(gè)連接上等待結(jié)果，第二個(gè)連接的結(jié)果將得不到處理。
若要避免此問題，請執(zhí)行下列任一操作：
對每個(gè)查詢使用查詢超時(shí)。
對每個(gè)查詢使用鎖定超時(shí)。
使用綁定連接。
SQL Server 本質(zhì)上是受客戶端應(yīng)用程序操縱的傀儡。客戶端應(yīng)用程序?qū)Ψ?wù)器上獲取的鎖幾乎有完全的控制（并對鎖負(fù)責(zé)）。雖然 SQL Server數(shù)據(jù)庫鎖管理器自動(dòng)使用鎖保護(hù)事務(wù)，但這受客戶端應(yīng)用程序發(fā)出的查詢類型和對結(jié)果的處理方式的直接鼓動(dòng)。因此，大多數(shù)阻塞問題的解決方案都涉及檢查客戶端應(yīng)用程序。
阻塞問題常要求檢查應(yīng)用程序提交的 SQL 語句本身，以及檢查與連接管理、所有結(jié)果行的處理等有關(guān)的應(yīng)用程序行為本身。如果開發(fā)工具不允許顯式控制連接管理、查詢超時(shí)、結(jié)果處理等，阻塞問題可能得不到解決。
設(shè)計(jì)應(yīng)用程序以避免阻塞的準(zhǔn)則包括：
不要使用或設(shè)計(jì)使用戶得以填寫編輯框的應(yīng)用程序，編輯框會(huì)生成長時(shí)間運(yùn)行的查詢。例如，不要使用或設(shè)計(jì)提示用戶輸入的應(yīng)用程序，允許某些字段保留空白或允許輸入通配符。這可能導(dǎo)致應(yīng)用程序提交運(yùn)行時(shí)間過長的查詢，從而導(dǎo)致阻塞問題。
不要使用或設(shè)計(jì)使用戶得以在事務(wù)內(nèi)輸入內(nèi)容的應(yīng)用程序。
允許取消查詢。
使用查詢或鎖定超時(shí)，防止失控查詢和避免分布式死鎖。
立即完成提取所有結(jié)果行。
使事務(wù)盡可能簡短。
顯式控制連接管理。
在所預(yù)計(jì)的并發(fā)用戶全負(fù)荷下對應(yīng)用程序進(jìn)行應(yīng)力測試。
【編輯推薦】
