資料庫鎖概念介紹

資料庫和作業系統一樣，是一個多使用者使用的共用資源。當多個使用者並發地存取資料 時，在資料庫中就會產生多個事務同時存取同一資料的情況。若對並行作業不加控制就可能會讀取和儲存不正確的資料，破壞資料庫的一致性。加鎖是實現資料庫並 發控制的一個非常重要的技術。在實際應用中經常會遇到的與鎖相關的異常情況，當兩個事務需要一組有衝突的鎖，而不能將事務繼續下去的話，就會出現死結，嚴 重影響應用的正常執行。 
在資料庫中有兩種基本的鎖類型：排它鎖（Exclusive Locks，即X鎖）和共用鎖定（Share Locks，即S鎖）。當資料對象被加上排它鎖時，其他的事務不能對它讀取和修改。加了共用鎖定的資料對象可以被其他事務讀取，但不能修改。資料庫利用這兩 種基本的鎖類型來對資料庫的事務進行並發控制。 
死結的第一種情況 
一個使用者A 訪問表A(鎖住了表A),然後又訪問表B；另一個使用者B 訪問表B(鎖住了表B)，然後企圖訪問表A；這時使用者A由於使用者B已經鎖住表B，它必須等待使用者B釋放表B才能繼續，同樣使用者B要等使用者A釋放表A才能繼續，這就死結就產生了。 
解決方案： 
這種死結比較常見，是由於程式的BUG產生的，除了調整的程式的邏輯沒有其它的辦法。仔細剖析器的邏輯，對於資料庫的多表操作時，盡量按照相同的順序進 行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A後B的順序處理， 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源。 
死結的第二種情況 
使用者A查詢一條紀錄，然後修改該條紀錄；這時使用者B修改該條紀錄，這時使用者A的事務裡鎖的性質由查詢的共用鎖定企圖上升到獨佔鎖，而使用者B裡的獨佔鎖由於A 有共用鎖定存在所以必須等A釋放掉共用鎖定，而A由於B的獨佔鎖而無法上升的獨佔鎖也就不可能釋放共用鎖定，於是出現了死結。這種死結比較隱蔽，但在稍大點的項 目中經常發生。如在某項目中，頁面上的按鈕點擊後，沒有使按鈕立刻失效，使得使用者會多次快速點擊同一按鈕，這樣同一段代碼對資料庫同一條記錄進行多次操 作，很容易就出現這種死結的情況。 
解決方案： 
1、對於按鈕等控制項，點擊後使其立刻失效，不讓使用者重複點擊，避免對同時對同一條記錄操作。 
2、使用樂觀鎖進行控制。樂觀鎖大多是基於資料版本（Version）記錄機制實現。即為資料增加一個版本標識，在基於資料庫表的版本解決方案中，一般是 通過為資料庫表增加一個“version”欄位來實現。讀取出資料時，將此版本號碼一同讀出，之後更新時，對此版本號碼加一。此時，將提交資料的版本資料與數 據庫表對應記錄的目前的版本資訊進行比對，如果提交的資料版本號碼大於資料庫表目前的版本號，則予以更新，否則認為是到期資料。樂觀鎖機制避免了長事務中的資料 庫加鎖開銷（使用者A和使用者B操作過程中，都沒有對資料庫資料加鎖），大大提升了大並發量下的系統整體效能表現。Hibernate
在其資料訪問引擎中內建了樂觀鎖實現。需要注意的是，由於樂觀鎖機制是在我們的系統中實現，來自外部系統的使用者更新操作不受我們系統的控制，因此可能會造 成髒資料被更新到資料庫中。 
3、使用悲觀鎖進行控制。悲觀鎖大多數情況下依靠資料庫的鎖機制實現，如Oracle的Select … for update語句，以保證操作最大程度的獨佔性。但隨之而來的就是資料庫效能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。如一個金融系統， 當某個操作員讀取使用者的資料，並在讀出的使用者資料的基礎上進行修改時（如更改使用者賬戶餘額），如果採用悲觀鎖機制，也就意味著整個操作過程中（從操作員讀 出資料、開始修改直至提交修改結果的全過程，甚至還包括操作員中途去煮咖啡的時間），資料庫記錄始終處於加鎖狀態，可以想見，如果面對成百上千個並發，這
樣的情況將導致災難性的後果。所以，採用悲觀鎖進行控制時一定要考慮清楚。 
死結的第三種情況 
如果在事務中執行了一條不滿足條件的update語句，則執行全表掃描，把行級鎖上升為表級鎖，多個這樣的事務執行後，就很容易產生死結和阻塞。類似的情 況還有當表中的資料量非常龐大而索引建的過少或不合適的時候，使得經常發生全表掃描，最終應用系統會越來越慢，最終發生阻塞或死結。 
解決方案： 
SQL語句中不要使用太複雜的關聯多表的查詢；使用“執行計畫”對SQL語句進行分析，對於有全表掃描的SQL語句，建立相應的索引進行最佳化。 
5．小結 
總體上來說，產生記憶體溢出與鎖表都是由於代碼寫的不好造成的，因此提高代碼的品質是最根本的解決辦法。有的人認為先把功能實現，有BUG時再在測試階段進 行修正，這種想法是錯誤的。正如一件產品的品質是在生產製造的過程中決定的，而不是品質檢測時決定的，軟體的品質在設計與編碼階段就已經決定了，測試只是 對軟體品質的一個驗證，因為測試不可能找出軟體中所有的BUG。

