Range locks do not only occur in Serializable transactions....

,

According to BOL, "Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level".  From this I took it to mean that Range locks are ONLY used in Serializable transactions.

So then I had to troubleshoot a deadlocking problem where one of these Range locks was involved.  I removed all the Serializable transactions and I was still getting the range locks.  Bottom line:  it turns out that these Range locks happen in other isolation levels under certain conditions.  In my case, if you update a range of rows then a Range lock is being taken even in the READ COMMITTED isolation level.  I also saw on the Web that it happens during cascading deletes/updates.

Any other experiences with this out there?

SB

 

Rate

Share

Share

Rate