Post reply

Locking in “Read Committed Snapshot”

  • August 9, 2013 at 3:36 am

    #296783

    IF update command is run on a table with "Read Committed_Snapshot" isolation level and Commit is pending

    eg:

    1) update table1 set col1 = col1 + 1 where PKcol < 3

    2) update table1 set col1 = col1 + 1 where PKcol = 3

    3) update table1 set col1 = col1 + 1 where NonPKcol < 3

    4) update table1 set col1 = col1 + 1 where NonPKcol = 3

    5) update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

    (In above case PKcol is primary key in table and NonPKcol is a non-primary key)

    then whether Update is locked for only rows satisfying 'where' condition ? (is it based on value or index or Primary column ?)

  • August 9, 2013 at 6:39 am

    #1640121

    IT researcher (8/9/2013)

    then whether Update is locked for only rows satisfying 'where' condition ? (is it based on value or index or Primary column ?)

    As I said in the other thread here, not necessarily. Page locks can be taken and row locks can escalate, which can lock many rows at a time. In the examples above, since these are simple queries that should complete in milliseconds, why does it matter? RCSI isn't a panacea to blocking, it only stops readers and writers blocking each other, not writers blocking other writers. It doesn't change the behaviour of update locks.

  • August 12, 2013 at 3:51 am

    #1640495

    When does page lock and row lock is taken?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply