Databases
MVCC and isolation: multiple-choice review
Crux Multiple-choice synthesis across the MVCC unit — snapshot visibility, isolation-level tradeoffs, write skew vs lost update, bloat pinning, and XID wraparound.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each one mirrors a call you make in a real incident — which isolation level, why autovacuum is stuck, what the 40001 actually means — not a definition to recite.
Goal
Confirm you can connect snapshot visibility, isolation-level choice, the anomalies each level allows, bloat pinning, and wraparound — the synthesis the seven lessons built toward.
Quiz
Completed
Connection B runs this under READ COMMITTED while connection A commits an UPDATE between the two statements: ```sql -- B: BEGIN; SELECT balance FROM accounts WHERE id = 42; -- returns 100 -- (A commits: UPDATE accounts SET balance = 0 WHERE id = 42) SELECT balance FROM accounts WHERE id = 42; -- returns ? ``` What does the second SELECT return, and why?
Heads-up That is REPEATABLE READ behaviour. READ COMMITTED re-snapshots at the start of every statement, so the second read sees the newly committed value — the classic non-repeatable read.
Heads-up Postgres raises nothing here. A concurrent commit is invisible to the running statement and simply becomes visible to the next one under READ COMMITTED.
Heads-up UPDATE never overwrites in place; it writes a new tuple and marks the old dead. The fresh snapshot returns the new live version, not NULL.
Quiz
Completed
An app reads a row, computes a new value in code, and writes it back. Two requests race: ```sql -- both sessions, concurrently: BEGIN; SELECT stock FROM items WHERE id = 7; -- both read 10 -- app computes 10 - 1 = 9 UPDATE items SET stock = 9 WHERE id = 7; COMMIT; ``` Under the default isolation level the final stock is 9, not 8. What is this and what is the minimal fix?
Heads-up No new rows are involved; both sessions touch the same existing row. A phantom is a row that enters a re-evaluated predicate, not a clobbered value.
Heads-up Write skew requires the two transactions to modify different rows. Here they modify the SAME row with the same value, which is a lost update, not write skew.
Heads-up MVCC gives each transaction a valid snapshot but does not serialize blind writes to the same row at READ COMMITTED; one update is silently lost.
Quiz
Completed
A doctors table has the invariant 'at least one doctor on_call'. Two transactions run under REPEATABLE READ: ```sql -- T1: -- T2 (concurrent): SELECT count(*) FROM doctors SELECT count(*) FROM doctors WHERE on_call; -- 2 WHERE on_call; -- 2 UPDATE doctors SET on_call=false UPDATE doctors SET on_call=false WHERE name='alice'; WHERE name='bob'; COMMIT; COMMIT; ``` Both commit; now zero doctors are on call. What happened and which level prevents it?
Heads-up Each UPDATE touches a different row (alice vs bob), so neither overwrites the other. There is no row-version conflict for REPEATABLE READ to catch; the anomaly spans the read set, which is write skew.
Heads-up Snapshot isolation prevents non-repeatable and phantom reads but explicitly allows write skew. This scenario is the canonical write-skew example.
Heads-up Neither transaction re-reads a row that changed; both keep a frozen snapshot. The bug is two independent writes that jointly violate an invariant — write skew.
Quiz
Completed
Autovacuum has run for 30 minutes on a 200 GB orders table and logs '287 million are dead but not yet removable, oldest xmin: 28391456'. n_dead_tup is not dropping. What is the root cause and the first thing you check?
Heads-up The cost limit only throttles how fast autovacuum scans; it does not change what it is ALLOWED to remove. The log says the tuples are not removable, which is an oldest-xmin pin, not a speed problem.
Heads-up VACUUM FULL reclaims file space; it is not a prerequisite for regular autovacuum. Regular vacuum is working — it simply cannot remove tuples still visible to the pinned oldest xmin.
Heads-up Wraparound triggers a distinct emergency anti-wraparound vacuum with its own log wording. 'Dead but not yet removable' with an oldest-xmin value is a snapshot pin.
Quiz
Completed
A bank's two-account transfer reads both balances, debits one, credits the other. The DBA proposes READ COMMITTED with SELECT ... FOR UPDATE on both rows (locked in a consistent order) instead of SERIALIZABLE, which is 5x slower at peak. Is that correct?
Heads-up Write skew requires acting on rows you do NOT modify. A point transfer reads and writes the same two rows, so FOR UPDATE on both is functionally equivalent to SERIALIZABLE here — reserve SSI for invariants spanning unmodified rows.
Heads-up REPEATABLE READ + retry is pathological on hot accounts: every concurrent transfer to a popular account aborts and retries, collapsing throughput, with no correctness gain over RC + FOR UPDATE for the two-row pattern.
Heads-up FOR UPDATE does not disable MVCC; it takes a row lock so the second transaction blocks until the first commits, then re-reads the latest version. MVCC visibility still applies.
Quiz
Completed
A workload does heavy SELECT ... FOR KEY SHARE (the implicit lock from foreign-key checks) and hits an autovacuum wraparound warning much sooner than its transaction throughput would predict. Which counter is the constraint?
Heads-up FOR KEY SHARE creates MultiXactIds, not extra XIDs. A FK-heavy workload stresses the MultiXact space specifically, which can wrap long before the XID counter does.
Heads-up CLOG pages are trimmed as old transactions are frozen; running out is not the failure mode. Shared locks pressure the separate MultiXact wraparound clock.
Heads-up Shared locks do not consume connections. MultiXactIds are allocated per locked row regardless of connection count, and it is their wraparound that fires the warning.
Recap
The through-line of the unit is one visibility rule and the choices stacked on it. Snapshots decide what each transaction sees (per-statement under READ COMMITTED, per-transaction under REPEATABLE READ). The isolation level decides which anomalies you own: RC leaves lost updates to the app, REPEATABLE READ raises 40001 on concurrent row updates but still allows write skew, SERIALIZABLE adds SSI to catch write-skew cycles. The storage tax — dead tuples — is reclaimed by autovacuum only up to the global oldest xmin, so a long transaction or orphan slot is the first suspect for runaway bloat. And the 32-bit XID and MultiXact counters both demand freezing before wraparound. Pick the weakest level that still protects your invariant, then defend the bound.