0

Let's say I have two transactions A and B. Transaction A starts first. then transaction B starts, inserts some data, and commits. So from transaction A (not committed yet), I can see data were committed by transaction B. Since transaction A starts before transaction B, its XID would be smaller than the XID of transaction B. From my understanding, transaction A can only see rows that are modified or inserted by transactions whose XID is smaller than transaction A.

read committed

In the above example (read committed isolation level), the XID of the current transaction is 741, why it can see data was committed by the transaction whose XID is 742?

Sorry, I think my example wasn't clear enough. I started two transactions A and B concurrently, I let transaction B update and insert some data, and transaction A can see those data after transaction B commit. So, I wonder how MVCC rules work under read committed isolation level.

0

1 Answer 1

3

why it can see data was committed by the transaction whose XID is 742

Because that is what READ COMMITTED means. In addition to the transaction having a txid (if it has ever needed one), each statement has a snapshot. The snapshot changes from statement to statement within a READ COMMITTED transaction (at least it will if there is concurrent activity), while it will be the same throughout for REPEATABLE READ.

2
  • thank you for the answer! I just want to add a resource that also explains my question, section 5.5 of Concurrency Control. Aug 27, 2023 at 8:52
  • The answer is correct, but I wouldn't say that's what READ COMMITTED means. Per SQL standard, READ COMMITTED means that you don't read any uncommitted data. The fact that concurrently committed transactions become visibly immediately is PostgreSQL's implementation. It happens to match the name of the isolation level nicely, but it is not what the standard committee had in mind. Aug 28, 2023 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.