SqlServer 並發事務（二）：鎖粒度和鎖模式

標籤：des   style   blog   http   io   ar   color   os   使用   

鎖粒度：

資源	格式	說明
DATABASE	不適用	resource_database_id 列中已提供資料庫 ID。
FILE	<file_id>	此資源所表示的檔案 ID。
Object	<object_id>	此資源所表示的物件識別碼。 此對象可以是sys.objects 中列出的任何對象，不僅僅是表。
PAGE	<file_id>:<page_in_file>	HoBt ID。此值與 sys.partitions.hobt_id 相對應。 PAGE 資源並不總是有 HoBt ID，因為 HoBt ID 是可由調用方提供的額外資訊，而有些調用方不能提供該資訊。
KEY	<hash_value>	表示行中由此資源表示的鍵列的雜湊。HoBt ID。此值與 sys.partitions.hobt_id 相對應。
EXTENT	<file_id>:<page_in_files>	表示此資源所表示的區的檔案和頁 ID。 區 ID 與區中的第一頁的頁 ID 相同。
RID	<file_id>:<page_in_file>:<row_on_page>	表示此資源所表示的行的頁 ID 和行 ID。 請注意，如果關聯的物件識別碼 為 99，則此資源表示 IAM 鏈的第一個 IAM 頁上的八個混合頁槽之一。HoBt ID。此值與 sys.partitions.hobt_id 相對應。
APPLICATION	<DbPrincipalId>:<upto 32 characters>:(<hash_value>)	表示用於劃分此應用程式鎖資源範圍的資料庫主體的 ID。 還包含與此應用程式鎖資源相對應的資源字串，最多包含其中的 32 個字元。 在某些情況下，因不再提供完整字串而只能顯示 2 個字元。 只有在恢複過程中重新擷取的應用程式鎖處於資料庫恢複期間才會發生此行為。 雜湊值表示與此應用程式鎖資源相對應的完整資源字串的雜湊。
HOBT	不適用	作為 resource_associated_entity_id 提供的 HoBt ID。此值與 sys.partitions.hobt_id 相對應。
ALLOCATION_UNIT	不適用	作為 resource_associated_entity_id 提供的配置單位 ID。此值與 sys.allocation_units.allocation_unit_id相對應。

鎖粒度查看測試:

CREATE TABLE mytest(idint,name varchar(20),info varchar(20))insert into mytestselect 1,'kk',nullunion allselect 2,'mm',nullcreate nonclustered index ix_mytest_name on mytest(name,id)--create clustered index ix_mytest on mytest(id) 

--事務的鎖資源類型SET TRANSACTION ISOLATION LEVEL READ COMMITTEDbegin tranupdate t set info='kk' from mytest t where ID=1--OBJECT,PAGE,RID(堆),KEY(叢集索引)--update t set info='kk' from mytest t with(rowlock) where ID=1--OBJECT,PAGE,RID(堆),KEY(叢集索引)--update t set info='kk' from mytest t with(paglock) where ID=1--OBJECT,PAGE--update t set info='kk' from mytest t with(tablock) where ID=1--OBJECT--alter table mytest add col int--DATABASE,METADATA(Sch-S),OBJECT(Sch-M),OBJECT,RID(堆),KEY(叢集索引)select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPIDrollback tran

--事務的鎖資源類型SET TRANSACTION ISOLATION LEVEL READ COMMITTEDbegin tranalter index ix_mytest_name on mytest rebuild--幾乎包括所有基本類型select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPIDrollback tran

--所有正在請求的資源資訊select request_session_id,resource_type,resource_description,request_mode,request_status,request_type,request_lifetime,request_owner_type,resource_associated_entity_id,lock_owner_addressfrom sys.dm_tran_locks where resource_database_id=DB_ID() order by request_session_id

--根據sys.dm_tran_locks(resource_associated_entity_id)可確定正在鎖定的是哪個對象或索引--（對象正在訪問則需等待結束）select p.partition_id,p.partition_number,OBJECT_NAME(p.object_id) as table_name,i.name as index_name,i.type_desc,p.rowsfrom sys.partitions p inner join sys.indexes i on p.object_id=i.object_id and p.index_id=i.index_idwhere  281474979397632 IN(p.object_id,p.hobt_id)--即where resource_associated_entity_id IN(p.object_id,p.hobt_id)

摘：

Microsoft SQL Server 資料庫引擎具有多粒度鎖定，允許一個事務鎖定不同類型的資源。為了盡量減少鎖定的開銷，資料庫引擎自動將資源鎖定在適合任務的層級。鎖定在較小的粒度（例如行）可以提高並發度，但開銷較高，因為如果鎖定了許多行，則需要持有更多的鎖。鎖定在較大的粒度（例如表）會降低了並發度，因為鎖定整個表限制了其他事務對錶中任意部分的訪問。但其開銷較低，因為需要維護的鎖較少。

鎖模式：

鎖模式	說明
共用 (S)	用於不更改或不更新資料的讀取操作，如 SELECT語句。
更新 (U)	用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死結。
排他 (X)	用於資料修改操作，例如 INSERT、UPDATE或 DELETE。確保不會同時對同一資源進行多重更新。
意向	用於建立鎖的階層。意圖鎖定包含三種類型：意圖共用 (IS)、意向排他 (IX)和意向排他共用 (SIX)。
架構	在執行依賴於表架構的操作時使用。架構鎖包含兩種類型：架構修改 (Sch-M)和架構穩定性 (Sch-S)。
大容量更新 (BU)	在向表進行大容量資料複製且指定了 TABLOCK 提示時使用。
鍵範圍	當使用可序列化交易隔離等級時保護查詢讀取的行的範圍。確保再次執行查詢時其他事務無法插入符合可序列化事務的查詢的行。

