Snapshot Isolation with MVCC — Versions, Visibility, Cleanup Diagram

Snapshot isolation never updates a row in place: an update becomes a delete of the old version plus an insert of the new one
Each row version carries inserted_by and deleted_by transaction IDs; the deleted row is only marked, then removed later
All versions of a row live in the same heap and form a linked list, so a query can walk from version to version
Readers never block writers and writers never block readers — the key difference from two-phase locking

At start, the transaction lists all transactions still in progress; their writes are ignored even if they commit later
Writes from any later transaction ID are ignored, committed or not; writes from aborted transactions are ignored too
So transaction 12 reads balance $500 although transaction 13 already changed it to $400
A long-running transaction keeps reading its old snapshot while newer versions pile up beside it

An index entry points to one version of a row; the query walks the chain until a visible, matching version is found
Garbage collection removes versions no transaction can see any more, along with their index entries
PostgreSQL calls snapshot isolation repeatable read, Oracle calls it serializable, and MySQL's repeatable read is weaker
CouchDB, Datomic, and LMDB use immutable copy-on-write B-trees: each write makes a new root, and that root is the snapshot