相關文章: 
ORACLE裡幾種鎖模式 
遇到一個多事務並發的問題 
mysql資料庫鎖 

推薦圈子: Pipboy 
更多相關推薦 對鎖機制的研究要具備兩個條件： 
1．資料量大 
2．多個使用者同時並發 
如果缺少這兩個條件，資料庫不容易產生死結問題。研究起來可能會事倍功半。如果這兩個條件都有，但你還是按資料庫預設設定來處理資料，則會帶來很多的問題，比如： 
1）丟失更新 
A,B兩個使用者讀同一資料並進行修改,其中一個使用者的修改結果破壞了另一個修改的結果 
2）髒讀 
A使用者修改了資料時,B使用者也在讀該資料,但A使用者因為某些原因取消了對資料的修改,資料恢複原值,此時B得到的資料就與資料庫內的資料產生了不一致 
3）不可重複讀取 
B使用者讀出該資料並修改,同時，A使用者也在讀取資料，此時A使用者再讀取資料時發現前後兩次的值不一致 
SQL SERVER 作為多使用者資料庫系統，以事務為單位，使用鎖來實現並發控制。SQLSERVER使用“鎖”確保事務完整性和資料一致性。 

一、鎖的概念 
鎖（LOCKING）是最常用的並發控制機構。是防止其他事務訪問指定的資源控制、實現並發控制的一種主要手段。鎖是事務對某個資料庫中的資源（如表和記 錄）存取前，先向系統提出請求，封鎖該資源，事務獲得鎖後，即取得對資料的控制權，在事務釋放它的鎖之前，其他事務不能更新此資料。當事務撤消後，釋放被 鎖定資源。 
當一個使用者鎖住資料庫中的某個對象時，其他使用者就不能再訪問該對象 

二、鎖的粒度 
SQL Server 2000 具有多粒度鎖定，允許一個事務鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQL Server 自動將資源鎖定在適合任務的層級。鎖定在較小的粒度（例如行）可以增加並發但需要較大的開銷，因為如果鎖定了許多行，則需要控制更多的鎖。鎖定在較大的粒 度（例如表）就並發而言是相當昂貴的，因為鎖定整個表限制了其它事務對錶中任意部分進行訪問，但要求的開銷較低，因為需要維護的鎖較少。SQL Server 可以鎖定行、頁、擴充盤區、表、庫等資源。 

