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 
        insert into T
        values (@i, @i)

        set @i = @i + 1

--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

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)

Need Your Help

read a file from a specific line php

php preg-match text-files lines

I try to read some specific lines in text file.

visible binding not working properly

javascript html knockout.js

I am currently working on a project where I want a button to register the number of clicks and show a message when the user clicks on the button first time. Here is what the code looks like. Pleas...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.