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

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

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 🙂