MVCC — multi-version concurrency control: Question bank

Before you start, make sure these words feel solid — every question leans on them:

Recall Vocabulary refresher (open only if a word feels shaky)

version chain ::: one logical row stored as several physical copies, each tagged with a birth-txid and a death-txid.
xmin ::: the transaction id (txid) that created this version — its "birth certificate".
xmax ::: the txid that deleted or superseded this version — its "death certificate"; ∞ means still alive.
snapshot ::: a frozen note of which transactions had committed at the instant a transaction began.
visible ::: a version I am allowed to read = I can see its birth and I can NOT yet see its death.

True or false — justify

Every answer must give a reason, never a bare "true/false".

MVCC lets a reader see a value that was never simultaneously the "latest" value in the database.

True — a reader sees the snapshot from its start time, so if newer committed versions exist, the reader deliberately reads an older one; the value it sees is consistent-as-of-start, not necessarily current.

Under MVCC, an UPDATE overwrites the old row in place.

False — an update writes a new version and stamps the old one's xmax; the old bytes stay until VACUUM reclaims them. In-place overwrite would destroy the version a concurrent reader still needs.

Because "readers never block writers", MVCC needs no locks whatsoever.

False — reader↔writer blocking is gone, but write↔write on the same row still serializes via a row lock or first-committer-wins; two versions cannot both claim to supersede the same parent.

A snapshot stores the actual data of every row at snapshot time.

False — a snapshot stores only which txids had committed (oldest running, next-to-assign, active set); the data itself is reconstructed on demand by applying the visibility rule to version chains.

Snapshot Isolation guarantees serializability.

False — SI still permits write skew: two transactions read overlapping data, write different rows, and jointly break a cross-row invariant no serial order would allow.

A version whose xmax is set is definitely dead and can be garbage-collected.

False — xmax being set only means some transaction deleted it; it is dead only when that deleter is committed and older than the oldest live snapshot's horizon, so no running transaction could still need it.

Assigning a txid to a transaction is the same as committing it.

False — a txid is handed out when a transaction starts writing; commit is a separate later event. Visibility depends on commit status, not merely on having a txid.

Two transactions reading the same row concurrently ever conflict under MVCC.

False — read↔read never conflicts under any concurrency scheme; both just read (possibly different) visible versions with zero coordination.

Spot the error

Each line states a flawed claim or reasoning; the answer names the flaw.

"T100 started before T101 committed, so T100 will see T101's write once T101 commits."

Wrong — visibility is decided by T100's snapshot, frozen at T100's start. T101 committing later does not retroactively enter T100's snapshot; T100 keeps seeing the old version.

"MVCC removes deadlocks entirely since readers don't wait."

Wrong — write↔write conflicts still take row locks, and two transactions grabbing two rows in opposite order can still deadlock. MVCC removes reader-caused waits, not lock deadlocks.

"A version with xmin in my snapshot's active set is invisible because it committed after me."

Wrong reason — it's invisible because at my snapshot instant that creator was still running; whether it committed before or after me is irrelevant, consistency demands I pretend it never ran.

"Write skew is caught by MVCC's write-write conflict detection."

Wrong — write skew writes disjoint rows, so there is no write-write conflict to detect; that is precisely why SI misses it and you need SSI or explicit locking.

"Keeping a long report transaction open is fine because it only reads."

Wrong — a long reader pins the GC horizon: VACUUM cannot remove any version the reader might still see, causing bloat and slow scans for everyone.

"The old version can be deleted as soon as the writing transaction commits."

Wrong — even after commit, older concurrent snapshots may still need the old version; it is reclaimable only once no active snapshot's horizon can reach it.

"Since xmax = ∞ means 'live', a row with xmax set to a txid is never visible to anyone."

Wrong — the deleter must itself be visible to you for the death to count; if the deleting txid is invisible to your snapshot, the row still looks alive to you.

Why questions

Why does the visibility rule require xmin to be committed, not merely assigned?

Because reading data from an uncommitted transaction would be a dirty read — that transaction might still abort, so its version is garbage to everyone else until commit.

Why is the deleter (xmax) logic the mirror image of the creator (xmin) logic?

A version is "gone for me" only if the deleting transaction is visible to me by the same three tests; so "alive for me" = "I can see the birth" AND "I can NOT yet see the death".

Why must a txid ≥ xmax_snap (the next-to-be-assigned id) be invisible?

Any such txid started after my snapshot was taken — it lies in my future, so honoring it would break the frozen consistent picture my snapshot promises.

Why does MVCC trade storage and background cleanup for concurrency?

Keeping old versions is what lets readers proceed without waiting; the cost is extra disk (dead versions) plus VACUUM to reclaim them — a deliberate space-for-throughput deal.

Why does pessimistic 2PL block reads while MVCC does not?

2PL guards the single current copy with locks, so a reader must wait for a writer's lock; MVCC keeps multiple copies, so the reader just grabs an older committed one.

Why can two transactions each pass their own constraint check yet jointly violate it (write skew)?

Each reads a frozen snapshot that does not contain the other's not-yet-visible write, so each believes the invariant still holds; combined, their disjoint writes break it.

Why does MVCC still need something like WAL and commit records?

Visibility asks "is this txid committed?"; that fact must survive crashes and be durable, which the commit log / WAL provides — MVCC handles isolation, not durability.

Edge cases

What does a reader see if the only version of a row was created by a transaction still in its active set?

Nothing — that version's creator is invisible (still running at snapshot time), so to this reader the row effectively does not exist yet.

A transaction updates a row it created earlier within the same transaction. What does it read afterward?

Its own uncommitted new version — a transaction always sees its own writes, even before commit; the visibility rule treats "my own txid" as visible to me.

What happens to visibility when the txid counter wraps around (runs out of numbers)?

Naive "older/newer" comparisons break, so ancient rows could suddenly look "future" and vanish; wraparound handling (freezing very old rows to a permanent-visible marker) prevents this catastrophe.

A row is deleted by an aborted transaction. Is it still visible to others?

Yes — the deleter never committed, so its xmax is invisible to everyone; the death "didn't happen", and the row stays alive.

Under ACID, which letter does MVCC most directly implement, and which does it not?

It directly serves Isolation (each transaction sees a consistent snapshot); it does not by itself provide Durability — that needs logging/WAL.

Two transactions read the same row and both UPDATE it; what is the outcome under Snapshot Isolation?

The second writer waits for the first; on the first's commit the second gets a serialization error (first-committer-wins), because both can't supersede the same version.

What if a snapshot's active set is empty when it is taken?

Then every already-committed version with xmin < xmax_snap is visible with no exceptions — the simplest case, common on a lightly-loaded system where no other transaction is mid-flight.