Pages

Thursday, August 22, 2019

Key Range Locks (RangeS-U) in the default Isolation Level Read Committed

As we all know, SQL Server applies Key Range locks in the Serializable isolation level to prevent Phantom Reads,
But recently, in a deadlock graph, I have encountered Key Range (RangeS-U) locks in the default Isolation Level - Read Committed.

My question was, how come, why?
Well, the answer is: foreign key constraints with Cascading Deletes.

Once you have FK defined with “ON DELETE CASCADE” and you delete records from the parent table, SQL Server will use Key Range Locks at the child table to prevent insertion of new records during the Cascading Delete operation.

So now it makes sense why you can see the Key Range Locks (RangeS-U) in the default Isolation Level Read Committed:)


No comments:

Post a Comment