SQL Server Key Range Locks – RangeX-X and RangeS-U

Hi Friends,

This blog post is a result of a question asked by a DBA from my client side: “Why am I seeing RangeS-U locks along with RangeX-X locks when I run a criteria-based update on a table under Serializable isolation level?”

Short Answer: To avoid phantoms.

Long answer:

USE AdventureWorks2008R2
GO

select * from Production.Product
WHERE ReorderPoint = 600

Production.Product table has 504 rows out of which there are 25 rows with ReorderPoint=600.

-- Connection 1
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
 
BEGIN TRAN
UPDATE Production.Product
SET ReorderPoint = 1000
WHERE ReorderPoint = 600
GO

The above code will update all the rows where ReorderPoint is 600. The transaction runs under Serializable isolation level. Which means:

– no new records can be added to the table with ReorderPoint = 600
– no existing records can be updated where ReorderPoint is 600
– no existing records can be deleted where ReorderPoint is 600

That’s why a key range lock is issued.

Let us verify.

SELECT resource_type, resource_description, request_mode, request_status, *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND request_mode = 'RangeX-X'
GO

1_SQL_Server_Key_Range_Locks_RangeXX _RangeSU

You will additionally observe that 25 rows are returned. This is the same number of records where ReorderPoint is 600. Thus, all rows with value 600 are locked with RangeX-X lock mode.

Now, observe this:

SELECT resource_type, resource_description, request_mode, request_status, *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND request_mode = 'RangeS-U'
GO

2_SQL_Server_Key_Range_Locks_RangeXX _RangeSU

Explanation: The remaining rows where the ReorderPoint is not 600 are locked with RangeS-U lock mode to avoid any updates to these rows (SQL Server here thinks that another transaction can come in and modify the value of one, many or all records to 600 which violates the operation/criteria of the previous transaction which is still not complete – and all this happens only because the transaction is running under the highest pessimistic isolation level; to avoid phantoms)

You will also observe that total numbers of records are 479 + 1. 479 are the remaining rows. There is an additional row with resource_description (fffffffffff) and I leave that to you to investigate 🙂

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server Key Range Locks – RangeX-X and RangeS-U”

  1. Thanks Amit, this post saved me atleast 2 hours of RnD.

    Onq quick question: can we get X-X and S-U locks on the same table in Read Committed Isolation Level also?

Leave a Reply to amit kumar Gupta Cancel reply

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