Intuition The one core ideaA database that many people use at once faces a fight: readers and writers colliding over the same data. MVCC ends that fight by keeping several dated copies of every row, so each reader is handed a photograph of the data as it looked the instant they started — nobody has to wait, and nobody sees a half-finished change.
This page assumes you know nothing . Before you can read the parent note MVCC — multi-version concurrency control , you must own every word and symbol it throws at you. We build them one at a time, each resting on the one before.
A row is one record in a table — one line of a spreadsheet. Example: the line balance = 100 for one bank account. When we say "update the row," we mean change that stored value.
A transaction is a bundle of database work that must happen all-or-nothing . Either every step lands, or none does. Think of moving money: "subtract from A" and "add to B" are one transaction — you never want one without the other.
The picture below is the whole stage on which MVCC performs: many transactions, one shared table.
Intuition Why "all-or-nothing" matters hereIf a transaction is only half done , its data is not real yet. MVCC's entire visibility machinery exists to answer one question: has this change fully happened (committed) or not? So we must nail "transaction" before anything else.
The all-or-nothing promise is one of the ACID Properties (atomicity). MVCC is the mechanism that makes the isolation letter of ACID cheap.
Definition Transaction id (txid)Every transaction, when it begins, is stamped with a number that only ever counts up : 100, 101, 102, … This number is its txid (transaction id).
Intuition Why a counter, and why must it only increase?A pure counter gives us a cheap notion of "who came first." If txid 100 < txid 101, then transaction 100 started earlier . That single comparison — a plain < — is what lets us reason about time without clocks. This is exactly the tool MVCC needs, and nothing fancier: not timestamps, not wall-clock time, just "smaller number = older."
We write comparisons like 101 < 100 no . Keep that literal reading in mind; every visibility check is just such a question.
Recall What does< mean between two txids?
"Started earlier." If a < b a b
Common mistake "A big txid started later, so it must finish later."Why it feels right: later start seems like later finish.
The fix: start order ≠ finish order. Transaction 101 can commit before 100 does. txid only tells you start order. That gap is precisely why MVCC also tracks commit status separately (Section 5).
(The counter is finite and eventually wraps — see Transaction IDs and Wraparound — but for these foundations, treat it as counting up forever.)
Definition Commit / AbortA transaction commits when it finishes successfully — its changes become permanent and real for others. It aborts if it fails or is cancelled — its changes are thrown away as if they never happened.
Intuition Why this is a separate fact from txidtxid says when you started . Commit says whether your work counts yet . These are independent: a transaction can have a small (old) txid but still be un-committed right now. MVCC must ask both questions about any change: "who made it?" (txid) and "did it stick?" (committed?).
Dirty data = a change from a transaction that has not committed (and might still abort). Reading it is dangerous: it may vanish. A core job of the visibility rule is to hide dirty data.
Now we can define the two symbols the parent leans on hardest.
==xmin== is the txid that created a particular version of a row. It is the row-version's birth certificate : "transaction #101 made this."
==xmax== is the txid that deleted or replaced this version. It is the death certificate : "transaction #101 killed this old version." If the version is still alive (nobody has superseded it), we write x ma x = ∞
∞ ∞ no , since nothing is smaller than the future. Picking ∞ same comparison logic work for live and dead versions — no special case needed.
The figure shows a single logical row as a chain of versions , each with its birth and death tags. This chain is the "version chain" the parent note names.
Recall A version tagged(xmin=101, xmax=∞) means what?
Question ::: Created by transaction 101, and still alive (never replaced), because its death tag is infinity.
A version is one stored copy of a row's value, carrying its own xmin and xmax. Updating a row does not overwrite it — it appends a new version and marks the old one as dead (sets the old version's xmax).
A version chain is all the versions of one logical row, oldest→newest, linked together. The row balance might be: (100, xmin=90, xmax=101) → (200, xmin=101, xmax=∞).
Intuition Why keep the old copy at all?This is the whole trick of MVCC. If we overwrote , a reader mid-flight would suddenly see the new value — inconsistent. By keeping the old version alive until no one needs it, an in-progress reader can safely read the old copy while the writer builds the new one. Storage cost buys concurrency. The leftover dead versions are later swept by VACUUM and Garbage Collection .
This is the subtlest symbol group, so we build it slowly.
The active set is the list of txids that were still running (not yet committed or aborted) at the instant a snapshot was taken. These are the "in-progress" transactions.
A snapshot is a frozen note of "who had committed by now." It captures three things at the chosen instant:
snapshot = ( x min s na p , x ma x s na p , active set )
x min s na p oldest still-running txid.x ma x s na p next txid to be handed out — i.e. the boundary of the future.active set = who was mid-flight (from above).
Intuition Why these exact three numbers?To decide "can I see change #N?" a transaction only needs to sort every txid into three buckets:
Definitely in my past and done — txid < x ma x s na p not in the active set → visible.Was running when I looked — txid in the active set → pretend it never happened.In my future — txid ≥ x ma x s na p
These three numbers are the minimum information to draw those two boundary lines. Nothing less works; nothing more is needed.
The figure draws the txid number-line with those boundaries as the walls of your visible world.
x ma x s na p Why it feels right: "max" sounds like "biggest so far."
The fix: x ma x s na p next txid to be assigned — the first id of the future . Any transaction with txid ≥ x ma x s na p
Recall Why is a txid≥ x ma x s na p after the snapshot was taken — it lives in the transaction's future, which by definition has not happened yet for this reader.
The visibility rule glues small tests together with logic. Define them once:
∧ AND : true only if both sides are true.∨ OR : true if at least one side is true.¬ NOT : flips true↔false.
Intuition Why we need these"Is this version alive for me?" is really two questions joined: "Can I see its birth?" AND "I can NOT see its death yet." That sentence is literally ( see birth ) ∧ ¬ ( see death )
Recall Read aloud:A ∧ ¬ B
Isolation = the illusion that your transaction is running alone , even though many run at once. MVCC provides isolation by handing each transaction its private snapshot.
A schedule of concurrent transactions is serializable if its outcome equals some order of running them one-at-a-time . This is the gold standard — see Serializability .
Definition Snapshot Isolation (SI)Snapshot Isolation is the guarantee MVCC naturally gives: every transaction reads from one consistent snapshot. It is strong but not fully serializable — it allows an anomaly called write skew (parent's Example 3). See Snapshot Isolation .
A lock is a "do not touch" flag one transaction places on data, forcing others to wait. The old, pessimistic approach — Two-Phase Locking (2PL) — locks everything , including for readers. MVCC's win is dropping locks for reader↔writer clashes, though write↔write on the same row still uses a lock . The broader trade-off is Optimistic vs Pessimistic Concurrency Control .
Intuition Where MVCC sitsMVCC is the optimistic-leaning middle path: readers proceed without locks (optimistic), while conflicting writers still block (a touch of pessimism). Durability of all this is handled separately by Write-Ahead Logging (WAL) .
snapshot xmin xmax active set
Every box on the left must be solid before the visibility rule on the right makes sense — and that rule is the heart of MVCC.
Self-test: cover the right side and answer each.
What does a txid tell you, and what does it not tell you? It tells start order (smaller = older); it does NOT tell commit/finish order.
What is xmin? The txid that created this row version — its birth certificate.
What is xmax, and what does ∞ mean for it? The txid that deleted/replaced this version; ∞ means still alive, no death yet.
Why does an UPDATE keep the old version instead of overwriting? So in-flight readers can still read a consistent old value without waiting — the core of MVCC.
Name the three parts of a snapshot. x min s na p x ma x s na p
What is x ma x s na p The next txid to be assigned; anything ≥
Translate A ∧ ¬ B A is true AND B is not true.
What is dirty data and why hide it? A change from an un-committed transaction; it may still abort and vanish, so reading it risks inconsistency.
What does "serializable" mean? The result equals some strictly one-at-a-time ordering of the transactions.
Which conflict does MVCC still resolve with locks? Write↔write on the same row.
