I am trying to understand how committed data works in a transaction. I have 4 screenshots attached to document my process.
- File name = Tab 1 Uncommitted Data
- 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
- File name = Tab 2 Uncommitted Data
- I open a new connection (a new query tab) and run a SELECT query. It continues to execute endlessly, which is expected.
- File name = Tab 1 Committed Data
- 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.
- File name = Tab 2 Committed Data
- 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.
- 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)?
- 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)?
- 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.