Apolgies in advance if this has been discussed before, or is even fundamental enough for it to be outlined on, say, Wikipedia. I just felt that asking would be more efficient!
Anyway, I want to ask about transactions, from a theoretical point of view. In particular, say we have two (or more) users looking at a dataset and the following occur at the times indicated (tX):
t0 User1 starts a transaction
t1 User2 starts a transaction
t2 User1 runs a DML operation on the dataset
t3 User2 runs a different DML on the same dataset
t4 User1's operation finishes and is committed
t5 User2 commits
My question is: how do the DML operations run between User1 and User2 interact with each other? Does the operation started at t2 by User1 operate on the data as it was defined at the start of their transaction (i.e., at t0), and likewise for User2's operation, so that there is no interaction? Or, if say User1 committed their operation after User2's transaction had started but before they had started their operation, would the data User2 was looking at be updated respectively? Moreover, how is consistency maintained in the case of any conflicts?
