Suleyman Essa
Lock Compatibility, Conversion, Intention locks,Key-Range Locks, And Escalation
Suleyman
December 14, 2024
Uncategorized
Hi everyone, today we will explore in depth how each lock interacts with the others and which one could escalate to what and the special SERIALIZABLE lock mode Key Range locks. SQL server is very efficient; it tries to hold its deepest, least resource-intensive lock mode, but sometimes it has to escalate or move from lock mode to a higher one to assure efficiency. We will explore these.    

Lock Compatibility = How do locks interact with each other

if you are trying to lock some resource ( PAGE, KEY etc.) SQL server checks if there is any other session, or transaction holding any locks on the same resource, if there is none you will get your resource and your status will be GRANT(in sys.dm_tran_locks), or there is another lock on the stuff it will check compatibility, an internal table to see if you can hold the same lock on the row if you can you will get GRANT like with S locks two transactions can read the same resource at the same time, but if they are not compatible can not co-exist then you will be in WAIT status which exactly what it sounds you will wait until the resource lock is released and then you can do whatever you want, like if you are you trying to modify a statement you will get X lock and if somebody else is trying to read he will be blocked because they are incompatible with each other. there is a lot of tables in the docs ( docs) we will go through some in this article.   the first one is : for example, if you are updating a row you will have an X lock and it will be granted, but you can’t use any other lock mode on the same row. there is a huge table in the docs that includes all kinds of lock compatibility you can check out and it all works the same way as this one, we will touch on a few more here :

Sch-S: schema stability lock

this lock is compatible with everything except Sch-M, it is main purpose is to prevent ALTER TABLE statements from Modifying while you are reading, and it is so quick that you can’t really see it sys.dm_tran_locks except in rare situations we are going to create some situation where you can see it and we are going to explain extended events on locking in an upcoming blog. you need 3 sessions one to read, one to alter the same table, and a third one to query DBLocks (Kalen Delany’s script check out resources)(we are going to use Microsoft’s ContosoRetailDW Database)(also this article by Mr. diaz has a similar situation here) 
BEGIN TRAN --56
SELECT*
FROM DimCurrency;
WAITFOR  DELAY '00:00:10';
COMMIT TRAN
The second session ( New Query button in SSMS) 
BEGIN TRAN -- 73
ALTER TABLE DimCurrency 
ADD Col3 VARCHAR(25);
WAITFOR DELAY '00:00:02';
ROLLBACK TRAN
And a third Session 
SELECT*--75
FROM DBLocks
and our results if you notice it here we could not see the 56’s Sch-s since it was released but we saw 73’s Sch-m and Sch-s and they were both granted since we have Sch-s on the same table now if we add query hint WITH (SERIALIZABLE) : 
BEGIN TRAN --56
SELECT*
FROM DimCurrency WITH (SERIALIZABLE) ;
WAITFOR  DELAY '00:00:10';
COMMIT TRAN
73 had to WAIT and that is the whole purpose of this lock

Sch-M: Schema Modification Locks

it is the least compatible lock, it is acquired with ALTER TABLE or any other operation that changes the table’s definition, you can have only one Sch-m on the whole table and you can’t have any other session doing anything. the previous example should suffice.

Lock Conversion

when you move from a low resource-consuming lock to a higher one you are converting locks like from IS to S, S to X, or IX to X. let's look at some
  • first let's create a table with 3 columns: 1 ID and 2 Col
USE ContosoRetailDW;

BEGIN TRAN 
CREATE TABLE ConversionTable (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Col1 NVARCHAR(100),
    Col2 NVARCHAR(100)
);
WAITfOR DELAY'00:00:02';
SELECT*
FROM DBLocks;
and the results are
  • so first locks are on some undocumented system tables on our database these were acquired because we are trying to acquire X locks on some rows since we are trying to add some new stuff like columns (syscols)
  • to metadata tables then these will be shipped and modified internally to DMVs where you see METADATA as a Resource type these IXs might escalate to X if we made enough modifications you see Sch-m lock on partitions (HOBT) Which you would expect since we are doing a DDL statement on a table = adding to its schema = schema modification lock and Sch-s I don’t what is n/a is but it is acquired on every read to protect from Sch-m like we said
  • here SQL server auto-created a primary key and an index on ID both are Sch-m Statements
  • after that, we see a lot of undocumented stuff that had been supplied with Sch-m modifications
  • Then let's insert a thousand rows
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO ConversionTable (Col1, Col2)
    VALUES ('SampleData' + CAST(@i AS NVARCHAR(10)), 'MoreData' + CAST(@i AS NVARCHAR(10)));
    SET @i = @i + 1;
