Post reply

Transaction Isolation question

  • September 11, 2013 at 3:24 pm

    #297729

    The isolation level of the database that I'm running the query on is READ COMMITTED.

    I ran query 1 and query 2.

    Query 1 is

    IF OBJECT_ID('tempdb..##my_name') IS NOT NULL

    BEGIN

    DROP TABLE ##my_name;

    END;

    CREATE TABLE ##my_name

    (

    id INT,

    first_name VARCHAR(20)

    );

    INSERT INTO ##my_name (id, first_name)

    VALUES (1, 'dexter');

    BEGIN TRAN

    UPDATE ##my_name

    SET first_name = 'derek'

    WHERE id = 1;

    Query 2 is

    SELECT * FROM ##my_name WITH (NOLOCK);

    SELECT * FROM ##my_name;

    My question is that I was expecting "SELECT * from ##my_name" to hang without generating results because I haven't ran COMMIT TRAN or ROLLBACK TRAN yet. But I'm getting results as dirty read.

    I'm little confused here. Can someone help me out?

  • September 12, 2013 at 12:12 am

    #1649580

    You should run the Query 2 on a different session (query window).

  • September 12, 2013 at 1:07 am

    #1649586

    You never block yourself. If you have just run a data modification, in that same session you can always see the data you modified.

    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

  • September 12, 2013 at 2:08 pm

    #1649869

    Feeling stupid.

    Thanks guys! Problem solved..

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

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