MS SQL Server數據庫事務鎖機制分析

　　鎖是網絡數據庫中的一個非常重要的概念它主要用於多用戶環境下保證數據庫完整性和一致性各種大型數據庫所采用的鎖的基本理論是一致的但在具體實現上各有差別目前大多數數據庫管理系統都或多或少具有自我調節自我管理的功能因此很多用戶實際上不清楚鎖的理論和所用數據庫中鎖的具體實現
　　
　　Microsoft SQL Server（以下簡稱SQL Server）作為一種中小型數據庫管理系統已經得到了廣泛的應用該系統更強調由系統來管理鎖在用戶有SQL請求時系統分析請求自動在滿足鎖定條件和系統性能之間為數據庫加上適當的鎖同時系統在運行期間常常自動進行優化處理實行動態加鎖對於一般的用戶而言通過系統的自動鎖定管理機制基本可以滿足使用要求但如果對數據安全數據庫完整性和一致性有特殊要求就必須自己控制數據庫的鎖定和解鎖這就需要了解SQL Server的鎖機制掌握數據庫鎖定方法
　　
　　鎖的多粒度性以及鎖升級
　　
　　數據庫中的鎖是指一種軟件機制用來指示某個用戶（也即進程會話下同）已經占用了某種資源從而防止其他用戶做出影響本用戶的數據修改或導致數據庫數據的非完整性和非一致性這兒所謂資源主要指用戶可以操作的數據行索引以及數據表等根據資源的不同鎖有多粒度（multigranular）的概念也就是指可以鎖定的資源的層次SQL Server中能夠鎖定的資源粒度包括數據庫表區域頁面鍵值（指帶有索引的行數據）行標識符（RID即表中的單行數據）
　　
　　采用多粒度鎖的重要用途是用來支持並發操作和保證數據的完整性SQL Server根據用戶的請求做出分析後自動給數據庫加上合適的鎖假設某用戶只操作一個表中的部分行數據系統可能會只添加幾個行鎖（RID）或頁面鎖這樣可以盡可能多地支持多用戶的並發操作但是如果用戶事務中頻繁對某個表中的多條記錄操作將導致對該表的許多記錄行都加上了行級鎖數據庫系統中鎖的數目會急劇增加這樣就加重了系統負荷影響系統性能因此在數據庫系統中一般都支持鎖升級(lock escalation)所謂鎖升級是指調整鎖的粒度將多個低粒度的鎖替換成少數的更高粒度的鎖以此來降低系統負荷在SQL Server中當一個事務中的鎖較多達到鎖升級門限時系統自動將行級鎖和頁面鎖升級為表級鎖特別值得注意的是在SQL Server中鎖的升級門限以及鎖升級是由系統自動來確定的不需要用戶設置
　　
　　鎖的模式和兼容性
　　
　　在數據庫中加鎖時除了可以對不同的資源加鎖還可以使用不同程度的加鎖方式即鎖有多種模式SQL Server中鎖模式包括
　　
　　．共享鎖
　　
　　SQL Server中共享鎖用於所有的只讀數據操作共享鎖是非獨占的允許多個並發事務讀取其鎖定的資源默認情況下數據被讀取後SQL Server立即釋放共享鎖例如執行查詢SELECT * FROM my_table時首先鎖定第一頁讀取之後釋放對第一頁的鎖定然後鎖定第二頁這樣就允許在讀操作過程中修改未被鎖定的第一頁但是事務隔離級別連接選項設置和SELECT語句中的鎖定設置都可以改變SQL Server的這種默認設置例如 SELECT * FROM my_table HOLDLOCK就要求在整個查詢過程中保持對表的鎖定直到查詢完成才釋放鎖定
　　
　　．修改鎖
　　
　　修改鎖在修改操作的初始化階段用來鎖定可能要被修改的資源這樣可以避免使用共享鎖造成的死鎖現象因為使用共享鎖時修改數據的操作分為兩步首先獲得一個共享鎖讀取數據然後將共享鎖升級為獨占鎖然後再執行修改操作這樣如果同時有兩個或多個事務同時對一個事務申請了共享鎖在修改數據的時候這些事務都要將共享鎖升級為獨占鎖這時這些事務都不會釋放共享鎖而是一直等待對方釋放這樣就造成了死鎖如果一個數據在修改前直接申請修改鎖在數據修改的時候再升級為獨占鎖就可以避免死鎖修改鎖與共享鎖是兼容的也就是說一個資源用共享鎖鎖定後允許再用修改鎖鎖定
　　
　　．獨占鎖
　　
　　獨占鎖是為修改數據而保留的它所鎖定的資源其他事務不能讀取也不能修改獨占鎖不能和其他鎖兼容
　　
　　．結構鎖
　　
　　結構鎖分為結構修改鎖（SchM）和結構穩定鎖（SchS）執行表定義語言操作時SQL Server采用SchM鎖編譯查詢時SQL Server采用SchS鎖
　　
　　．意向鎖
　　
　　意向鎖說明SQL Server有在資源的低層獲得共享鎖或獨占鎖的意向例如表級的共享意向鎖說明事務意圖將獨占鎖釋放到表中的頁或者行意向鎖又可以分為共享意向鎖獨占意向鎖和共享式獨占意向鎖共享意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來讀取數據獨占意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置獨占鎖來修改數據共享式獨占鎖說明事務允許其他事務使用共享鎖來讀取頂層資源並意圖在該資源低層上放置獨占鎖
　　
　　．批量修改鎖
　　
　　批量復制數據時使用批量修改鎖可以通過表的TabLock提示或者使用系統存儲過程sp_tableoption的table lock on bulk load選項設定批量修改鎖
　　
　　另外SQL Server命令語句操作會影響鎖定的方式語句的組合也同樣能產生不同的鎖定詳情如下表
　　
　　鎖沖突及其防止辦法
　　
　　在數據庫系統中死鎖是指多個用戶（進程）分別鎖定了一個資源並又試圖請求鎖定對方已經鎖定的資源這就產生了一個鎖定請求環導致多個用戶（進程）都處於等待對方釋放所鎖定資源的狀態
　　
　　在SQL Server中系統能夠自動定期搜索和處理死鎖問題系統在每次搜索中標識所有等待鎖定請求的進程會話如果在下一次搜索中該被標識的進程仍處於等待狀態SQL Server就開始遞歸死鎖搜索
　　
　　當搜索檢測到鎖定請求環時系統將根據各進程會話的死鎖優先級別來結束一個優先級最低的事務此後系統回滾該事務並向該進程發出號錯誤信息這樣其他事務就有可能繼續運行了死鎖優先級的設置語句為
　　
　　SET DEADLOCK_PRIORITY { LOW | NORMAL}
　　
　　其中LOW說明該進程會話的優先級較低在出現死鎖時可以首先中斷該進程的事務另外各進程中通過設置LOCK_TIMEOUT選項能夠設置進程處於鎖定請求狀態的最長等待時間該設置的語句
　　
　　SET LOCK_TIMEOUT { timeout_period }
　　
　　其中timeout_period以毫秒為單位
　　
　　理解了死鎖的概念在應用程序中就可以采用下面的一些方法來盡量避免死鎖了
　　
　　（）合理安排表訪問順序
　　
　　（）在事務中盡量避免用戶干預盡量使一個事務處理的任務少些
　　
　　（）采用髒讀技術髒讀由於不對被訪問的表加鎖而避免了鎖沖突在客戶機/服務器應用環境中有些事務往往不允許讀髒數據但在特定的條件下我們可以用髒讀
　　
　　（）數據訪問時域離散法數據訪問時域離散法是指在客戶機/服務器結構中采取各種控制手段控制對數據庫或數據庫中的對象訪問時間段主要通過以下方式實現: 合理安排後台事務的執行時間采用工作流對後台事務進行統一管理工作流在管理任務時一方面限制同一類任務的線程數（往往限制為個）防止資源過多占用; 另一方面合理安排不同任務執行時序時間盡量避免多個後台任務同時執行另外 避免在前台交易高峰時間運行後台任務
　　
　　（）數據存儲空間離散法數據存儲空間離散法是指采取各種手段將邏輯上在一個表中的數據分散到若干離散的空間上去以便改善對表的訪問性能主要通過以下方法實現: 第一將大表按行或列分解為若干小表; 第二按不同的用戶群分解
　　
　　（）使用盡可能低的隔離性級別隔離性級別是指為保證數據庫數據的完整性和一致性而使多用戶事務隔離的程度SQL定義了種隔離性級別未提交讀提交讀可重復讀和可串行如果選擇過高的隔離性級別如可串行雖然系統可以因實現更好隔離性而更大程度上保證數據的完整性和一致性但各事務間沖突而死鎖的機會大大增加大大影響了系統性能
　　
　　（）使用Bound ConnectionsBound connections 允許兩個或多個事務連接共享事務和鎖而且任何一個事務連接要申請鎖如同另外一個事務要申請鎖一樣因此可以允許這些事務共享數據而不會有加鎖的沖突
　　
　　（）考慮使用樂觀鎖定或使事務首先獲得一個獨占鎖定一個最常見的死鎖情況發生在系列號生成器中它們通常是這樣編寫的
　　
　　begin tran
　　
　　select new_id from keytab holdlock
　　
　　update keytab set new_id＝new_id+l
　　
　　commit tran
　　
　　如果有兩個用戶在同時運行這一事務他們都會得到共享鎖定並保持它當兩個用戶都試圖得到keytab表的獨占鎖定時就會進入死鎖為了避免這種情況的發生應將上述事務重寫成如下形式
　　
　　begin tran
　　
　　update keytab set new_id=new_id+l
　　
　　select new_id from keytab
　　
　　commit tran
　　
　　以這種方式改寫後只有一個事務能得到keytab的獨占鎖定其他進程必須等到第一個事務的完成這樣雖增加了執行時間但避免了死鎖
　　
　　如果要求在一個事務中具有讀取的可重復能力就要考慮以這種方式來編寫事務以獲得資源的獨占鎖定然後再去讀數據例如如果一個事務需要檢索出titles表中所有書的平均價格並保證在update被應用前結果不會改變優化器就會分配一個獨占的表鎖定考慮如下的SQL代碼
　　

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22085.html