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 (
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
User2 interact with each other? Does the operation started at
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?