Lesson 29. Multiversion Concurrency Control

Mission link: A report's query and a payment's update land on the same row at the same moment more often than a code review imagines, and an engineer who does not know why the report never stalls, or what its answer was a snapshot of, will misdiagnose the next stale-looking number or the next update that vanished.
Primary source: PostgreSQL, 13.1 Introduction
Prerequisites: Lesson 4, Lesson 28

Warm-up

1. ▢ Lesson 28 established that a transaction's atomicity promise means every statement inside it takes effect together or none do, as if it had never run. Given that, when a transaction updates a row and is then rolled back, what does "as if it never ran" require of what a later reader sees?

Know this

This stage writes, so it uses its own table:

CREATE TABLE accounts (
    id      int PRIMARY KEY,
    owner   text NOT NULL,
    balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'ada', 100.00), (2, 'grace', 100.00);

The mechanism, from the system columns

Every row carries three columns nothing in that CREATE TABLE asked for: xmin, the transaction that wrote this version; xmax, the transaction that ended it, zero when nothing has; and ctid, its literal position on disk.

SELECT id, xmin::text, xmax::text, ctid::text FROM accounts ORDER BY id;
-- one xmin shared by both rows, the INSERT that created them; xmax zero on both, since neither has ever been ended

Now inside a transaction:

BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT id, xmin::text, xmax::text, ctid::text FROM accounts WHERE id = 1;
-- a new xmin, naming this UPDATE, and a new ctid: a second, physically different row now answers for id 1
ROLLBACK;
SELECT id, xmin::text, xmax::text, ctid::text FROM accounts WHERE id = 1;
-- xmin and ctid both back to what they were before this transaction ever touched the row

Every one of those values differs on a different run, so what matters is the shape: the UPDATE did not change the row that was already there. It left that row alone and wrote a second one, with a new xmin naming the transaction that wrote it, and marked the first row's xmax with that same transaction's id, meaning "this version ends here, once that transaction commits." Which version a reader is shown, while both sit in the table, depends only on whether the transaction named in xmax has committed. ROLLBACK means it never does, so the second row becomes dead weight nobody will be shown, and the first row's ctid is exactly what it always was, since nothing had to move for a version that was going to be discarded anyway.

One detail worth being exact about: ROLLBACK does not reset the first row's xmax to empty; it still names the transaction that tried to end it. What changed is not the mark but what it means: a reader checks whether the named transaction ever committed, and an aborted one counts as though never named at all.

Readers never wait, writers never wait, except on each other

That mechanism has a consequence that matters more than the columns that produced it. A read is never made to wait for a write, nor a write for a read, because a SELECT only looks at whichever version its own snapshot says is current, and an UPDATE is free to go on writing a new one underneath it. A reader runs the following by opening two client sessions and typing the A lines in one and the B lines in the other, in the order written.

A: BEGIN ISOLATION LEVEL READ COMMITTED;
A: SELECT balance FROM accounts WHERE id = 1;     -- 100.00
B: BEGIN;
B: UPDATE accounts SET balance = 150 WHERE id = 1;
A: SELECT balance FROM accounts WHERE id = 1;     -- 100.00, and A's read never waited on B's uncommitted write
B: COMMIT;
A: SELECT balance FROM accounts WHERE id = 1;     -- 150.00, now that B's version is the one a fresh read is shown
A: COMMIT;

Both directions hold: B's UPDATE was never blocked by A's open read, and A's reads were never blocked by B's uncommitted write. Only the second usually feels surprising, coming from a lock-based system where a write in progress makes a read wait.

The one case MVCC makes a session wait for is two writers wanting to end the same row version at once, since only one can be the transaction that ends it.

A: BEGIN;
A: UPDATE accounts SET balance = 200 WHERE id = 1;
B: BEGIN;
B: UPDATE accounts SET balance = 300 WHERE id = 1;  -- blocks here, waiting on A's row lock
A: COMMIT;
B: COMMIT;                                          -- B's UPDATE had already landed the moment A released the row

Which session waits, and how, is lesson 32's subject. This lesson only explains why some wait is unavoidable here, the one case where the ordinary rule does not apply.

The snapshot: when did you ask

xmin and xmax describe one row's history. What decides which version of every row a statement is shown is its snapshot, and two functions expose it directly.

BEGIN;
SELECT txid_current()::text, pg_current_snapshot()::text;
SELECT txid_current()::text, pg_current_snapshot()::text;
COMMIT;

txid_current() reports the same id both times, since it names the transaction rather than the statement. pg_current_snapshot() also reports the same value both times, because nothing else committed against this table in between: a snapshot states which transactions had already committed at the moment it was taken, and an idle transaction gives it nothing new to report on a second look. Both differ on a different run, so the fact to keep is the shape, not the value.

