Post reply

Changing the isolation level without wrapping code in BEGIN / COMMIT TRAN

  • March 6, 2019 at 10:12 am

    #374075

    Hi all,

    My question is whether or not I have to wrap my SP code in a transaction to take the "SET TRANSACTION ISOLATION LEVEL ..." into effect? Examples:

    CREATE PROCEDURE Prc1
    AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    BEGIN
    SELECT Col1 FROM Tbl1
    SELECT Col2 FROM Tbl2
    END

    -- VS

    CREATE PROCEDURE Prc2
    AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    BEGIN
    BEGIN TRANSACTION
    SELECT Col1 FROM Tbl1
    SELECT Col2 FROM Tbl2
    COMMIT TRANSACTION
    END

    Thanks!!

    __________________________
    Allzu viel ist ungesund...

  • March 6, 2019 at 12:10 pm

    #2023704

    Have you tested the code both ways to see which works?

  • March 6, 2019 at 12:30 pm

    #2023713

    You don't need to do an explicit transaction.  Once you put the level into effect, it will stay in effect until/unless you explicitly change/override it.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT Col1 FROM Tbl1 --RU in effect
    SELECT Col2 FROM Tbl2 --RU in effect
    --as an example
    SELECT Col3 FROM Tbl3 WITH (TABLOCKX)  --RU NOT in effect because you explicitly overrode it
    SELECT Col4 FROM Tbl4 --RU in effect

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 12, 2019 at 5:42 pm

    #2024283

    Thanks for the replies. I tested with repeatable read and did a waitfor delay 30 seconds as the last statement in both cases - then I realized without wrapping the statements in a transaction they would get committed one by one as separate transactions (and finally the waitfor delay statement), so it was a stupid way of doing it because the locks were already released for a given statement in the SP when I queried the related DMV.

    What's the best way to test this? If I wanted to look at what type of locks get acquired in both situations? For instance repeatable read with 5 simple statements in each SP - one with explicit transaction, one without? Thank you

    __________________________
    Allzu viel ist ungesund...

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

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