Post reply

Transactions Committed vs. Uncommitted Data

  • January 14, 2020 at 11:38 pm

    #3715456

    I am trying to understand how committed data works in a transaction.  I have 4 screenshots attached to document my process.

    1. File name = Tab 1 Uncommitted Data

      1. I update the last name to 'Elder' then do a SELECT query.  The change appears in the result set, but it is not committed because the COMMIT TRANSACTION line was commented out

    2. File name = Tab 2 Uncommitted Data

      1. I open a new connection (a new query tab) and run a SELECT query.  It continues to execute endlessly, which is expected.

    3. File name = Tab 1 Committed Data

      1. The COMMIT TRANSACTION line is no longer commented out.  I execute the entire transaction, then the SELECT statement.  The last name changed to 'Elder' still appears which is expected because it has been committed at this point.

    4. File name = Tab 2 Committed Data

      1. The SELECT statement is executed but it executes endlessly, which I did not expect at this point because the change has been committed.

    Here are my questions.

    1. After step one, why is it possible to query the uncommitted data if you are still in the same connection (same tab where the change was made)?
    2. After step 2, why is it all of a sudden not possible to query the uncommitted data if you are now in a new connection (different tab from where the change was made)?
    3. After step 4, since the data is committed at this point, I would think that a SELECT statement would execute and return the committed results, but instead it runs endlessly (like step 2).  Why does the SELECT statement run endlessly at this point, even though the data has been committed already?

     

    Update:  Since posting this, there were times when I was able to get step 4 to display the SELECT statement results without running endlessly.  I can't pintpoint exactly what I did in those cases.  It seems like might have something to do with running multiple statements at once, e.g. running COMMIT and SELECT at the same time.  Not sure if this is the cause of step #4 above, but it's merely speculation.

    Attachments:
    You must be logged in to view attached files.

  • January 15, 2020 at 1:34 am

    #3715475

    The problem in step 4 is that you actually have 2 transactions in step 3 but have only committed once.  In step 3, you run it once with COMMIT commented out.  That's an open transaction (assuming that it contained a BEGIN TRANSACTION).  Then, you uncomment the COMMIT and run step 3 again.  That starts a 2nd transaction and the COMMIT commits that second transaction, but not the first.  You either need to do a ROLLBACK or a COMMIT on the first run in step 3.

    --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)

  • January 15, 2020 at 10:35 pm

    #3715820

    I think I've got it now.  Thank you.

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

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