Sql Server 鎖機制

 轉自：http://blog.csdn.net/missmecn/archive/2008/10/06/3019798.aspx 相關文章:  

• mysql資料庫鎖
• ORACLE裡幾種鎖模式

推薦圈子: 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代碼

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

begin tranupdate titles set title_idid＝title_id  where 1＝2if (selectavg(price)fromtitles)>$15beginupdate titles set price＝price*1.10where price<(select avg(price)from titles)endcommit 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代碼

1. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
2. SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM table1 ROWLOCK WHERE A = 'a1'

2 鎖定資料庫的一個表
select col1 from 表 (tablockx) where 1=1 ;
加鎖後其它人不可操作，直到加鎖使用者解鎖，用commit或rollback解鎖
3.執行個體
建表 Java代碼

1. create table table1(A varchar(50)  not  null, B varchar(50) ,C varchar(50));   
2. create table table2(D varchar(50),E varchar(50))   
3. insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);   
4. insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);   
5. insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);   
6. insert table2 (D,E) values(‘d1’,’e1’);   
7. 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代碼

1. -- A事務先更新table1表，在更新時，對其他事務進行排他   
2. begin tran   
3. update table1 set A='aa' where B='b2'；   
4. waitfor delay '00:00:30'； --等待30秒   
5. commit tran   
6. -- A事務先更新table2表   
7. begin tran   
8. select * from table1 where B='b2';   
9. commit tran  

-- A事務先更新table1表，在更新時，對其他事務進行排他begin tranupdate table1 set A='aa' where B='b2'；waitfor delay '00:00:30'； --等待30秒commit tran-- A事務先更新table2表begin transelect * from table1 where B='b2';commit tran

若同時執行上述兩個事務，則select查詢必須等待update執行完畢才能執行即要等待30秒
2）共用鎖定 Java代碼

1. -- A事務先查詢table1表，在查詢時，加共用鎖定，防止其他事務對該表進行修改操作   
2. begin tran   
3. select * from table1 holdlock where B='b2' ;   
4.  -holdlock人為加鎖   
5. waitfor delay '00:00:30';--等待30秒   
6. commit tran   
7. -- A事務先查詢table1表，後更改table1表   
8. begin tran   
9. select A,C from table1 where B='b2';   
10. update table1 set A='aa' where B='b2';   
11. commit tran  

-- A事務先查詢table1表，在查詢時，加共用鎖定，防止其他事務對該表進行修改操作begin transelect * from table1 holdlock where B='b2' ;-holdlock人為加鎖waitfor delay '00:00:30';--等待30秒commit tran-- A事務先查詢table1表，後更改table1表begin transelect A,C from table1 where B='b2';update table1 set A='aa' where B='b2';commit tran

若並發執行上述兩個事務，則B事務中的select查詢可以執行,而update必須等待第一個事務釋放共用鎖定轉為排它鎖後才能執行即要等待30秒
3）死結 Java代碼

1. -- A事務先更新table1表，然後延時30秒，再更新table2表；   
2. begin tran   
3. update table1 set A='aa' where B='b2';   
4. --這將在 Table1 中產生排他行鎖，直到事務完成後才會釋放該鎖。   
5. waitfor delay '00:00:30';   
6. --進入延時   
7. update table2 set D='d5' where E='e1' ;   
8. commit tran   
9. -- B事務先更新table2表，然後延時10秒，再更新table1表；   
10. begin tran   
11. update table2 set D='d5' where E='e1';   
12. --這將在 Table2 中產生排他行鎖，直到事務完成後才會釋放該鎖   
13. waitfor delay '00:00:10'  
14. --進入延時   
15. update table1 set A='aa' where B='b2' ;   
16. commit tran  

-- A事務先更新table1表，然後延時30秒，再更新table2表；begin tranupdate 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 tranupdate 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： 為應用程式資源解鎖