Hi everyone, today we are going to explore what a lock is, its modes, what resources they do lock, the duration they hold the resource for, who owns it, and some DMVs on the subject. and then we’ll run some demos to apply what we have learned so tag along.

Locks

it’s a logical concept(means the engine doesn't need to operate on these, with some sacrifices in data integrity your query will be executed in contrast to latches which are physical, meaning they are enforced by the engine on everything and you can’t function without them ) that prevents other sessions from using the resources at certain levels. it is the way SQL serve keeps ACID properties intact, the it implements pessimistic isolation levels by locks, by other means the difference between one isolation level and the other is the type of locks it acquires. the type of query you use and your isolation level determine what kind of resource you will need, more locks more contention less concurrency, and performance issues. when you use locks you lock something from being read, modified, etc. it could be a row or a table …. we call that a lock resource not all locks are similar each type is called a mode then you have their duration, what is the scope of them = who owns them and how do review related information = DMV

Lock Resource

what are we using, locking other people from using? generally SQL server tries to pick the most granular form which is a row lock, however, each lock requires some memory and sometimes acquiring a separate lock on each row would require a lot of memory, at some tipping point SQL server will decide that locking higher resources like a table would be more beneficial, this is called lock escalation. when you lock an index you lock a KEY, when you lock a HEAP table you lock row ID lock =RID, if you lock clustered indexes will acquire KEY lock on all the rows, when you lock non_clustered index you only acquire it on the rows that index contains. now if our isolation level is not SERIALIZABLE when lock data while we are dealing with it, no need to lock resources in between, we only lock rows we need, in SERIALIZABLE you lock everything in between to prevent phantom reads( different results from the same select while somebody else is updating for more details check out our first blog here) so it locks everything in the range of the update, this is what you call Key_Range lock. this will be clearer in upcoming examples.

Lock Modes = Types

Shared Locks (S)

when you read anything in SQL server except in read committed and optimistic concurrency models (check this out for more explanation here) you acquire it, it prevents data from being modified by blocking exclusive locks unless the session that acquired the S lock(shared) is the same session that’s updating. this is not the case when in SERIALIZABLE or REPEATABLE_READ, it takes S locks and does not release them until the end of the read.

Exclusive Locks (X)

when you write data ( INSERT, UPDATE, DELETE) you acquire this lock, one transaction at a time could take it, and you can’t read the data meaning you can’t get S locks on the stuff(except in READ_UNCOMMITTED you can read whatever you want )

Update Locks (U)

update is a mix of X and U, it holds an Update lock on all the rows it might modify while searching for them (U lock) then when any row meets the criteria it holds X locks on it so no one can read or modify it. X locks are totally incompatible with it while S locks could be on U but not on X ( like it is supposed to be) with this process it prevents other modifying sessions from approaching the same data (ACID). while this is good for data consistency if two U locks are held on different rows on the same table after a while a clash could happen at some point on the same resource, if the timing is known the first one would be chosen and the other one will be prevented from taking the resource, but if they both try to acquire X locks on the same row Deadlock would happen meaning non of them will have what he wants and they both will be blocked. SQL server has some ways to deal with this but if not your system will be frozen, we will talk about this in another article

Intent Lock (IU, IX, IS)

it is not a lock on its own, but in some instances SQL server might need to use any of these locks on the resources so it provides other sessions with this information. at any moment IU might become U so be aware of that. they are compatible with each other so while you are updating some row you will have an U, but at any moment you might update a bigger set of rows and higher granularity so you lock a PAGE with IU. That is the main purpose of it. for example you are modifying Order ID you will have row lock X and IX on PAGE from SalesOrderHeader table etc.

Lock Duration

depends on two factors :

isolation level: so if you use READ_UNCOMMITTED you will acquire no S locks on any data, which means you can read whatever you want, so nothing slows you down but you will have data inconsistency. on the other hand, if you use SERIALIZABLE you will not just lock S on what you read, you will lock everything in between too so no one can even read what you are modifying.
Lock Mode: X locks block everything S locks just reads the more modifying sessions you have the more wait you will have which could slow the system .

Lock Ownership = Where in the server is this lock located

the scope of the lock is the ownership ( A column in this DMV: sys.dm_tran_locks)

if it is in a TRANSACTION ( which is the most common type of ownership) then it is created by it and operates based on it and what we mostly deal with in troubleshooting,
or it is A SHARED_TRANSACTION_WORKSPACE which is created by each connection in a database (other than master or tempdb ) by using these you can tell which database is active and how many people are using it etc.
or it is an EXCLUSIVE_TRANSACTION_WORKSPACE which is acquired when you are doing something to the entire database ( like dropping it or making it in a read-only status etc. ) you can only have one on a database and you need to have no SHARED_TRANSACTION_WORKSPACE it otherwise you will be screwing the entire database while someone is connected and reading or modifying something. you never restore tempdb so you won’t have it cause there is no need to and you cant drop or change the readability of the master database

sys.dm_tran_locks A DMV

this what we use to diagnose deadlocks , it is the first handy tool , every column has one of two prefixes :

request
and resource

we will talk about each

resource_

the most useful ones are resource_type and resource_id

resource_type

what is using this lock on that is what it is and these are the types :

DATABASE: it is always there for every lock and ours was DATABASE and it will always have and resource_type_id our row was 4 here
OBJECT: could be any object table, view etc.

for example, here it is an object and the resource_entity_id = 2085582468

HOBT( a partition of a table or an index ): This value corresponds to sys.partitions.hobt_id if you had one and you will find it in resource_entity_id
KEY ( a row in an index clustered or non_clustered in a heap or not ): it will contain a hash value( some calculation) based on RID + whatever columns the non_clustered is based on or in a clustered from the key components of the index

here it locked a row on an index and the id is 720……

RID : is like a key but in a heap and there is no calculations here and no index

for full list of the resources you could check out the docs databases don’t have resource_entity_id so it will always be zero, same goes for EXTENT. everyone has different ways of customizing this view in this series will try different scripts, will start with Kalen Delany’s DBLOCKS view :


IF EXISTS ( SELECT 1
FROM sys.views
WHERE name = 'DBlocks' )
DROP VIEW DBlocks ;
--- HERE SHE IS JUST CHECKING IF SOMEBODY CREATED A VIEW WITH THE SAME NAME 
GO
CREATE VIEW DBlocks AS
SELECT request_session_id AS spid ,
DB_NAME(resource_database_id) AS dbname ,-- THIS FUNCTION IS OBVIOUS RETURNS THE NAME
-- OF WHATEVER DATABASE ID YOU PUT IN IT WHICH YOU WILL GET FROM THE DMV 
CASE WHEN resource_type = 'OBJECT'-- VIEW , TABLE ETC.
THEN OBJECT_NAME(resource_associated_entity_id)-- SO YOU GET THE OBJECT NAME 
WHEN resource_associated_entity_id = 0 THEN 'n/a'-- IT IS NOT AN OBJECT IT IS EITHER A 
-- DATABASE OR SOMETHING THAT HAS NO OBJECT ID 
ELSE OBJECT_NAME(p.object_id)-- SAME HERE BUT FROM PARTITIONS
END AS entity_name ,
index_id ,
resource_type AS resource ,-- RENAMING FOR MORE OBVIOUS NAMES 
resource_description AS description ,
request_mode AS mode ,
request_status AS status
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = DB_ID()--- THIS WILL GET WHATEVER DATABASE YOU ARE USING 
AND resource_type <> 'DATABASE' ;-- SO WE DON'T HAVE TO DEAL WITH SOMETHING WILL BE
-- ON EVERY LOCK INITIALLY

request_

the other prefix in sys.dm_tran_locks

request_mode = lock type for example Sch-S

request_type = it is always = LOCK
request_status =
- GRANT = The resource is locked but whatever lock you want it is all yours
- WAIT = You have to wait until the other transaction is done
- CONVERT = if you had S and you want to upgrade to an X you would like to convert your lock so you already have one but you want more
request_session_id or in DBLOCKS (spid) = it is exactly as it sounds it is the session id to whatever SSMS window you opened
request_reference_count : how many times you request this resource that does not close automatically
request_exec_context_id =
- 0 = non_parallel query
- 0 a parallel query
request_owner_type = Lock Ownership heading in this article at first like Transaction which means a transaction requested this lock and it is owned by it
request_owner_id = A transaction ID unless you are using FILESTREAM
lock_owner_address = address of waiting task very helpful for troubleshooting based on wait stats

now we are going to create some locks

Examples

Case one: Reading READ COMMITTED

now the default is READ COMMITTED in 2022 but if you have changed it at some point it won’t hurt to reset it to have similar results

ALTER DATABASE ContosoRetailDW SET READ_COMMITTED_SNAPSHOT OFF;
 GO -- Set the transaction isolation level to READ COMMITTED 
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

now we have to check what we did so in the first query we’ll check if we have snapshot isolation on and it should return 0

SELECT 
    name AS database_name, 
    is_read_committed_snapshot_on 
FROM sys.databases
WHERE name = 'ContosoRetailDW';

and in the second one we will check what mode is it on spid equals the new query button in SSMS which is a new session, and this is another DMV sys.dm_exec_sessions

SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

and that is what we want , now lets see it in action we will use Kalen’s DBLocks and some other scriot

BEGIN TRAN;
 SELECT *
 FROM DimAccount;
 SELECT*
 FROM DBlocks;
 SELECT 
    request_session_id AS spid,
    DB_NAME(resource_database_id) AS dbname,
    CASE 
        WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
        WHEN resource_associated_entity_id = 0 THEN 'n/a'
        ELSE OBJECT_NAME(resource_associated_entity_id)
    END AS entity_name,
    resource_type AS resource,
    resource_description AS description,
    request_mode AS mode,
    request_status AS status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND resource_type <> 'DATABASE';
 COMMIT TRAN; 
 GO

in READ COMMITTED the engine acquires S lock while he is reading but here it was so quick that we did not even get to see it while SELECT the DBlocks view and we only saw an IS on the view itself which means even when we were SELECTing the view we acquired S locks that were so quick we could not see it and the same happened with the last query we could not even see the IS since we were done with request now both queries filter the DATABASE lock since they have to be on every transaction and they are rarely useful.

Case Two: Reading In REPEATABLE READ

now let's reset and check our isolation level

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
 GO
 SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO

We have some indexes here so in order to see them you could use this query

USE ContosoRetailDW;
GO

SELECT 
    i.name AS index_name,
    i.index_id,
    ic.index_column_id,
    ic.column_id,
    col.name AS column_name,
    i.type_desc AS index_type,
    i.is_unique,
    i.is_primary_key,
    i.is_disabled
FROM 
    sys.indexes AS i
JOIN 
    sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN 
    sys.columns AS col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE 
    i.object_id = OBJECT_ID('DimCurrency')
ORDER BY 
    i.index_id, ic.index_column_id;
GO

and the results

so when we see KEY as resource_type it means these indexes now let’s try something

GO
BEGIN TRAN 
SELECT*
FROM DimCurrency;
SELECT*
FROM DBlocks ;
COMMIT TRAN;
GO

and the results are

So it acquired locks on all indexes while it was reading it to prevent REPEATABLE READ and it had 2 intent locks one on the PAGE and the other one on the TABLE which had OBJECT name since it is one of them objects, in order to prevent the REPEATABLE READS it had to lock them all until the end of the transaction unlike READ COMMITTED which could not see its locks ‘cause it already released them while we were to read the DMV. it had its locks only on the Clustered index.

Case Three: Reading in SERIALIZABLE

now let’s change the isolation level and check it

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
 GO
 SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO

The results are

now let’s try something

GO
BEGIN TRAN 
SELECT* 
FROM DimCurrency;
SELECT*
FROM DBLocks;
COMMIT TRAN 
GO

Same as REPEATABLE READ except here we are trying Phantom Reads too so it would hold RangeS-S, so no body could insert anything even on the stuff we are not reading, and the difference between phantom read and non_repeatable read is that two transactions can’t have different reading results while reading the same information because a third somebody is trying to modify between the two, non on the other hand only guarantees one of them would not have different results. as you can see it acquired IS on PAGE and OBJECT too.

What Does Range Mode do ?

if you were trying to read CurrencyKey from DimCurrency that has 2,3,4 and 5 it will lock them obviously then it would lock 1 to 2 so nobody could insert there and 5 to 6 so nobody could insert after so the KEY before is also locked too let’s see

GO
BEGIN TRAN 
SELECT CurrencyKey
FROM DimCurrency
WHERE CurrencyKey BETWEEN 2 AND 5;
SELECT*
FROM DBLocks;
COMMIT TRAN 
GO

The results

one on 1 to 2 , one on 2 to 3, one on 3 to 4, one on 4 to 5, and finally one on 5 to 6 the first and the last one is not asked for but they are here to ensure nobody inserts in the range 5 locks

4th Case: Modification in READ COMMITTED

okay first let’s change our isolation level and check

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 GO
 SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO

Let’s try something

GO
BEGIN TRAN
UPDATE DimChannel
SET ChannelName = 'OFFLINE'
WHERE ChannelKey = '2';
SELECT*
FROM DBlocks;
COMMIT TRAN
GO

And this will pop up

so it took a U lock on the KEY but we could not see it because it almost immediately escalated to an X lock on it - the whole reason for the U lock is to see if the thing need an X lock - then it escalated the other to IX on PAGE and table = OBJECT.

5th Case: Modification with SERIALIZABLE:

Now first reset the isolation level and check

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
 GO
 SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO

now let’s try something :

GO
BEGIN TRAN
UPDATE DimAccount
SET AccountName = 'IDK'
WHERE ParentAccountKey='6'
SELECT*
FROM DBlocks;
COMMIT TRAN
GO

The results will be like

so it took Range S-U on several KEYs looked around and found the ROW escalated that specific row lock to a RangeX-X - before that, it took IU on page and OBJECT and Escalated both to A IX but we couldn’t see i-. In all our previous examples we did not have any non-clustered indexes on our tables so the update was only on the clustered index level one if did have one the index id would have been different.

6th Case: Creating A Table In READ COMMITTED

Let’s reset and check

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
 SELECT 'Isolation level' = CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    ELSE 'Unknown'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Now let's create a table that has 3 columns one ID and two others, it has one primary key, one clustered index, and one nonclustered index, then populate it with a thousand rows:

BEGIN TRANSACTION;
CREATE TABLE MyTable (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Col1 NVARCHAR(50),                          
    Col2 NVARCHAR(50)                           
);
CREATE NONCLUSTERED INDEX IX_Col1 ON MyTable (Col1);
SELECT*
FROM DBlocks;
DECLARE @Counter INT = 1;
WHILE @Counter <= 1000
BEGIN
    INSERT INTO MyTable (Col1, Col2)
    VALUES ('Value ' + CAST(@Counter AS NVARCHAR(10)), 'Description ' + CAST(@Counter AS NVARCHAR(10)));

    SET @Counter = @Counter + 1;
END;
COMMIT TRANSACTION;

and a sample of the results

so this is what happened in a nutshell :

here we are writing new data to the system so we have to acquire X and IX on tables and rows we add them
then we Build the table on the stuff we added in the sys tables and for that we need sch-M locks and
then we have to update the metadata for that so IX and X on that
and then we insert into the table and for that, we need IX and X on our table

the details will be provided on another blog.

7th Case: UPDATING A HEAP In READ COMMITTED

We created this table

CREATE TABLE HeapTest (
    Col1 INT,
    Col2 INT
);
GO
DECLARE @i INT = 1;

WHILE @i <= 1000
BEGIN
    INSERT INTO HeapTest (Col1, Col2) VALUES (@i, @i);
    SET @i = @i + 1;
END
GO

then we are going to UPDATE it and see the lock types

GO
BEGIN TRAN 
UPDATE HeapTest
SET Col2 = 1000
WHERE Col1 = 1;
SELECT*
FROM DBLocks ;
COMMIT TRAN ;
GO

The only difference here is that you insert into a RID a row pointer instead of KEY an index the resource is RID . and with the last example we are done with basic lock types catch you on the next one.

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners by Dmitri Korotkevitch Pro SQL Server Internals by Dmitri Korotkevitch SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Technique by Dmitri Korotkevitch SQL Server Concurrency by Kalen Delaney Microsoft SQL Server 2012 Internals by Kalen Delaney