數據庫的鎖
鎖是數據庫中的一個非常重要的概念它主要用於多用戶環境下保證數據庫完整性和一致性 我們知道多個用戶能夠同時操縱同一個數據庫中的數據會發生數據不一致現象即如果沒有鎖定且多個用戶同時訪問一個數據庫則當他們的事務同時使用相同的數據時可能會發生問題這些問題包括丟失更新髒讀不可重復讀和幻覺讀
當兩個或多個事務選擇同一行然後基於最初選定的值更新該行時會發生丟失更新問題每個事務都不知道其它事務的存在最後的更新將重寫由其它事務所做的更新這將導致數據丟失例如兩個編輯人員制作了同一文檔的電子復本每個編輯人員獨立地更改其復本然後保存更改後的復本這樣就覆蓋了原始文檔最後保存其更改復本的編輯人員覆蓋了第一個編輯人員所做的更改如果在第一個編輯人員完成之後第二個編輯人員才能進行更改則可以避免該問題
髒讀就是指當一個事務正在訪問數據並且對數據進行了修改而這種修改還沒有提交到數據庫中這時另外一個事務也訪問這個數據然後使用了這個數據因為這個數據是還沒有提交的數據那麼另外一個事務讀到的這個數據是髒數據依據髒數據所做的操作可能是不正確的例如一個編輯人員正在更改電子文檔在更改過程中另一個編輯人員復制了該文檔(該復本包含到目前為止所做的全部更改)並將其分發給預期的用戶此後第一個編輯人員認為目前所做的更改是錯誤的於是刪除了所做的編輯並保存了文檔分發給用戶的文檔包含不再存在的編輯內容並且這些編輯內容應認為從未存在過如果在第一個編輯人員確定最終更改前任何人都不能讀取更改的文檔則可以避免該問題
不可重復讀是指在一個事務內多次讀同一數據在這個事務還沒有結束時另外一個事務也訪問該同一數據那麼在第一個事務中的兩次讀數據之間由於第二個事務的修改那麼第一個事務兩次讀到的的數據可能是不一樣的這樣就發生了在一個事務內兩次讀到的數據是不一樣的因此稱為是不可重復讀例如一個編輯人員兩次讀取同一文檔但在兩次讀取之間作者重寫了該文檔當編輯人員第二次讀取文檔時文檔已更改原始讀取不可重復如果只有在作者全部完成編寫後編輯人員才可以讀取文檔則可以避免該問題
幻覺讀是指當事務不是獨立執行時發生的一種現象例如第一個事務對一個表中的數據進行了修改這種修改涉及到表中的全部數據行同時第二個事務也修改這個表中的數據這種修改是向表中插入一行新數據那麼以後就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行就好象發生了幻覺一樣例如一個編輯人員更改作者提交的文檔但當生產部門將其更改內容合並到該文檔的主復本時發現作者已將未編輯的新材料添加到該文檔中如果在編輯人員和生產部門完成對原始文檔的處理之前任何人都不能將新材料添加到文檔中則可以避免該問題
所以處理多用戶並發訪問的方法是加鎖鎖是防止其他事務訪問指定的資源控制實現並發控制的一種主要手段當一個用戶鎖住數據庫中的某個對象時其他用戶就不能再訪問該對象加鎖對並發訪問的影響體現在鎖的粒度上為了控制鎖定的資源應該首先了解系統的空間管理在SQL Server 系統中最小的空間管理單位是頁一個頁有K所有的數據日志索引都存放在頁上另外使用頁有一個限制這就是表中的一行數據必須在同一個頁上不能跨頁頁上面的空間管理單位是盤區一個盤區是個連續的頁表和索引的最小占用單位是盤區數據庫是由一個或者多個表或者索引組成即是由多個盤區組成放在一個表上的鎖限制對整個表的並發訪問;放在盤區上的鎖限制了對整個盤區的訪問;放在數據頁上的鎖限制了對整個數據頁的訪問;放在行上的鎖只限制對該行的並發訪問
SQL Server 的鎖機制
SQL Server 具有多粒度鎖定允許一個事務鎖定不同類型的的資源為了使鎖定的成本減至最少SQL Server 自動將資源鎖定在適合任務的級別鎖定在較小的粒度(例如行)可以增加並發但需要較大的開銷因為如果鎖定了許多行則需要控制更多的鎖鎖定在較大的粒度(例如表)就並發而言是相當昂貴的因為鎖定整個表限制了其它事務對表中任意部分進行訪問但要求的開銷較低因為需要維護的鎖較少SQL Server 可以鎖定行頁擴展盤區表庫等資源
行是可以鎖定的最小空間 行級鎖占用的數據資源最少所以在事務的處理過程中允許其他事務繼續操縱同一個表或者同一個頁的其他數據大大降低了其他事務等待處理的時間提高了系統的並發性
頁級鎖是指在事務的操縱過程中無論事務處理數據的多少每一次都鎖定一頁在這個頁上的數據不能被其他事務操縱在SQL Server 以前使用的是頁級鎖頁級鎖鎖定的資源比行級鎖鎖定的數據資源多在頁級鎖中即使是一個事務只操縱頁上的一行數據那麼該頁上的其他數據行也不能被其他事務使用因此當使用頁級鎖時會出現數據的浪費現象也就是說在同一個頁上會出現數據被占用卻沒有使用的現象在這種現象中數據的浪費最多不超過一個頁上的數據行
表級鎖也是一個非常重要的鎖表級鎖是指事務在操縱某一個表的數據時鎖定了這個數據所在的整個表其他事務不能訪問該表中的其他數據當事務處理的數據量比較大時一般使用表級鎖表級鎖的特點是使用比較少的系統資源但是卻占用比較多的數據資源與行級鎖和頁級鎖相比表級鎖占用的系統資源例如內存比較少但是占用的數據資源卻是最大在表級鎖時有可能出現數據的大量浪費現象因為表級鎖鎖定整個表那麼其他的事務都不能操縱表中的其他數據
盤區鎖是一種特殊類型的鎖只能用在一些特殊的情況下簇級鎖就是指事務占用一個盤區這個盤區不能同時被其他事務占用例如在創建數據庫和創建表時系統分配物理空間時使用這種類型的鎖系統是按照盤區分配空間的當系統分配空間時使用盤區鎖防止其他事務同時使用同一個盤區當系統完成分配空間之後就不再使用這種類型的盤區鎖特別是當涉及到對數據操作的事務時不使用盤區鎖
數據庫級鎖是指鎖定整個數據庫防止任何用戶或者事務對鎖定的數據庫進行訪問數據庫級鎖是一種非常特殊的鎖它只是用於數據庫的恢復操作過程中這種等級的鎖是一種最高等級的鎖因為它控制整個數據庫的操作只要對數據庫進行恢復操作那麼就需要設置數據庫為單用戶模式這樣系統就能防止其他用戶對該數據庫進行各種操作
行級鎖是一種最優鎖因為行級鎖不可能出現數據既被占用又沒有使用的浪費現象但是如果用戶事務中頻繁對某個表中的多條記錄操作將導致對該表的許多記錄行都加上了行級鎖數據庫系統中鎖的數目會急劇增加這樣就加重了系統負荷影響系統性能因此在SQL Server中還支持鎖升級(lock escalation)所謂鎖升級是指調整鎖的粒度將多個低粒度的鎖替換成少數的更高粒度的鎖以此來降低系統負荷在SQL Server中當一個事務中的鎖較多達到鎖升級門限時系統自動將行級鎖和頁面鎖升級為表級鎖特別值得注意的是在SQL Server中鎖的升級門限以及鎖升級是由系統自動來確定的不需要用戶設置
SQL Server 的鎖模式
在SQL Server數據庫中加鎖時除了可以對不同的資源加鎖還可以使用不同程度的加鎖方式即鎖有多種模式SQL Server中鎖模式包括
共享鎖 SQL Server中共享鎖用於所有的只讀數據操作共享鎖是非獨占的允許多個並發事務讀取其鎖定的資源默認情況下數據被讀取後SQL Server立即釋放共享鎖例如執行查詢“SELECT * FROM AUTHORS”時首先鎖定第一頁讀取之後釋放對第一頁的鎖定然後鎖定第二頁這樣就允許在讀操作過程中修改未被鎖定的第一頁但是事務隔離級別連接選項設置和SELECT語句中的鎖定設置都可以改變SQL Server的這種默認設置例如“ SELECT * FROM AUTHORS HOLDLOCK”就要求在整個查詢過程中保持對表的鎖定直到查詢完成才釋放鎖定
更新鎖 更新鎖在修改操作的初始化階段用來鎖定可能要被修改的資源這樣可以避免使用共享鎖造成的死鎖現象因為使用共享鎖時修改數據的操作分為兩步首先獲得一個共享鎖讀取數據然後將共享鎖升級為排它鎖然後再執行修改操作這樣如果同時有兩個或多個事務同時對一個事務申請了共享鎖在修改數據的時候這些事務都要將共享鎖升級為排它鎖這時這些事務都不會釋放共享鎖而是一直等待對方釋放這樣就造成了死鎖如果一個數據在修改前直接申請更新鎖在數據修改的時候再升級為排它鎖就可以避免死鎖
排它鎖 排它鎖是為修改數據而保留的它所鎖定的資源其他事務不能讀取也不能修改
結構鎖 執行表的數據定義語言 (DDL) 操作(例如添加列或除去表)時使用架構修改 (SchM) 鎖當編譯查詢時使用架構穩定性 (SchS) 鎖架構穩定性 (SchS) 鎖不阻塞任何事務鎖包括排它鎖因此在編譯查詢時其它事務(包括在表上有排它鎖的事務)都能繼續運行但不能在表上執行 DDL 操作
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22354.html
