Key Range Lock in SQL Server

 A Key Range Lock is a specialized locking mechanism used by SQL Server under the SERIALIZABLE isolation level to prevent Phantom Reads. Unlike ordinary row locks that protect only existing rows, Key Range Locks protect both existing index keys and the gaps between them. This ensures that no other transaction can insert new rows into a range that has already been read by the current transaction. Internally, SQL Server acquires range-based locks such as RangeS-S or RangeX-X on index keys. This mechanism guarantees that repeated execution of the same query within a transaction returns the same result set. Key Range Locks are commonly observed in financial systems, inventory management systems, and regulatory reporting workloads where absolute transactional consistency is required. While they provide the highest level of isolation, they can significantly increase blocking and reduce concurrency because inserts into the protected range are prevented until the transaction completes. Understanding Key Range Locks is essential when diagnosing SERIALIZABLE isolation behavior, Phantom Read prevention, and concurrency bottlenecks in enterprise SQL Server environments.

Let’s see the demo

Creating a table and inserting few records.

DROP TABLE IF EXISTS Accounts; GO CREATE TABLE Accounts (     AccountNo INT PRIMARY KEY,     CustomerName VARCHAR(100) ); GO  INSERT INTO Accounts VALUES (1001,'John'), (1005,'Rahul'), (1010,'Amit'), (1015,'David');

See the data

Now running the below query in session 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN; SELECT * FROM Accounts  WHERE AccountNo = 1008; --commit;

Transaction is not yet committed.

It does not return any row. We have not yet inserted accountNo= 1008.

SQL Server understands, "This transaction checked whether 1008 exists."

To guarantee that the result does not change during the transaction, SQL Server locks:

1005 ---------- 1010

not just existing rows. This is called a Key Range Lock.

In session 2 we are trying to insert a record

INSERT INTO Accounts VALUES (1008,'New Customer');

Insert is blocked.

We can see the lock details using deb query

SELECT       request_session_id,       resource_type,       request_mode,       request_status FROM sys.dm_tran_locks WHERE resource_type='KEY';

 

Ø  RangeS-S : Range Shared Key Shared. Used when reading.

Ø  RangeS-U: Range Shared Key Update. Used when update might occur.

Ø  RangeI-N: Insert Intent. Used during INSERT.

Ø  RangeX-X: Range Exclusive. Used during updates and inserts.

If we try to insert out of this range the we can insert that record. Let’s try.

INSERT INTO Accounts VALUES (1248,'New Customer');

Record inserted successfully. Because it is out of range. See this value.

Yet I have not yet committed open transaction. Due to that 1008 we are not able to see. Now we are going to commit the session 1 transaction. As soon as committed this transaction new row inserted. See the record now.

Now Account no 1008 inserted.

Basically, it prevents phantom read.