John Huang's Blog

Update and Exclusive locks mostly are used for Data Manipulating Language delete and update. Fisrt of all, let’s have a quick glance below:

use tempdb
set nocount on 
if OBJECT_ID('abc') is not null
	drop table abc
create table abc(id int)
insert into abc values(1),(2),(3)
begin transaction
delete abc where id = 1
exec sp_lock @@spid
rollback transaction
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
52     2      1365579903  0      RID  1:172:0                          X        GRANT
52     2      1365579903  0      PAG  1:172                            IX       GRANT
52     2      1365579903  0      TAB                                   IX       GRANT
52     1      439672614   0      TAB                                   IS       GRANT
*/

From result, you can ignore the line 17, the IS lock on that line on master database (dbid = 1) is look up values in the master database, like RID, PAG, TAB. While data being deleted, there’s on IX (Intent Exclusive) lock granted to the table (TAB), IX lock on the page where the records located, and X (eXclusive) lock on the row. After transaction is rolledback or committed, the lock on those resources will be release. Seem like there is nothing to the Update lock. That’s true if you use this tool or sys.dm_trans_locks to check the locking behaviour. Your will see more locks by using below.

Read more

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.

Read more