資源 層級 描述 
RID 行鎖 表中的單個行 
Key 行級鎖 索引中的行 
Page 頁級鎖 一個資料頁或者索引頁 
Extent 頁級鎖 一組資料頁或者索引頁 
Table 表級鎖 整個表 
Database 資料庫級鎖 整個資料庫 

選擇多大的粒度，根據對資料的操作而定。如果是更新表中所有的行，則用表級鎖;如果是更新表中的某一行，則用行級鎖。 
行級鎖是一種最優鎖，因為行級鎖不可能出現資料既被佔用又沒有使用的浪費現象。但是，如果使用者事務中頻繁對某個表中的多條記錄操作，將導致對該表的許多記 錄行都加上了行級鎖，資料庫系統中鎖的數目會急劇增加，這樣就加重了系統負荷，影響系統效能。因此，在SQL Server中，還支援鎖定擴大(lock escalation)。 
所謂鎖定擴大是指調整鎖的粒度，將多個低粒度的鎖替換成少數的更高粒度的鎖，以此來降低系統負荷。在SQL Server中當一個事務中的鎖較多，達到鎖定擴大門限時，系統自動將行級鎖和頁面鎖定擴大為表級鎖。 
特別值得注意的是，在SQL Server中，鎖的升級門限以及鎖定擴大是由系統自動來確定的，不需要使用者佈建。 

三、鎖的模式 
鎖模式以及描述表 

鎖模式 描述 
共用（S） 用於不更改或不更新資料（唯讀操作），如SELECT語句 
更新（U） 用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死結。 
排它（X） 用於資料修改操作，例如 INSERT、UPDATE或DELETE。確保不會同時對同一資源進行多重更新 
意向 當 Microsoft SQL Server 資料庫引擎擷取低層級的鎖時，它還將在包含更低層級對象的對象上放置意圖鎖定.例如： 當鎖定行或索引鍵範圍時，資料庫引擎將在包含行或鍵的頁上放置意圖鎖定。當鎖定頁時，資料庫引擎將在包含頁的更進階別的對象上放置意圖鎖定。 
意圖鎖定的類型為：意圖共用（IS）、意向排它（IX）以及意向排它共用（SIX） 
架構 在執行依賴於表架構的操作時使用。架構鎖的類型為：架構修改（Sch-M）和架構穩定（Sch-S） 
大容量更新（BU） 向表中大量複製資料並指定了TABLOCK提示時使用 

四 SQL Server 中鎖的設定 
1 處理死結和設定死結優先順序 
死結就是多個使用者申請不同封鎖,由於申請者均擁有一部分封鎖權而又等待其他使用者擁有的部分封鎖而引起的無休止的等待 
可以使用SET DEADLOCK_PRIORITY控制在發生死結情況時會話的反應方式。 
Syntax: 
SET DEADLOCK_PRIORITY { LOW | NORMAL} 
其中LOW說明該進程會話的優先順序較低，在出現死結時，可以首先中斷該進程的事務。 
2 處理逾時和設定鎖逾時期間。 
@@LOCK_TIMEOUT 返回當前會話的當前鎖逾時設定，單位為毫秒 
SET LOCK_TIMEOUT 設定允許應用程式設定語句等待阻塞資源的最長時間。當語句等待的時間大於 LOCK_TIMEOUT 設定時，系統將自動取消阻塞的語句，並給應用程式返回"已超過了鎖請求逾時時段"的 1222 號錯誤資訊 
樣本 
1）將鎖逾時期限設定為 1,800 毫秒。 
SET LOCK_TIMEOUT 1800 
2) 配置索引的鎖定粒度 
可以使用 sp_indexoption 系統預存程序來設定用於索引的鎖定粒度 
3）設定交易隔離等級 
SET TRANSACTION ISOLATION LEVEL 

