雖然說SQL Server數據庫本身提供了很好的鎖管理機制但是從某一方面來說其實數據庫只是一些客戶端應用程序的傀儡這主要是因為客戶端應用程序對服務器上獲取的鎖幾乎有完全的控制能力客戶端應用程序發出的查詢請求以及對結果的處理方式往往具有直接的控制能力所以如果應用程序在設計上稍有不合理的情況時就會因為鎖機制而導致阻塞
如當遇到如下幾種情形時就可能會導致阻塞情況的發生
一客戶端取消查詢後沒有回滾實務
查詢是大部分應用程序經常發生的作業但是用戶通過前台客戶端應用程序查詢後台數據庫時有時候往往會因為各種原因取消查詢如用戶打開查詢窗口後因為死機或者用戶覺得反映速度慢強制取消查詢但是當客戶端取消查詢時若沒有加上回滾事務的語句則此時因為用戶已經向服務器發送了查詢請求所以後台數據庫中所涉及的表都已經加L上了鎖故即使用戶取消查詢後所有在事務內獲取的鎖都將會保留此時若其他用戶也需要查詢這些表或者用戶重新打開查詢窗口想通過輸入查詢條件來提高系統響應速度時就會發生阻塞的現象
二客戶端沒有及時取得所有查詢的結果
通常情況下用戶將查詢請求發送到服務器之後前台應用程序必須立即完成提取所有結果行如果應用程序沒有提取所有結果行的話就會產生一個問題因為只要應用程序沒有及時提取所有結果鎖可能會留在表上而阻塞其他用戶既然應用程序已經將SQ語句遞交給服務器則該應用程序就必須提取所有的結果行若應用程序不遵循這個原則的話(如因為一時疏漏而沒有配置)就無法從根本上解決阻塞問題
三查詢執行時間過長
有些查詢會耗用比較長的時間如因為查詢語句設計不合理或者查詢設計到的表與記錄比較多時都會使得查詢的執行時間加長如有時候用戶需要對紀錄進行Update或者Delete操作時如果涉及的行比較多時就會獲取很多的鎖這些鎖無論是否最終升級到表鎖都會阻塞其他查詢
故通常情況下不要將長時間運行的決策支持查詢和聯機事務處理查詢混在一起
當數據庫遇到阻塞時往往需要檢查應用程序遞交的SQL語句本身以及檢查與連接管理所有結果行的處理等有關的應用程序行為通常情況下為了避免因鎖沖突所導致的阻塞筆者有如下建議
建議一查詢完成後提取所有的結果行
有些應用程序為了提高用戶查詢的響應速度會有選擇的提取所需要的記錄這個小聰明看起來很合理但是卻會造成更大的浪費因為查詢結果沒有及時提取的話鎖就不能釋放當其他人查詢數據時就會發生阻塞
所以筆者建議在應用程序設計時對於數據庫中查詢的記錄要及時的提取可以通過其他方式如添加查詢條件或者後台查詢的方式來提高查詢的效率同時在應用程序層面設置合理的緩存也可以非常明顯的提高查詢效率
建議二在事務執行時不要讓用戶輸入內容
雖然在事務執性的過程中可以讓用戶參與進來以提高互動性但是我們數據庫管理員往往不建議這麼做因為若要用戶在事務執行過程中輸入參數會延長事務的執行時間雖然人比較聰明但是其反應速度仍然沒有電腦那麼快所以在執行過程中加入讓用戶參與的過程會延長事務的等待時間故除非有特殊的需要不要在應用程序的執行過程中提醒用戶輸入參數一些事務執行必須的參數最好在事先就提供如可以通過變量等預先把需要的參數傳入進去
建議三使事務盡可能的簡短
筆者認為數據庫管理員應該把一些問題簡單化當某個需求需要很多SQL語句才能夠完成時不妨把任務進行分解同時也把事務分解成一些簡短的事務
如數據庫中一張產品信息表其記錄數量有二百萬條現在處於管理的需要把一次性更改其中的一百五十萬條記錄時若通過一個事務進行更改則其時間會比較長若其中還牽涉到級聯更新的話則時間會更長
針對這種情況我們就可以學著把事務簡短話如這個產品信息中可能有產品類型字段那麼在更新數據時我們能否不一次性進行更新而是通過產品類別字段進行控制對記錄進行分次更新的如此每個類別的更新事務所耗用的時間就可能會大大縮短如此雖然操作的時候會需要多個步驟但是往往可以有效避免阻塞情況的發生提高數據庫的性能
建議四子查詢與列表框最好不要同時使用
有時候在應用程序設計的時候通過列表框確實可以提高用戶輸入的速度與准確率但是若前台應用程序沒有緩存機制的話往往會造成阻塞
如在一個訂單管理系統中可能需要頻繁的輸入銷售代表為了用戶輸入的方便銷售代表往往設計成一個列表框每次需要輸入時前台應用程序都會從後台中進行查詢銷售代表信息(如果應用程序沒有涉及緩存)一方面子查詢的速度本來就比較慢;其次列表框會生成長時間運行的查詢這兩個方面碰在一起就可能導致應用程序提高運行時間過程的查詢從而對其他用戶的查詢如系統管理員需要維護用戶信息而造成阻塞
所以在應用程序設計時子查詢最好少用而子查詢與列表框同時使用更需要禁止若避免不了的話則要在應用程序中實現緩存機制如此的話應用程序需要銷售代表信息的時候就會從應用程序緩存中取得而不會每次都去查詢數據庫
同時可以在這個列表框中設計重新查詢功能當用戶信息有變更如系統管理員加入了一個新的銷售代表在沒有進行重新查詢之前由於應用程序是從自身的緩存中取得數據所以沒有剛更新的內容此時用戶就需要運行重新查詢功能讓前台應用程序重新從數據庫中查詢信息這種設計就可以提高列表框與子查詢的執行時間有效避免阻塞問題
建議五在取消查詢時設置回退事務
前台應用程序設計時應該允許用戶臨時改變主意取消查詢如用戶查詢所有產品信息時可能會覺得響應時間比較長難以忍受此時他們就會想到取消查詢在這種情況下應用程序設計時就需要設計一個取消查詢按鈕用戶可以在查詢的過程中隨時點擊這個按鈕取消查詢同時在這個按鈕事件中需要注意加入一個回滾命令讓數據庫服務器能夠及時對相關記錄或者表進行解鎖
同時最好能夠采用鎖或者查詢超時機制這主要是因為有時候大量查詢也會耗費用戶主機的大量資源而導致客戶機死機此時若能夠采用查詢或者鎖超時機制即在查詢超時過後數據庫服務器自動對相關對象進行解鎖這也是數據庫管理員需要跟程序開發人員協商的一個問題
另外對數據庫連接采取顯式控制在所預計的並發用戶全負荷下對應用程序進行承受能力測試使用邦定連接對每個查詢使用查詢超時與鎖超時等等這些手段都可以有效避免鎖沖突產生的阻塞當數據庫管理員發現有阻塞的症狀時可以從這些方面出發尋找解決的措施
從以上的分析中可以看出SQL Server數據庫鎖是一把雙刃劍其在保障數據庫數據一致性的同時也會給數據庫造成一些負面影響如何把這些負面影響降到最低就是我們數據庫管理員的任務在應用程序設計時遵循如上建議可以有效解決因鎖問題產生的阻塞問題提高數據庫的性能可見要從根本上解決阻塞問題需要數據庫管理人員與程序開發人員共同努力
From:http://tw.wingwit.com/Article/program/SQL/201311/16390.html
