Microsoft KB Archive/297466

= BUG: READPAST Locking Hint Returns an Incorrect Number of Rows =

Article ID: 297466

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q297466



BUG #: 353808 (SHILOH_BUGS)



SYMPTOMS
If the first row passed to the Bookmark Lookup operator is locked, a query that contains the READPAST locking hint returns an incorrect number of rows. Refer to the &quot;More Information&quot; section for an example.



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.



WORKAROUND
To work around this behavior, add a clustered index to the table.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



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

Keywords: kbbug kbpending KB297466

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.