Why SQL Server unexpectedly stops issuing range locks
I am puzzled by following situation: issuing of range locks surprisingly stops when exceeded some number of rows in accessed table.
Brief description: There is one table with unique clustered index; except PK table contains not unique column. We trying to select all values from this table with serializable (it doesn't happens with repeatable read IL) isolation level. Everything is going as expected (as msdn says: the number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.) until some number of rows limit is exceeded.
I think it will be better to example provide code:
if (exists (select * from information_schema.tables where table_name = 'T')) drop table T create table T ( id int not null, val int not null primary key (id) ) declare @numOfRow int = 10000 -- after 6232 range locks doesn't issued begin tran declare @i int = 0 while @i < @numOfRow begin insert into T values (@i, @i) set @i = @i + 1 end commit --set transaction isolation level repeatable read set transaction isolation level serializable begin tran select * from T -- with (holdlock) select * from sys.dm_tran_locks where request_session_id = @@SPID commit
For me if I set @numOfRow equals to 6233 and greater no range lock will be issued.
@@VERSION = Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
It does acquire range locks.
Because each lock consumes memory lock escalation can kick in though typically at some point after 5,000 locks are acquired.
Lock escalation results in fewer locks at a less granular level.
To see this you can add dbcc traceon(1200,3604,-1)
Remember to turn the flags off again with dbcc traceoff(1200,3604,-1)