I am going to start a series of posts to talk about Locking and blocking in SQL Server, which is one of my favorate topics. As a SQL Server developer, many professionals are more focusing on programming languages, database designs, features (like Service Broker), writting SQLs to effectively manipulate data and process designs. They’re all correct. But one more thing is quite frequently getting neglected is locking. Especially after SQL Server 2005, Snapshot isolatioin level is used more frequent than others. It seems like SQL Server will never locked. Actually, that’s not the fact. Nothing has been changed from locking perspective before and after version 2005. After having a fully understanding of locking and blocking mechanism, you will find out you can easily write a high concurrency application, prevent excessive blocking, and design more sophisticated concurrent SQL process controls.

If you have any experience with multi-threading programming, you’re probably familiar with the keyword lock(obj){}, mutex, semaphore, and some others. Most of them can be implemented by TSQL! As a typical multi-threading application, SQL Server, it implements all isolation levels, Read Uncommitted, Read Committed, Repeatable Read, and Serializable, by locks ONLY.
Now let’s get started. First of all, let’s run this procedure on any of user database

exec sp_lock @@spid
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51     13     0           0      DB                                    S        GRANT
51     1      439672614   0      TAB                                   IS       GRANT
*/

You will see as you connect to the a database, you immediately get a Shared lock on database id = 13. This tells the system, the database is in use, no one should drop it. What is the second line? The object is in database ID 1, master database, with object id 439672614. This object is dbo.spt_values which hold the lookup values for many IDs. Some of the values are lock type – DB means database, TAB means table. There are also other types. I will introduce them in my future posts.
Alternatively, you can also use

select * form sys.dm_tran_lock where request_session_id = @@spid

The ultimate killer is using Extended Event below. We are going to use it a lot in the future posts to analyze locking behaviour of SQL Server.

CREATE EVENT SESSION locks
ON SERVER
ADD EVENT sqlserver.lock_acquired,--(where database_id = 16 ),
ADD EVENT sqlserver.lock_released--(where database_id = 16 )
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

GO

ALTER EVENT SESSION locks ON SERVER STATE = START
--begin transaction
-- You SQL here
--rollback
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, EventName, resource_type ResourceType, 
			mode Mode, resource_0 Res0, resource_1 Res1, 
			resource_2 Res2, lockspace_workspace_id, lockspace_sub_id, 
			lockspace_nest_id, owner_type OwnerType,transaction_id TransID, 
			database_id DBID, 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
Locking and Blocking (1) – Tools

You May Also Like

Leave a 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.