Post reply

Transaction lvel isolation with INSERT/UPDATE

  • June 14, 2006 at 12:23 pm

    #190949

    Good afternoon-

    I know that setting the transaction level isolation to READ UNCOMMITTED allows dirty reads, etc...what I am not sure of is if this means anything when using an INSERT INTO...SELECT or UPDATE statement vs an ordinary SELECT...in other words, I guess, does the INSERT INTO...SELECT and/or UPDATE ignore the isolation level?

    Thanks!

    JM

  • June 14, 2006 at 3:36 pm

    #643812

    According to BOL:

    "READUNCOMMITTED, and NOLOCK, cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement."

  • June 17, 2006 at 7:09 am

    #644455

    I'd recommend that, whatever you do, you do NOT use "Set Transaction Isolation Level" in updatable views... don't know when it will happen but you will eventually run across an isolation level conflict, if you do.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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