That is the precise answer to a question the next lesson turns into a rule: an isolation level is not how strictly transactions run one after another, but when a snapshot gets taken and how long it is kept before a fresh one replaces it. Lesson 30 names the levels; this lesson only pins down what "snapshot" refers to.

Dead versions have to go somewhere

Section one's rolled-back UPDATE left a dead row in the table, distinguishable from a live one only by whether the transaction named in its xmax committed. A committed UPDATE leaves the same leftover: the old version stops being anyone's current row, but PostgreSQL does not erase it there and then, because some snapshot taken before the commit may still be entitled to see it.

UPDATE accounts SET balance = balance WHERE id = 2;
-- a new ctid even though no value changed, because writing a new version is what UPDATE does regardless

Reclaiming that space is VACUUM's job, run by hand or by the autovacuum daemon PostgreSQL schedules on its own, which removes a dead version once no snapshot can still need it (24.1.2 Recovering Disk Space). What dead versions cost, and how to watch that, is stage 6's subject.

What MVCC does not give you

Everything so far is a promise about a single read: it is consistent with a snapshot, one well-defined point in a row's history, rather than a mash-up of half-applied writes. It is not a promise that a decision made from that read is still right once it turns into a write, and that gap is where a reader's confidence in MVCC tends to outrun what MVCC actually did.

A: BEGIN;
A: SELECT balance FROM accounts WHERE id = 1;     -- 100.00; A decides 100 minus 80 leaves 20, safe to spend
B: BEGIN;
B: SELECT balance FROM accounts WHERE id = 1;     -- 100.00; B decides 100 minus 10 leaves 90, safe to spend
A: UPDATE accounts SET balance = 20 WHERE id = 1;
A: COMMIT;
B: UPDATE accounts SET balance = 90 WHERE id = 1;
B: COMMIT;
A: SELECT balance FROM accounts WHERE id = 1;     -- 90.00, not the 10.00 both withdrawals together should have left, and no error anywhere

Both reads were perfectly consistent with a snapshot; neither session was shown a value the other had not committed. B's decision was correct given the balance B read. What made it wrong by the time it landed is that B wrote the value it had computed at read time, rather than telling the database to compute it at write time: B's UPDATE said "the balance is 90" instead of "take 10 off whatever it now is". Naming that failure, and the others shaped like it, is lesson 31's job; this lesson stops at showing that MVCC's consistency and a decision's correctness are two separate promises, and only one comes free.

Practice

1. ▢ accounts was seeded by one INSERT naming both rows at once. Predict whether the two rows share the same xmin.

2. ▢ Predict whether UPDATE accounts SET balance = balance WHERE id = 2, which changes no value at all, gives that row a new ctid.

3. ▢ Predict whether DELETE FROM accounts WHERE id = 3 followed by ROLLBACK moves that row's ctid, given that section one showed an UPDATE followed by ROLLBACK leaving ctid exactly where it started.

4. ▢ A has updated row 1 inside an open transaction, and B's update of the same row is blocked waiting for A. Predict what a third session's plain read does while that block is in effect.

   C: SELECT balance FROM accounts WHERE id = 1;
   

5. ▢ Inside one transaction, with nothing else committing against accounts in between, pg_current_snapshot() is called twice. Predict whether the two calls print visibly different values.

6. ▢ Section five's interleaving had A and B each read 100.00 and write back a literal number they had computed themselves, silently losing one withdrawal. Predict the final balance of the same shape of interleaving, starting again from 100.00, if both sessions write balance = balance - 10 instead.

   A: BEGIN;
   A: SELECT balance FROM accounts WHERE id = 1;
   B: BEGIN;
   B: SELECT balance FROM accounts WHERE id = 1;
   A: UPDATE accounts SET balance = balance - 10 WHERE id = 1;
   A: COMMIT;
   B: UPDATE accounts SET balance = balance - 10 WHERE id = 1;
   B: COMMIT;
   

Real-world reps

• [ ] Run SELECT id, xmin::text, xmax::text, ctid::text FROM ... against a table you already have write access to, update one row, and watch which columns change and which do not.
• [ ] Open two sessions of your own and run this lesson's reader-writer interleaving, then its two-writer interleaving, and confirm for yourself which direction never waits and which one does.
• [ ] Tomorrow: find one place in code you maintain where a value is read and a decision is made from it before a later statement writes a value back, and ask what happens if another session's write lands in between.

Going further

• 13.1 Introduction: the chapter this lesson compresses, on why reading and writing never block each other
• 5.6 System Columns: where xmin, xmax and ctid are documented
• 24.1.2 Recovering Disk Space: why an old version survives its UPDATE, and what VACUUM does about it
• 13.2 Transaction Isolation: what a snapshot's timing buys, named in lesson 30
• Transactions: the stage 5 reference sheet
• Resources

Not landing? Reread the primary source at the top, since this lesson compresses it and compression is where understanding leaks. Check the glossary for any term that felt slippery.

If the lesson itself is unclear rather than the material, that is a defect: open an issue.