none
Readpast hint and READ_COMMITTED_SNAPSHOT option

    Question

  • what is written in msdn (http://msdn.microsoft.com/en-us/library/ms187373.aspx):

    The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true.

    • The transaction isolation level of the session is READ COMMITTED.

    • The READCOMMITTED table hint is also specified in the query.

    Does it mean that I can't use readpast hint for update if READ_COMMITTED_SNAPSHOT is set to ON and transaction isolation level is readcommitted? I've checked it and readpast works fine, but it shouldn't in accordance ith msdn.

    Sunday, April 28, 2013 6:19 PM

Answers

  • No.  The BOL is currently wrong here.

    The READPAST hint _can_ be specified in an RCSI database, but in the READ COMMITTED isolation level or with a READCOMMITTED lock hint it may not have the intended effect, as the query will not skip locked rows, but will instead read the consistent version from the version store.  It would be more accurate to say that READPAST should be combined with a table hint that requires locks, like UPDLOCK or READCOMMITTEDLOCK in a SNAPSHOT transaction or an a READ COMMITTED transaction in a database with the READ COMMITTED SNAPSHOT option set.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, April 28, 2013 7:54 PM

All replies

  • The text is not wholly clear, but the last sentence clears it up:

    To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query.

    Here is an example to illustrate. Run this in one window:

    CREATE TABLE  mytable (a int NOT NULL PRIMARY KEY,
                           inuse bit NOT NULL DEFAULT 0)
    go
    CREATE INDEX mytable_ix ON mytable(a) WHERE inuse = 0
    go
    INSERT mytable(a) VALUES (1)
    INSERT mytable(a) VALUES (2)
    INSERT mytable(a) VALUES (4)
    INSERT mytable(a) VALUES (3)
    go
    UPDATE mytable SET inuse = 1 WHERE a = 1
    UPDATE mytable SET inuse = 1 WHERE a = 3
    BEGIN TRANSACTION
    UPDATE mytable SET inuse = 1 WHERE a = 2
    go

    Now in a second window, run:

    select MIN(a) from mytable WITH (READPAST) WHERE inuse = 0

    This returns 2. Whether this is correct or not is the eye of the beholder, but since you in most cases uses READPAST to implement some form of queue, it is very likely that using the value 2 will cause you grief later on. If you instead run:

    select MIN(a) from mytable WITH (READPAST, READCOMMITTEDLOCK) WHERE inuse = 0

    You will get back 4.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 28, 2013 7:11 PM
  • No.  The BOL is currently wrong here.

    The READPAST hint _can_ be specified in an RCSI database, but in the READ COMMITTED isolation level or with a READCOMMITTED lock hint it may not have the intended effect, as the query will not skip locked rows, but will instead read the consistent version from the version store.  It would be more accurate to say that READPAST should be combined with a table hint that requires locks, like UPDLOCK or READCOMMITTEDLOCK in a SNAPSHOT transaction or an a READ COMMITTED transaction in a database with the READ COMMITTED SNAPSHOT option set.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, April 28, 2013 7:54 PM