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
*/
Locking and Blocking (2) – Shared Locks and Lock Resource

You May Also Like

4 thoughts on “Locking and Blocking (2) – Shared Locks and Lock Resource

Leave a Reply to John H Cancel reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.