END;
SELECT*
FROM DBLocks;
  • here we will have IX and X like inserts
  • Then let's create a clustered index on ID columns
CREATE CLUSTERED INDEX IX_ConversionTable_Id
ON ConversionTable (id);
WAITfOR DELAY'00:00:02';
SELECT*
FROM DBLocks;
commit tran
  • here you will have the same as the top thing Sch-m, IX, and X
  • every thing before will be a different transaction so commit here then begin another one
  • Then let’s use REPEATABLE READ query hint for one read
begin tran
SELECT *
FROM ConversionTable WITH (REPEATABLEREAD)
WHERE id = 2;
WAITfOR DELAY'00:00:02';
SELECT*
FROM DBLocks;
  • here you see an S lock on the row and IS on an object which is the table and the PAGE where the KEY or the row index is located or pointed to depending on the page
  • and one update
UPDATE ConversionTable WITH (REPEATABLEREAD)
SET Col2 = '56'
WHERE id = 3;
WAITfOR DELAY'00:00:02';
SELECT*
FROM DBlocks;
COMMIT TRAN
  • each time we will query DBLocks(Kalen Delany’s script) to see what is going on
  • I got tired here since I did not see U because it was so fast that almost instantaneously escalates to X and IU to IX so I changed the query
  • here I will show the one with extended events we will talk about in another article but I will show the code and a sample output
begin tran
UPDATE ConversionTable WITH (REPEATABLEREAD)
SET Col2 = '56'
WHERE id ='';
WAITfOR DELAY'00:00:02';
SELECT 
    transaction_id
FROM 
    sys.dm_tran_current_transaction;

COMMIT TRAN
  • so first it acquired IX on the table and IU on the page together on our transaction so no problem both are compatible, then it starts reading since we have over a thousand locks on 1000 row table that’s because we have extra on pages, objects, and escalation locks and we have U on every row and IX on OBJECT, PAGE, and the modified ROW, and X since we don’t have empty Col2

Special Intent Locks: Special Thing + I Lock

  • now we have regular old fashion conversion locks or intent locks IX, IU, and IS these are compatible with each other and can escalate for higher locks,
  • but there is another type of conversion locks( locks meant to be converted) special intent locks they happen in the following scenario :
  1. we have one transaction with two statements one update and one read
  2. you have a statement that wants to update a row it acquires IX on page, object, and X on KEY, RID
  3. another statement wants to read it acquires S on PAGE, OBJECT
  4. SQL server Combines both and forms IX+S = SIX so you have an S and IX
  5. if we had IX and IS on the same resource (high-level resource) it would just drop the lower one since the first one has
now let’s run some demos

Shared Intent Exclusive locks: SIX

we just explained it so I will just get right to it 
USE ContosoRetailDW
GO
BEGIN TRAN 
UPDATE ConversionTable
SET Col1 = '100'
WHERE Col2='90' ;
SELECT id
FROM ConversionTable WITH (TABLOCK)/*here we hinted to USE S on the tables 
becaues we don't want to wait for SQL server's memory tipping point which is like %3 
of SQL serve memory and this table has a thousand rows in it */
WHERE Col1 = '20';
select*
from DBlocks;
COMMIT TRAN
and the results are here you go

Update Intent Exclusive: UIX

here is the scenario:
  1. one transaction with a U lock on PAGE resource and one with IX on the same one
  2. The first one has a U lock on it
  3. The second one has IX on the same resource
  4. both combine to form UIX
now let's run a demo 
BEGIN TRAN 
UPDATE ConversionTable
SET Col2 ='200000'
WHERE Col1= 'SampleData11'
USE ContosoRetailDW;
SELECT id
FROM ConversionTable WITH (PAGLOCK,UPDLOCK)/* we had to hint here since we don't want
 to wait for the tipping point or an update that does not acquire X on 
a page but does U */
WHERE Col1 = '20';
COMMIT TRAN
and

Shared Intent Update: SIU

so :
  1. so we have an update statement on a row with U and IU on a PAGE since we can’t have it on a table
  2. we have another one with read S on the same PAGE and rows
  3. both combined SIU
demo 
BEGIN TRAN 
UPDATE ConversionTable WITH(ROWLOCK,UPDLOCK)
SET Col1 =''
WHERE Col2='';
waitfor delay '00:00:01';
select*
from DBLocks;
SELECT COL1
FROM ConversionTable WITH(PAGLOCK,REPEATABLEREAD);
SELECT*
FROM DBLocks;
COMMIT TRAN
and this is what you will see and we are done here.

