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:

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

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.

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:

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 :)