Range locks

First published on MSDN on May 26, 2006

Lately I have seen some quesitons on range locks. Why certain keys are locked? How is this behavior different for unique vs. non-unique indexes? So I played around with range locks and here are my findings so far.

Range locks are obtained on index keys to prevent phantoms when you execute transactions under serializable isolation. SQL Server implements this by taking range locks.  There are two cases of interest

• Equality Predicate
  
  
  
  • If the key value exists, then the range lock is only taken if the index is non-unique. In the non-unique index case, the ‘range’ lock is taken on the requested key and on the ‘next’ key. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value. If the index is unique then a regular S lock on the key.
    
  • If the key does not exist, then the ‘range’ lock is taken on the ‘next’ key both for unique and non-unique index. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.

• Range Predicate (key between the two values)
  
  
  
  • ‘range lock on all the key values in the range when using ‘between’
    
  • ‘range’ lock on the ‘next’ key that is outside the range. This is true both for unique and non-unique indexes. This is to ensure that no row can be inserted between the requested key and the one after that. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.

Let us consider two examples:

Example-1:

create table foo (c1 int)

go

insert into foo values (1)

insert into foo values (2)

insert into foo values (3)

insert into foo values (4)

insert into foo values (5)

create unique clustered index foo_ci on foo(c1)

set tran isolation level serializable

begin tran

select * from foo where c1 between 2 and 4

Once this query is executed, your locks will look as follows (I used sys.dm_tran_locks DMV available in SQL2005)

resourcetype resourcedesc         request_mode

------------ -------------------- ------------------------------------------------------------

S

S

S

1:8546       1:8546               IS

IS

(020068e8b   (020068e8b274)       RangeS-S (this is a range lock on value 2)

(0400b4b7d   (0400b4b7d951)       RangeS-S (this is a range lock on value 4)

(03000d8f0   (03000d8f0ecc)       RangeS-S (this is a range lock on value 3)

(0500d1d06   (0500d1d065e9)       RangeS-S (this is a range lock on value 5)

IS

(10 row(s) affected)

Since it is a unique index, we don’t really have to get a range lock on 5 but SQL Server takes it anyway. However for non-unique index, SQL Server needs to lock the key 5 for reasons described earlier.

Exanple-2:

If you issue the following query

begin tran

select * from foo where c1 = 4

In this case there will be no range lock taken as the index is unique. However, if the index is not unique then SQL Server will acquire range lock on key value 4 and 5 as follows

resourcetype resourcedesc         request_mode

------------ -------------------- ------------------------------------------------------------

S

S

S

1:8546       1:8546               IS

(060004b73   (060004b73a86)       RangeS-S (this is a range lock on value 4)

(0500b49e5   (0500b49e5abb)       RangeS-S (this is a range lock on value 5)

IS

In the above example, if I use the following query

begin tran

select * from foo where c1 = 0

A range lock will be taken on key value 1 both for unique and non-unique index.Thsi is because the key value '0' does not exist.

Sunil