Key-Range Locks

we talked about it before, the whole thing of SERIALIZABLE is preventing phantom reads( preventing two sessions from having different results while reading from the same resource while a third transaction is modifying) and to do that it had to lock the resource, the row before it, and, the row after it now if you lock on KEYs as the resource (an index) then you will get a Key-Range lock, let’s start with a brand new table insert 6000 rows and we will see where it goes 
USE ContosoRetailDW;

-- Create the table tableKeyRangeLs
CREATE TABLE tableKeyRangeLs (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Col1 NVARCHAR(100),
    Col2 NVARCHAR(100)
);
go
-- Create a clustered index on the id column
CREATE CLUSTERED INDEX IX_tableKeyRangeLs_id
ON tableKeyRangeLs (id);
go

-- Insert 6000 rows into tableKeyRangeLs
DECLARE @i INT = 1;
WHILE @i <= 6000
BEGIN
    INSERT INTO tableKeyRangeLs (Col1, Col2)
    VALUES ('SampleData' + CAST(@i AS NVARCHAR(10)), 'MoreData' + CAST(@i AS NVARCHAR(10)));
    SET @i = @i + 1;
END;
then let's set the transaction isolation level to SERIALIZABLE and double-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

RangeS-S Locks: S Locks On A Range Of Values Before, On, And After

as you guessed probably reads in SERIALAZABLE acquires RangeS-S locks on every row, the one before the lowest, and the one after the highest in the range. this happens if you have an index so you could have a range let’s try a demo on our table 
begin tran 
select id 
from tableKeyRangeLs 
where id between 5 and 8 ;
waitfor delay '00:00:05';
select*
from dblocks;
commit tran
image.png I assume you can predict what happened here:
  1. first you want to read a row so you need to acquire IS on OBJECT(table) and on PAGE
  2. then since it is an index you need to acquire RangeS-S on every range :
    1. first one on 4 to 5 ( lock before)
    2. the second one is 5 to 6
    3. the third one is 6 to 7
    4. the fourth one is 7 to 8
    5. and the last one 8 to 9 to in( one lock after)
what if you had a heap ( no index ) : first, let's create a heap and insert a thousand rows into it 
IF OBJECT_ID('myHeapTable', 'U') IS NOT NULL
    DROP TABLE myHeapTable;

-- Create the heap table myHeapTable
CREATE TABLE myHeapTable (
    Col1 NVARCHAR(100),
    Col2 NVARCHAR(100)
);

-- Insert 1000 rows into the heap table
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO myHeapTable (Col1, Col2)
    VALUES ('SampleData' + CAST(@i AS NVARCHAR(10)), 'MoreData' + CAST(@i AS NVARCHAR(10)));
    SET @i = @i + 1;
END;
and read some stuff with DBLocks: 
USE ContosoRetailDW;
BEGIN TRAN;
SELECT  Col1, Col2
FROM myHeapTable WITH (SERIALIZABLE)
WHERE Col1 BETWEEN 'SampleData10' AND 'SampleData20';
SELECT * 
FROM DBlocks;
COMMIT TRAN;
and guess what you only see S locks where are the KEY RangeS-S locks? non why? SQL server opted directly to a table lock in here instead of Ranges-s on each 2 RID RangeS-S.

RangeS-U

if you have a heap and a non-clustered index, but you are updating columns that do not have an index on it you will have this lock, you will have S locks on the ranges but the rows themselves will have U locks on them. the rows will have S locks on them if you modify on the RID. first let’s create A non-clustered index on Col1 and update Col2 
USE ContosoRetailDW;
CREATE NONCLUSTERED INDEX IX_myHeapTable_Col1
ON myHeapTable (Col1);


USE ContosoRetailDW;
BEGIN TRAN;
UPDATE myHeapTable
SET Col2 = 'UpdatedValue'
WHERE Col1 = 'SampleData10';
SELECT*
FROM DBLocks;
COMMIT TRAN;
this is what happened :
  1. you want to reach to the row through the index and update in order to do that you acquire IX on the table and IU the PAGE
  2. then you acquire RangeS-U on the range that includes the value, row before, and after on theKEY the index
  3. then you X on the RID and that’s it

RangeX-X

