I talked about tools can be used for analyzing locking behaviors in prevous post. In this post, I am going to describe shared lock and intent shared locks and how to extract index key hash value and index from the returning value of Extended Event. Shared lock and Intent Shared lock are used while reading the data from tables. They called “shared” because if there are one record holding a S lock on an record, other sessions can hold the S lock on the same record. You may think this lock is useless. That’s true if SQL Server only supports those 2 type of locks. In my later post, more lock types will come. The S lock conflicts with Exclusive(X) locks. While an Shared lock is holding, an Exclusive lock will wait. Think about this question, will you allow others to drop a database you connected? No, because there is an Shared lock on the database when a connection is on it.
The IS lock never gets applied to a row, it can be any resource higher than the row in the lock hierarchy. Conceptually, when SQL Server does reading using select
statement, it always tries to apply an IS lock on the table, then then page, then apply the S lock on a record that SQL Server is going to touch, then release the S lock, then apply S lock after reading operation complete then move onto next record, then go through this exercise until all the records are accessed then release IS lock on the page. But there is an exceptions. If all the records in a page have not been changed since last time the page is written to disk, SQL Server will only apply IS lock on the page without S locks on the records. This can reduce the overhead of getting/releasing locks.
First of all, Let’s create some test data.
use master set nocount on if DB_ID('test') is not null begin alter database test set read_only with rollback immediate drop database test end go create database test ; go alter database test set recovery simple alter database test set auto_create_statistics off alter database test set auto_update_statistics off alter database test set allow_snapshot_isolation off alter database test set read_committed_snapshot off alter database tempdb set auto_create_statistics off alter database tempdb set auto_update_statistics off go use test create table SimpleTable(id int , text1 char(1000)) go insert into SimpleTable values(100, replicate('a',111)), (200, replicate('b',111)), (300, replicate('c',111)), (400, replicate('d',111)) go
Then let’s do a simple select on table SimpleTable (line 7 below), then use Extended Event to capture the locking activities.
use test exec xp_cmdshell N'del c:\temp\locktest00*.*' ,no_output go CREATE EVENT SESSION locks ON SERVER ADD EVENT sqlserver.lock_acquired, ADD EVENT sqlserver.lock_released ADD TARGET package0.asynchronous_file_target(set filename = 'c:\temp\locktest00') WITH (MAX_MEMORY = 4096KB,EVENT_RETENTION_MODE = NO_EVENT_LOSS ,MAX_DISPATCH_LATENCY = 1 SECONDS,MEMORY_PARTITION_MODE = NONE,TRACK_CAUSALITY = ON,STARTUP_STATE = OFF); go ALTER EVENT SESSION locks ON SERVER STATE = START select * from SimpleTable ALTER EVENT SESSION locks ON SERVER STATE = STOP go DROP EVENT SESSION locks ON SERVER GO declare @Temp table (ID int identity(1,1), EventData xml);insert into @Temp(EventData) select cast(event_data as xml) event_data from sys.fn_xe_file_target_read_file ('c:\temp\locktest00*', 'c:\temp\locktest00*.xem', null, null) a;with x as (select a.ID, x.value('../@name', 'varchar(128)') EventName, x.value('../@timestamp', 'datetime') EventTime, x.value('@name','varchar(128)') [Col], case when isnull(rtrim(x.value('(./text)[1]', 'varchar(128)')), '') = '' then x.value('(.)[1]', 'varchar(128)') else x.value('(./text)[1]', 'varchar(128)') end [Value] from @Temp a cross apply EventData.nodes('//data') ed(x)) select * from( select ID, cast(EventName as varchar(20)) as EventName, cast(resource_type as varchar(10)) as ResourceType, cast(mode as varchar(10)) Mode, cast(owner_type as varchar(12)) OwnerType, cast(database_id as int) DBID, cast(resource_0 as varchar(16)) Res0 , cast(resource_1 as varchar(16)) Res1, cast(resource_2 as varchar(16)) Res2, cast(lockspace_workspace_id as varchar(20)) lockspace_workspace_id, cast(lockspace_sub_id as varchar(10)) lockspace_sub_id, cast(lockspace_nest_id as varchar(10)) lockspace_nest_id, cast(transaction_id as int) TransID, EventTime from x pivot( MAX([Value]) for Col in ([resource_type], [mode], [owner_type], [transaction_id], [database_id], [lockspace_workspace_id], [lockspace_sub_id], [lockspace_nest_id], [resource_0], [resource_1], [resource_2])) pvt ) y where ResourceType not in ('METADATA') and Mode not in ('NL') go /* ID EventName ResourceType Mode OwnerType DBID Res0 Res1 Res2 lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID EventTime ----------- -------------------- ------------ ---------- ------------ ----------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- ----------------------- 2 lock_acquired OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 535439 2011-10-11 04:29:30.303 3 lock_acquired PAGE IS Transaction 13 153 1 0 0x0000000080028100 1 1 535439 2011-10-11 04:29:30.303 4 lock_released PAGE IS Transaction 13 153 1 0 0x0000000080028100 1 1 535439 2011-10-11 04:29:30.303 5 lock_released OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 535439 2011-10-11 04:29:30.303 */
From result, you can see, SQL Server acquires IS lock on the object object_id = 2105058535
then acquires IS lock on page 153 (res0 = 153
), file number 1 (res1 = 1
, it’s file number in this case), then releses IS lock on page 153, then release the IS lock on the table.
Now, let’s do an update on one of the records with rollback at the end on another session. Because we don’t want the data modification in the memory to be flushed to disk system.
begin transaction update SimpleTable set text1 = 'zzz' where id = 300 rollback /* Command(s) completed successfully. */
Then run previous script again. Results are
/* ID EventName ResourceType Mode OwnerType DBID Res0 Res1 Res2 lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID EventTime ----------- -------------------- ------------ ---------- ------------ ----------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- ----------------------- 2 lock_acquired OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 3 lock_acquired PAGE IS Transaction 13 153 1 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 4 lock_acquired RID S Transaction 13 153 1 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 5 lock_acquired RID S Transaction 13 153 65537 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 6 lock_acquired RID S Transaction 13 153 131073 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 7 lock_acquired RID S Transaction 13 153 196609 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 8 lock_released PAGE IS Transaction 13 153 1 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 9 lock_released OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 538409 2011-10-11 04:43:02.897 */
Now we got almost the same information from previous code but S lock on the record. Now, let’s look at Resource 1 column and convert the numbers into binary:
select cast(cast(1 as bigint) as varbinary(4)) select cast(cast(65537 as bigint) as varbinary(4)) select cast(cast(131073 as bigint) as varbinary(4)) select cast(cast(196609 as bigint) as varbinary(4)) /*---------- 0x00000001 ---------- 0x00010001 ---------- 0x00020001 ---------- 0x00030001 */
The first 2 bytes are the record number and the last 2 bytes are the file number. In this example, we are using heap table. Now, let’s convert it to clustered index.
create clustered index CL_SimpleTable on SimpleTable(id)
Go to another sesion run
begin transaction update SimpleTable set text1 = 'zzz' where id = 300 rollback
Then run the test again. The results are below
/* ID EventName ResourceType Mode OwnerType DBID Res0 Res1 Res2 lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID EventTime ----------- -------------------- ------------ ---------- ------------ ----------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- ----------------------- 2 lock_acquired OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 3 lock_acquired PAGE IS Transaction 13 155 1 0 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 4 lock_acquired KEY S Transaction 13 14 4292542720 1113278989 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 5 lock_released KEY S Transaction 13 14 4292542720 1113278989 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 6 lock_acquired KEY S Transaction 13 14 295043328 2238952972 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 7 lock_released KEY S Transaction 13 14 295043328 2238952972 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 8 lock_acquired KEY S Transaction 13 14 3419209984 1362870644 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 9 lock_released KEY S Transaction 13 14 3419209984 1362870644 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 10 lock_acquired KEY S Transaction 13 14 3572695296 2738672865 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 11 lock_released KEY S Transaction 13 14 3572695296 2738672865 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 12 lock_released PAGE IS Transaction 13 155 1 0 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 13 lock_released OBJECT IS Transaction 13 2105058535 0 0 0x0000000080028100 1 1 546245 2011-10-11 05:06:25.367 */
Now you can see both resource 1 and resoure 2 populated. Those 2 are very differently than previous one. When table is in HEAP, the lock resource for the record is RowID, File:Page:Slot. When a table is in clustered index, the lock resource is the hash of the key which is generated by SQL Server. Let’s take resources from line 10 and convert them into binary:
select cast(cast(3572695296 as bigint) as binary(4)), cast(cast(2738672865 as bigint) as binary(4)) /* ---------- ---------- 0xD4F30100 0xA33CD4E1 */
The hash is F3D4E1D43CA3. Now let’s validate it.
select id, CAST(text1 as varchar(20)) from SimpleTable where %%lockres%% ='(F3D4E1D43CA3)' /* id ----------- -------------------- 400 dddddddddddddddddddd */
How can i get ‘F3D4E1D43CA3’ ??
That’s the values in Res1
John has explained how to get that value in later post:
http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/