--當前表記錄(只有非叢集索引ix_mytest_name)select * from mytestidnameinfo1kkNULL2mmNULLcreate nonclustered index ix_mytest_name on mytest(name) with(drop_existing=on)

--事務1SET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin transelect * from mytest select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPIDupdate t set info='kk' from mytest t where name='kk'select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPID--waitfor delay '00:30:00'rollback tran

說明:

先執行查詢時，整個表為共用鎖定（S）。接著進行更新操作，在隔離等級serializable中，查詢會將對象升級為獨佔鎖定。而如果其他事務也有共用鎖定，就不能轉為獨佔鎖定，所以資料庫引用了更新鎖定。當一個事務有了更新鎖定，其他事務就不能再申請更新鎖定。這個事務就正常升級為獨佔鎖定進行操作，避免了死結可能（如幾個事務都擁有共用鎖定，都打算進行排他操作，這樣就相互等待成死結）。在共用粒度較大的錶轉變為更新粒度較小的對象時，就會產生共用意向獨佔鎖定（SIX）。只有表中有索引並且需要查詢鍵列（如name=‘kk‘），就會產生關鍵範圍鎖定（RangeS-U），在表共用鎖定轉到鍵鎖過程，對錶個層次的粒度都加了相應的鎖。

如下表格：鎖粒度遞增，鎖模式更低。

粒度	鎖類型
KEY	RangeS-U
KEY	RangeS-U
RID	X
PAGE	IU
PAGE	IX
OBJECT	SIX
DATABASE	S

要檢驗上面的粒度是否存在鎖，先運行上面的【事務1】，等待30分鐘再結束。接著再開啟另一個查詢時段，逐條執行下面的【事務2】。

--事務2SET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin transelect name from mytest where name='kk'--KEY(RangeS-U)--select id from mytest where name='kk'--RID(X)--select name from mytest where name='mm'--PAGE(IU)--select id,name from mytest where name='mm'--PAGE(IX)--select * from mytest --OBJECT(SIX)--create table t(id int)--DATABASE(S)select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPIDrollback tran

上面可以驗證【事務1】在各個層級粒度是否加鎖。

意圖鎖定：

摘：

資料庫引擎使用意圖鎖定來保護共用鎖定（S鎖）或獨佔鎖定（X鎖）放置在鎖階層的底層資源上。意圖鎖定之所以命名為意圖鎖定，是因為在較低層級鎖前可擷取它們，因此會通知意向將鎖放置在較低層級上。

 

意圖鎖定有兩種用途：

防止其他事務以會使較低層級的鎖無效的方式修改較進階別資源。

提高資料庫引擎在較高的粒度層級檢測鎖衝突的效率。

 

例如，在該表的頁或行上請求共用鎖定（S鎖）之前，在表級請求共用意圖鎖定。在表級設定意圖鎖定可防止另一個事務隨後在包含那一頁的表上擷取獨佔鎖定（X鎖）。意圖鎖定可以提高效能，因為資料庫引擎僅在表級檢查意圖鎖定來確定事務是否可以安全地擷取該表上的鎖。而不需要檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意圖鎖定不多說明了，上面是操作也出現過，具體參考文檔吧

大容量更新鎖定查看測試：

大容量更新鎖定是在大容量操作時才出現，一下測試查看

--先建立表select c.name tablename,c.name columnname, o.object_id,c.column_id,o.type,o.type_desc into bulkTestfrom sys.objects o,sys.columns c where 1<>1select * from bulkTest

--匯出測試資料.exec sp_configure 'show advanced options',1--啟用進階配置選項設定reconfigure;exec sp_configure 'xp_cmdshell',1--啟用xp_cmdshellreconfigure;--必須放在同一行執行EXEC master..xp_cmdshell 'bcp "select c.name tablename,c.name columnname, o.object_id,c.column_id,o.type,o.type_desc from sys.objects o,sys.columns c" queryout C:\Users\Administrator\Desktop\bulkTest.txt -c -t"|" -r "\n" -Slocalhost -Usa -Psa'  

--測試用,無需記錄大量日誌ALTER DATABASE [mytest] SET RECOVERY SIMPLE WITH NO_WAIT--ALTER DATABASE [mytest] SET RECOVERY BULK_LOGGED WITH NO_WAIT

提示:
若指定TABLOCK提示,大容量操作將不鎖表,這樣可以並發插入資料,當然要求系統效能好,對資料約束不高的情況.
若不指定TABLOCK,系統預設鎖住整個表,進行大容量操作.

--好了,開始操作!!開啟一個查詢時段,執行以指令碼SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANBULK INSERT mytest.dbo.bulkTestFROM 'C:\Users\Administrator\Desktop\bulkTest.txt'WITH (TABLOCK,FIELDTERMINATOR ='|',ROWTERMINATOR ='\n');select resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=DB_ID() and [email protected]@SPIDROLLBACK TRAN--truncate table mytest.dbo.bulkTest--SELECT * from mytest.dbo.bulkTest

此文章只是觀察資料庫中鎖粒度和鎖模式出現的相關操作和部分影響。

參考：

鎖粒度和階層
關鍵範圍鎖定定
鎖模式

sys.dm_tran_locks (Transact-SQL)
BULK INSERT (Transact-SQL)

SqlServer 並發事務（二）：鎖粒度和鎖模式