五 查看鎖的資訊 
1 執行 EXEC SP_LOCK 報告有關鎖的資訊 
2 查詢分析器中按Ctrl+2可以看到鎖的資訊 

六、奇怪的sql語句 
Java代碼 
begin tran 
update titles set title_idid＝title_id   where 1＝2  
if (selectavg(price)fromtitles)>$15  
begin 
update titles set price＝price*1.10  
where price<(select avg(price)from titles) 
end 
commit tran  

begin tran
update titles set title_idid＝title_id   where 1＝2
if (selectavg(price)fromtitles)>$15
begin
update titles set price＝price*1.10
where price<(select avg(price)from titles)
end
commit tran

update titles set title_idid＝title_id   where 1＝2，這個條件是永遠也不會成立的，如此寫的含義是什麼呢？ 
這裡的where子句看起來很奇怪，儘管計算出的結果總是false。當最佳化器處理此查詢時，因為它找不到任何有效SARG，它的查詢規劃就會強制使用 一個獨佔鎖定來進行表掃描。此事務執行時，where子句立即得到一個false值，於是不會執行實際上的掃描，但此進程仍得到了一個獨佔的表鎖定。 
因為此進程現在已有一個獨佔的表鎖，所以可以保證沒有其他事務會修改任何資料行，能進行重複讀，且避免了由於holdlock所引起的潛在性死結。 
但是，在使用表鎖定來儘可能地減少死結的同時，也增加了對錶鎖定的爭用。因此，在實現這種方法之前，你需要權衡一下：避免死結是否比允許並發地對錶進行訪問更重要。 
所以，在這個事務中，沒有其他進程修改表中任何行的price。 

七 如何避免死結 
1 使用事務時，盡量縮短事務的邏輯處理過程，及早提交或復原事務； 
2 設定死結逾時參數為合理範圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛； 
3 所有的SP都要有錯誤處理（通過@error） 
4 一般不要修改SQL SERVER事務的預設層級。不推薦強行加鎖 
5 最佳化程式，檢查並避免死結現象出現； 
1）合理安排表訪問順序 
2）在事務中盡量避免使用者幹預，盡量使一個交易處理的任務少些。 
3）採用髒讀技術。髒讀由於不對被訪問的表加鎖，而避免了鎖衝突。在客戶機/伺服器應用環境中，有些事務往往不允許讀髒資料，但在特定的條件下，我們可以用髒讀。 
4）資料訪問時域離散法。資料訪問時域離散法是指在客戶機/伺服器結構中，採取各種控制手段控制對資料庫或資料庫中的對象訪問時間段。主要通過以下方式實 現: 合理安排後台事務的執行時間，採用工作流程對後台事務進行統一管理。工作流程在管理工作時，一方面限制同一類任務的線程數（往往限制為1個），防止資源過多佔 用; 另一方面合理安排不同任務執行時序、時間，盡量避免多個背景工作同時執行，另外，避免在前台交易高峰時間運行背景工作 
5）資料存放區空間離散法。資料存放區空間離散法是指採取各種手段，將邏輯上在一個表中的資料分散到若干離散的空間上去，以便改善對錶的訪問效能。主要通過以下方法實現: 第一，將大表按行或列分解為若干小表; 第二，按不同的使用者群分解。 
6）使用儘可能低的隔離性層級。隔離性層級是指為保證資料庫資料的完整性和一致性而使多使用者事務隔離的程度，SQL92定義了4種隔離性層級：未提交讀、 提交讀、可重複讀和可串列。如果選擇過高的隔離性層級，如可串列，雖然系統可以因實現更好隔離性而更大程度上保證資料的完整性和一致性，但各事務間衝突而 死結的機會大大增加，大大影響了系統效能。 
7）使用Bound Connections。Bound connections 允許兩個或多個事務串連共用事務和鎖，而且任何一個事務串連要申請鎖如同另外一個事務要申請鎖一樣，因此可以允許這些事務共用資料而不會有加鎖的衝突。 
8）考慮使用樂觀鎖定或使事務首先獲得一個獨佔鎖定。  

