sql server中意圖鎖定的作用資料庫弱一致性四個隔離等級

意圖鎖定

資料庫引擎使用意圖鎖定來保護鎖階層的底層資源，以防止其他事務對自己鎖住的資源造成傷害，提高鎖衝突檢測效能。例如，當讀取表裡的頁面時，在請求頁共用鎖定（S鎖）之前，事務在表級請求共用意圖鎖定。這樣可以防止其他事務隨後在表上擷取獨佔鎖定（X鎖），修改整個表格。意圖鎖定可以提高效能，因為資料庫引擎僅在表級檢查意圖鎖定，確定事務是否能安全地擷取該表上的鎖，而不需要檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

如何理解上面這句話的意思呢？我們以一個實際例子來說明。

假如有一張Student，其中包含1000條資料，測試資料如下：

create table Student(    id int,    name char(30),    constraint pk_id primary key(id))--3.插入1000條記錄SET NOCOUNT ON;GODECLARE @i int;SET @i = 1;WHILE @i <= 1000 BEGIN  INSERT INTO Student values(@i,'zhangsan'+cast(@i as char))  SET @i = @i + 1; END;GO

因為設定了id列為主鍵，那麼sql server會自動為其添加叢集索引。假如我們使用如下語句更新表中的資料，

begin tran UPDATE Student SET name ='zhangsan' WHERE id=1000;

然後再查詢當前的鎖狀態，執行如下TSQL

--查看所狀態SELECT request_session_id, resource_type, resource_associated_entity_id,    request_status, request_mode, resource_description    FROM sys.dm_tran_locks

其查詢結果如所示：

我們可以看到在Object和Page上面加了IX鎖，而在Key上面加了X鎖，這三種類型的鎖層次結果如所示：

那麼在表上加一個意向排它鎖(IX)有什麼用呢？假如此時有另外一個事務要求對整張表加S鎖，它需要判定能夠對這張表加這個S鎖。

1. 如果不使用意圖鎖定的話，那麼得遍曆Key查看是否有與S鎖衝突的鎖，而我們上面加鎖的那一條記錄剛好是最後一條，那麼就得遍曆所有資料。現在只有1000條資料還好，資料量不大，如果是上千萬或者過億的話，那麼消耗會非常大。
2. 如果使用意圖鎖定的話，我們就不需要遍曆資料，我們發現Key上面有X鎖，那麼會在表上面加一個IX鎖，而IX鎖與S鎖衝突，因此加S鎖失敗，這樣很快就得到了結果。

鎖相容性控制多個事務能否同時擷取同一資源上的鎖。如果資源已被另一事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。如果請求鎖的模式與現有鎖的模式不相容，則請求新鎖的事務將被迫進入等待狀態，阻塞也就隨之產生。例如，如果一個事務申請了在某個資源上的獨佔鎖定（X鎖），則在它釋放獨佔鎖定（X鎖）之前，其他事務均無法擷取該資源的任何類型（共用、更新或排他）的鎖。另一種情況是，如果一個事務已經獲得了某個資源上的共用鎖定（S鎖），則即使第一個事務尚未完成，其他事務也可以擷取該項的共用鎖定或更新鎖定（U鎖）。但是，在第一個事務釋放共用鎖定之前，其他事務無法擷取獨佔鎖定。

表9-3顯示了最常見的鎖模式的相容性。

表9-3  最常見的鎖模式相容性

	現有授予模式
請求模式	IS	S	U	IX	SIX	X
意圖共用(IS) [WX2]	是	是	是	是	是	否
共用(S)	是	是	是	否	否	否
更新(U)	是	是	否	否	否	否
意向排他(IX)	是	否	否	是	否	否
意向排他共用(SIX)	是	否	否	否	否	否
排他(X)	否	否	否	否	否	否

鎖的模式和相容性是SQL Server預先定義好的，沒有任何參數或配置能夠去修改它們。但是可以通過隔離等級來控制申請鎖和釋放鎖的時機，四個隔離等級中申請與釋放S鎖時機可以參考：資料庫弱一致性四個隔離等級。但是申請鎖的粒度，是資料庫設計能夠影響的。如果應用申請的鎖粒度都比較小，產生阻塞的幾率就會比較小。如果一個串連會經常申請頁面級、表級，甚至是資料庫一級的鎖資源，程式產生阻塞的可能性就會很大。