Read Committed & READ_COMMITTED_SNAPSHOT ON

• T3st0

  Old Hand

  Points: 394

  More actions

  February 22, 2013 at 1:06 pm

  #274471

  Hi All,

  Just browsing the MSDN and trying to educate myself on the different isolation levels I came across something that I am a little confused about.

  URL:

  READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

  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.

  In my test database that I created the isolation level was set to read committed and I turned on the READ_COMMITTED_SNAPSHOT.

  I wrote a simple update statement:

  UPDATETABLENAME WITH (READPAST)

  SETCOLBLAH = 'BLAH'

  WHERE

  SOMECOL = 1

  It did not raise an error so what exactly happens behind the scenes if your isolation level is read committed, and the read_committed_snapshot database option is set to ON and you use (READPAST) table hint in your query?

  Thanks a lot!