The munim never erases. He writes a new line.
Each write adds a version stamped with its transaction. Your snapshot, a token taken at the door, picks the newest version finished before it.
Does MVCC mean no locks?
Bad: No locks anywhere
So two payments to one wallet don’t wait either.
Good: Writers still block writers
Only one version can follow the current one. The second writer waits.
How every MVCC engine works
- Stamp each version with its writer’s ID
- Snapshot: who had committed when you started
- Read the newest version your snapshot can see
- Remove replaced versions no live snapshot can read
Where do the old versions live?
PostgreSQL writes a whole new tuple into the table. InnoDB and Oracle change the row in place and copy old values to undo.
Both designs keep history. Who pays for it?
Who pays
PostgreSQL charges the writer, a new tuple plus an entry in every index unless HOT. InnoDB charges old readers, who walk undo.
36M
old versions a 2-hour report pins
A Flipkart-style sale makes 5,000 order-row updates a second. Over 7,200 s, clean-up can remove none of the versions they replace.
A long query needs old versions. PostgreSQL and InnoDB keep them for it and let storage grow.
Oracle’s choice
Oracle may reuse that undo instead. The long query then fails with ORA-01555, snapshot too old.
Remember
Readers never wait for writers, or writers for readers. Keep transactions short so old versions can be cleaned up.