八如何對行、 表、資料庫加鎖 
1 如何鎖一個表的某一行 
Java代碼 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'
2 鎖定資料庫的一個表 
select col1 from 表 (tablockx) where 1=1 ; 
加鎖後其它人不可操作，直到加鎖使用者解鎖，用commit或rollback解鎖 
3.執行個體 
建表 
Java代碼 
create table table1(A varchar(50)   not   null, B varchar(50) ,C varchar(50)); 
create table table2(D varchar(50),E varchar(50)) 
insert table1 (A,B,C) values(‘a1’,’b1’,’c1’); 
insert table1 (A,B,C) values(‘a2’,’b2’,’c2’); 
insert table1 (A,B,C) values(‘a3’,’b3’,’c3’); 
insert table2 (D,E) values(‘d1’,’e1’); 
insert table2 (D,E) values(‘d2’,’e2’);  

create table table1(A varchar(50)   not   null, B varchar(50) ,C varchar(50));
create table table2(D varchar(50),E varchar(50))
insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);
insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);
insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);
insert table2 (D,E) values(‘d1’,’e1’);
insert table2 (D,E) values(‘d2’,’e2’);
1）排它鎖 
Java代碼 
-- A事務先更新table1表，在更新時，對其他事務進行排他 
begin tran 
update table1 set A='aa' where B='b2'； 
waitfor delay '00:00:30'； --等待30秒 
commit tran 
-- A事務先更新table2表 
begin tran 
select * from table1 where B='b2'; 
commit tran  

-- A事務先更新table1表，在更新時，對其他事務進行排他
begin tran
update table1 set A='aa' where B='b2'；
waitfor delay '00:00:30'； --等待30秒
commit tran
-- A事務先更新table2表
begin tran
select * from table1 where B='b2';
commit tran若同時執行上述兩個事務，則select查詢必須等待update執行完畢才能執行即要等待30秒 
2）共用鎖定 
Java代碼 
-- A事務先查詢table1表，在查詢時，加共用鎖定，防止其他事務對該表進行修改操作 
begin tran 
select * from table1 holdlock where B='b2' ; 
-holdlock人為加鎖 
waitfor delay '00:00:30';--等待30秒 
commit tran 
-- A事務先查詢table1表，後更改table1表 
begin tran 
select A,C from table1 where B='b2'; 
update table1 set A='aa' where B='b2'; 
commit tran  

-- A事務先查詢table1表，在查詢時，加共用鎖定，防止其他事務對該表進行修改操作
begin tran
select * from table1 holdlock where B='b2' ;
-holdlock人為加鎖
waitfor delay '00:00:30';--等待30秒
commit tran
-- A事務先查詢table1表，後更改table1表
begin tran
select A,C from table1 where B='b2';
update table1 set A='aa' where B='b2';
commit tran若並發執行上述兩個事務，則B事務中的select查詢可以執行,而update必須等待第一個事務釋放共用鎖定轉為排它鎖後才能執行即要等待30秒 
3）死結 
Java代碼 
-- A事務先更新table1表，然後延時30秒，再更新table2表； 
begin tran 
update table1 set A='aa' where B='b2'; 
--這將在 Table1 中產生排他行鎖，直到事務完成後才會釋放該鎖。 
waitfor delay '00:00:30'; 
--進入延時 
update table2 set D='d5' where E='e1' ; 
commit tran 
-- B事務先更新table2表，然後延時10秒，再更新table1表； 
begin tran 
update table2 set D='d5' where E='e1'; 
--這將在 Table2 中產生排他行鎖，直到事務完成後才會釋放該鎖 
waitfor delay '00:00:10'  
--進入延時 
update table1 set A='aa' where B='b2' ; 
commit tran  

