Post reply

Please confirm

  • August 31, 2007 at 7:49 am

    #121447

    I have the following sample script:

    BEGIN TRAN

    INSERT INTO a.tbl1 (col1,col2) VALUES ('b',2)

    ROLLBACK

    COMMIT TRAN

     

    If I only run the first two lines, they were successul. But I could not see any data using SELECT * FROM a.tbl1 (timeout). After executing either ROLLBACK or COMMIT TRAN, I could view data using SELECT clause.

    Please confirm my observation, and explain why it is so (blocked)?

    Many thanks for any input in advance.

  • August 31, 2007 at 7:53 am

    #730777

    Your insert statement is acquiring a lock on your table, which isn't released because the transaction hasn't been committed or rolled back.  That's why you get a timeout when your try to select from the table.  As soon as you execute the COMMIT or ROLLBACK statements, the lock is released and your select statement works.

    John

  • August 31, 2007 at 8:10 am

    #730787

    if you wish to view the data that has been added to the table but not yet committed you will need to execute a "dirty read" ie. select * from table with (nolock)

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

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