Post reply

Does READ_COMMITTED_SNAPSHOT ignore ROWLOCK hints?

  • November 22, 2012 at 7:58 am

    #151123

    Hi,

    I'm planning to change the isolation mode of a DB that is in READ_COMMITTED Isolation mode to READ_COMMITTED_SNAPSHOT. On the DB (vendor supplied) there is a Stored Procedure that has ROWLOCK hints in, if I change the isolation mode to READ_COMMITTED_SNAPSHOT will these ROWLOCK hints now be ignored or would I need to remove them from the Stored Procedure?

  • November 22, 2012 at 11:31 am

    #1561692

    Should be OK because ROWLOCK is not an isolation level hint, it's a lock granularity hint. It says 'when you take locks, take at the row level not page or table'

    The snapshot isolations don't lock except for updates, so for reads there are no locks to take and hence the rowlock hint is meaningless (not ignored, just meaningless in the context). For writes, locks still have to be taken so the rowlock hint there will lead to those locks been taken at the row level, not page or table.

    Now, if you had any isolation level hints (nolock/readuncommitted, repeatableread, serializable) those would override the default isolation level

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 23, 2012 at 8:50 am

    #1561884

    Thank you Gail, very good explanation.

  • November 23, 2012 at 9:08 am

    #1561887

    Do feel free to test it out though

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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