-- A事務先更新table1表，然後延時30秒，再更新table2表；
begin tran
update table1 set A='aa' where B='b2';
--這將在 Table1 中產生排他行鎖，直到事務完成後才會釋放該鎖。
waitfor delay '00:00:30';
--進入延時
update table2 set D='d5' where E='e1' ;
commit tran
-- B事務先更新table2表，然後延時10秒，再更新table1表；
begin tran
update table2 set D='d5' where E='e1';
--這將在 Table2 中產生排他行鎖，直到事務完成後才會釋放該鎖
waitfor delay '00:00:10'
--進入延時
update table1 set A='aa' where B='b2' ;
commit tran若並發執行上述兩個事務，A,B兩事務都要等待對方釋放獨佔鎖定，這樣便形成了死結。 

九、sqlserver提供的表級鎖 
sqlserver所指定的表級鎖定提示有如下幾種 
1． HOLDLOCK: 在該表上保持共用鎖定，直到整個事務結束，而不是在語句執行完立即釋放所添加的鎖。  
2． NOLOCK：不添加共用鎖定和排它鎖，當這個選項生效後，可能讀到未提交讀的資料或“髒資料”，這個選項僅僅應用於SELECT語句。 
3． PAGLOCK：指定添加頁鎖（否則通常可能添加表鎖） 
4． READCOMMITTED用與運行在提交讀隔離等級的事務相同的鎖語義執行掃描。預設情況下，SQL Server 2000 在此隔離等級上操作。 
5． READPAST: 跳過已經加鎖的資料行，這個選項將使事務讀取資料時跳過那些已經被其他事務鎖定的資料行，而不是阻塞直到其他事務釋放鎖，READPAST僅僅應用於READ COMMITTED隔離性層級下事務操作中的SELECT語句操作 
6． READUNCOMMITTED：等同於NOLOCK。 
7． REPEATABLEREAD：設定事務為可重複讀隔離性層級。  
8． ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。 
9． SERIALIZABLE：用與運行在可串列讀隔離等級的事務相同的鎖語義執行掃描。等同於 HOLDLOCK。 
10． TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執行完後釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務結束。 
11． TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務讀或更新這個表的資料，直到這個語句或整個事務結束。 
12． UPDLOCK ：指定在讀表中資料時設定更新鎖定（update lock）而不是設定共用鎖定，該鎖一直保持到這個語句或整個事務結束，使用UPDLOCK的作用是允許使用者先讀取資料（而且不阻塞其他使用者讀資料），並且 保證在後來再更新資料時，這一段時間內這些資料沒有被其他使用者修改 
SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表，但不能更新刪除 
SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除 

十、應用程式鎖 

應用程式鎖就是產生用戶端程式碼的鎖，而不是sql server本身產生的鎖處理應用程式鎖的兩個系統預存程序 
sp_getapplock： 鎖定應用程式資源 
sp_releaseapplock： 為應用程式資源解鎖 
）使用儘可能低的隔離性層級。隔離性層級是指為保證資料庫資料的完整性和一致性而使多使用者事務隔離的程度，SQL92定義了4種隔離性層級：未提交讀、提 交讀、可重複讀和可串列。如果選擇過高的隔離性層級，如可串列，雖然系統可以因實現更好隔離性而更大程度上保證資料的完整性和一致性，但各事務間衝突而死 鎖的機會大大增加，大大影響了系統效能。 
7）使用Bound Connections。Bound connections 允許兩個或多個事務串連共用事務和鎖，而且任何一個事務串連要申請鎖如同另外一個事務要申請鎖一樣，因此可以允許這些事務共用資料而不會有加鎖的衝突。 
8）考慮使用樂觀鎖定或使事務首先獲得一個獨佔鎖定。 

八如何對行、 表、資料庫加鎖 
1 如何鎖一個表的某一行 
Java代碼 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  

如果能有更多的 隔離等級對應鎖的詳細對比說明,就更好了