locked
STORED PROCEDURE set transaction isolation level read uncommitted RRS feed

  • Question

  • Hi to all of you!!

    I have a question about the scope and behavior of the sentecen "set transaction isolation level read uncommitted" inside of an SP.
    This is the example of an SP:

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    create proc myStoredProcedure
    ([list of variables],
    [list of variables],
    [list of variables])

    as

    set transaction isolation level read uncommitted

    exec OtherStoredProcedure
    [list of variables] = [value],
    [list of variables] = [value],
    [list of variables] = [value]

    return 0
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Now my question:
    The sentence: set transaction isolation level read uncommitted
    affects the other SP "OtherStoredProcedure" even though is inside of the first one SP "myStoredProcedure".

    Thanks in advance for your help.

    Thursday, August 20, 2009 2:07 PM

Answers

  • Generaly Isolation level is on the connection so it remains one and same for the connection until you change it , but in your case ( setting in Store procedure)
    according to Books Online
     "If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch."

    So for your question :If you do not change isolation level in OtherStroredProcedure it will be "read uncommited" as set per MyStoredprocedure.

    Ivan
    • Marked as answer by minerodo Thursday, August 20, 2009 3:43 PM
    Thursday, August 20, 2009 2:32 PM