SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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?




Posted by Sam Bendayan on 25 October 2011

OK...I just saw this even on a READ UNCOMMITTED transaction.

Posted by Sam Bendayan on 27 April 2012

Actually, if you have a distributed transaction (DTC) then this always uses Serializable isolation levels and you can't force it to do otherwise.

Leave a Comment

Please register or log in to leave a comment.