here you are updating that is about it. let’s use tableKeyRangeLs this time 
USE ContosoRetailDW
BEGIN TRAN 
UPDATE tableKeyRangeLs WITH (SERIALIZABLE) 
SET Col1 ='WHATEVER'
WHERE id BETWEEN 5 AND 8;
COMMIT TRAN
This is what happened:(here Paul White and here Erik Darling for LAST_MODE instead of RX-X in extended events because when I ran it that’s what I found ignore this now, we will come back to it later int another blog.)
  1. so you are trying to modify through an index so you don’t need to read too much you will almost immediately reach your stuff no need for looking around so no IU just IX on PAGE and OBJECT
  2. what row range are you modifying lock it all RangeX-X and that is it

RangeI-N = you are inserting into a NULL in an index

you have no resource to lock but you are in SERIALIZABLE so you need An X on an empty range this is what you get, it is a special X with one of the ranges being NULL in the index, so you have like a key but the value that the key points to is a NULL. let’s try it we are going to use the tableKeyRangeLs that we created, we will delete a few rows, try to read them in a session, and then we will try to refill the old one. 
use ContosoRetailDW
-- delete
begin tran 
delete from tableKeyRangeLs 
where id in (200,201,202,203)
commit tran
second step 
begin tran 
select*
FROM tableKeyRangeLs WITH(SERIALIZABLE)
WHERE ID BETWEEN 200 AND 203 ;
WAITFOR DELAY '00:00:10';
SELECT* 
FROM DBlocks;
-- no commit
third step in a different session (new query button in SSMS) 
USE ContosoRetailDW
BEGIN TRAN 
SET IDENTITY_INSERT tableKeyRangeLs on ;
insert into tableKeyRangeLs(id,Col1,Col2) 
 VALUES (200,'',''),
        (201,'',''),
		(202,'',''),
		(203,'','');
SELECT*
FROM DBlocks;
ROLLBACK TRAN
And DBLocks will show this: 56 is the reader obviously and 71 is the writer so this is what happened:
  1. we tried to read in 56 we acquires IS on PAGE and OBJECT and RangeS-S on the rows
  2. while we were doing that session 71 acquired IX on PAGE,OBJECT and RangeI-N on the empty rows
  3. he got blocked by our uncommitted transaction
  4. his status is WAIT
and that is it

Conversion Key-Range Locks = Key-Range Lock + another Lock

so you have a session or a transaction that has an S lock and the same one acquires RangeI-N lock it will convert those locks to RangeI-S and that is about it, there is a table for compatibility in the docs(DOCS) we will reproduce it here:

Lock Escalation

SQL server tries to be as efficient as he can, the least amount of locks, the most granular ones, row-level locks but sometimes,you might need to read an entire index page so he will acquire a lock on it or if it need to, if you need to review the entire table it might just lock the whole thing for you with one lock.

Escalation based on instance(the whole server) consumption

if an instance acquires more than 24% of its memory SQL Server will pick a random session(we can’t control which session will be escalated ) and escalate its locks to a Table lock. we can reset the threshold if we want, if we specify the memory we need for locks SQL server will escalate once 40% memory has been used for example if you specify 10k it will escalate at 4k etc. then after 1250 locks SQL server will reevaluate and see if he can use lower locks again. since the engine is great most of the time we will not show it here.

Escalation by the number of locks used by an individual statement

if you acquire more than 5000 locks in one statement SQL server will escalate that session(no randomness) to the next level lock (from KEY to a PAGE etc.) First we created a table with id and Col1 then we inserted 20k row into them 
CREATE TABLE dbo.NewTable (
    id INT PRIMARY KEY CLUSTERED,
    Col1 INT
);
DECLARE @i INT = 1;

WHILE @i <= 20000
BEGIN
    INSERT INTO dbo.NewTable(id, Col1)
    VALUES (@i, @i);

    SET @i = @i + 1;
END;
now we will read the whole table and see what extended events will show us (we will cover it in another article) 
BEGIN TRAN 
SELECT*
FROM dbo.NewTable;
SELECT*
FROM DBLocks;
SELECT transaction_id 
from sys.dm_tran_current_transaction;
COMMIT TRAN
as you can see we have only 50 S locks and they are on PAGE not on KEYS since SQL Server escalated to pages. now if we select until id = 100; 
BEGIN TRAN 
SELECT*
FROM dbo.NewTable
WHERE 1000>=id ;
SELECT*
FROM DBLocks;
SELECT transaction_id 
from sys.dm_tran_current_transaction;
COMMIT TRAN
AND we have 107 locks most of them are at the lower resource KEY and there are two IS on PAGE and OBJECT since SQL Server did not need to escalate. we are done with this blog, I'll 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
Share on Facebook
Share on Twitter

Leave a Reply

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