BUG: READPAST Locking Hint Returns an Incorrect Number of Rows (297466)

CAUSE

The READPAST hint does not read past the row identifier (RID) lock but does read past the KEY lock.

Without a clustered index the UPDATE statement takes a row identifier type of lock. With a clustered index, the UPDATE statement takes a KEY lock. You use a row identifier lock to lock a single row within a table. A KEY lock is a row lock within an index. You use a KEY lock to protect key ranges in serializable transactions.

MORE INFORMATION

The first SELECT statement from Connection 2, in the following example, is supposed to return the same result as the second SELECT statement (that is, 1 row).

Example

Connection 1
---------------

use tempdb
go
drop table lock_readpast
go
create table lock_readpast (c1 int not null, c2 int not null, c3 char(10) not null)
go
insert lock_readpast values (0, 1, 'a')
insert lock_readpast values (0, 2, 'b')
insert lock_readpast values (0, 3, 'c')
go
create index idx_c2 on lock_readpast (c2)
go

begin tran
update lock_readpast set c1 = 1 where c2 = 2
--rollback tran

Connection 2
---------------

use tempdb
go
set statistics profile on
go
select * from lock_readpast with (READPAST, INDEX(idx_c2)) WHERE c2 in (2, 3)
select * from lock_readpast with (READPAST, INDEX(idx_c2)) WHERE c2 in (2, 3) order by c2 desc
select * from lock_readpast with (READPAST, INDEX(idx_c2)) WHERE c2 in (1, 2, 3)
go
set statistics profile off

BUG: READPAST Locking Hint Returns an Incorrect Number of Rows (297466)

SYMPTOMS

CAUSE

WORKAROUND

STATUS

MORE INFORMATION

Example