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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.