Post reply

Read Committed Isolation Level

  • March 3, 2012 at 4:58 am

    #1454432

    Not just because of the isolation level. Read committed releases the shared locks on rows read after the read completes, but also read locks are shared, so two sessions can read for the same row at the same time and both see that it's not there, then both insert it. You could switch to repeatable read or serialisable, but then your code would be very susceptible to deadlocks.

    A better pattern (that doesn't even need a transaction) would be

    Insert into table (<columns>)

    select <values>

    WHERE NOT EXISTS (<check for the row existing>)

    and to be paranoid, put a updlock hint in the EXISTS subquery.

    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

  • March 3, 2012 at 7:00 am

    #1454436

    Got it. Thank you very much.

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

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