postgres://internals

MVCC & Transactions

// the problem

Two people open the same bank account at the same instant. Both see a consistent balance, a third person is busy changing it, and nobody waits on anybody — readers never block writers, writers never block readers. Postgres pulls this off without locking rows for reads. The trick is that it never overwrites a row in place: it keeps multiple versions and shows each transaction the right one. That's MVCC — Multi-Version Concurrency Control.

Below are two independent transactions over the same two rows. Give them different isolation levels, step them through reads, updates, and commits, and watch the heap accumulate versions — and watch what each transaction sees.

fig.01 · two transactionsnext xid: 2

Txn Aidle

isolation

sees

— (run Read)

Txn Bidle

isolation

sees

— (run Read)

the heap · all tuple versionslivependingdead

#0row 1= 100xmin 1xmax 0live

#1row 2= 200xmin 1xmax 0live

a version is visible if its xmin is committed in your snapshot and its xmax is not

Try this exact sequence and watch the last step:

// note · reproduce a repeatable-read snapshot

Set Txn A = read committed and Txn B = repeatable read. Begin both. Click B → Read (B takes its snapshot now, seeing 100/200). Now A → Row 1 +25, then A → Commit. Finally B → Read again. B still sees the old value — its snapshot was frozen. Switch B to read committed and repeat: this time B sees A's committed change.

Every row is really a stack of versions

From the Storage lesson: each tuple's header carries xmin (the transaction that created this version) and xmax (the transaction that deleted or superseded it, 0 if still live). An UPDATE never edits in place — it stamps the old version's xmax and writes a new version with a new xmin. Watch xmin change:

sql · live postgresrun me first

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (id int primary key, balance int);
INSERT INTO accounts VALUES (1, 100), (2, 200);
SELECT id, xmin, xmax, balance FROM accounts ORDER BY id;

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (id int primary key, balance int);
INSERT INTO accounts VALUES (1, 100), (2, 200);
SELECT id, xmin, xmax, balance FROM accounts ORDER BY id;

⌘/ctrl + enter

Every row shares one xmin (the inserting transaction) and has xmax = 0. Now update one row and look again — its xmin is different, because the row you see is a brand-new version:

sql · live postgreseditable — run it

UPDATE accounts SET balance = balance + 25 WHERE id = 1;
SELECT id, xmin, xmax, balance FROM accounts WHERE id = 1;

UPDATE accounts SET balance = balance + 25 WHERE id = 1;
SELECT id, xmin, xmax, balance FROM accounts WHERE id = 1;

⌘/ctrl + enter

The old version (balance 100) is still on the page, now dead — exactly the bloat you saw in the Storage lesson. (A plain SELECT can only show you live versions, which is why the visualizer above is the way to see the dead ones.)

Snapshots decide what you see

When a statement (or transaction) starts, it takes a snapshot: essentially “which transactions had committed by now.” A version is visible to you if:

its xmin is committed and inside your snapshot (or it's your own change), and
its xmax is not committed-in-your-snapshot (nobody you can see has deleted it).

That single rule is the whole of MVCC visibility. Because your snapshot is fixed, another transaction's in-flight or later-committed work is simply invisible to you — no locks required.

sql · live postgreseditable — run it

SELECT txid_current()        AS my_xid,
     pg_current_snapshot() AS snapshot;  -- xmin:xmax:in-progress list

SELECT txid_current()        AS my_xid,
     pg_current_snapshot() AS snapshot;  -- xmin:xmax:in-progress list

⌘/ctrl + enter

Isolation levels are just snapshot timing

The SQL isolation levels are, under the hood, mostly a question of when the snapshot is taken:

Read Committed (the default): a fresh snapshot for every statement. You always see the latest committed data — but two statements in the same transaction can disagree (a non-repeatable read).
Repeatable Read: one snapshot for the whole transaction, taken at the first statement. Every read is consistent; a concurrent committed change is invisible, and if you try to update a row someone else changed, you get a serialization error.
Serializable: repeatable read plus Serializable Snapshot Isolation (SSI), which watches for dangerous read/write patterns between transactions and aborts one to guarantee the result equals some serial order.

// note · Postgres has no dirty reads

The SQL standard defines Read Uncommitted (which permits dirty reads). Postgres doesn't implement it — asking for it just gives you Read Committed. You can never see another transaction's uncommitted changes.

// why it matters · pick the weakest level that's correct

Read Committed is cheapest and fine for most work. Reach for Repeatable Read when a transaction makes several reads that must agree (reports, consistency checks), and Serializable when correctness depends on there being no concurrency anomaly at all — accepting that you must be ready to retry aborted transactions.

Writers still conflict

MVCC removes read-write blocking, but two transactions writing the same row must still be ordered. When A updates row 1 and then B tries to update the same row, B doesn't fail — it waits on A's row lock. What happens when A finishes depends on B's isolation level:

Under Read Committed, once A commits, B wakes up, re-reads the latest version of the row, and applies its update on top — no error, the writes are serialized. (If A rolled back, B just proceeds.)
Under Repeatable Read, B can't silently write over a change it can't see: when A commits, B's update fails with “could not serialize access due to concurrent update” and the app must retry.

Either way Postgres won't lose A's write. (The simplified visualizer above can't model true blocking on one connection, so it refuses B's concurrent write outright — the real behavior is the wait-then-resolve above.)

The bill comes due: dead tuples

Every UPDATE and DELETE leaves a dead version behind. They're what makes readers lock-free, but they accumulate as bloat. VACUUM (and autovacuum) walks the pages and marks dead-tuple space reusable once no snapshot can still see them.

// gotcha · the long-transaction trap

A snapshot holds back cleanup: VACUUM cannot remove a dead version that some still-open transaction might need. One forgotten long-running transaction can therefore block cleanup table-wide, and bloat balloons. This is also tied to transaction-id wraparound: xids are 32-bit, so VACUUM must periodically “freeze” very old rows before the counter wraps.

Your turn

Uses the accounts table from the “run me first” cell (id 1 and 2).

// challengewrite it yourself

Show both accounts with the transaction that created each version (xmin) and its deleter (xmax).

SELECT id, xmin, xmax, balance
FROM accounts
ORDER BY id;

SELECT id, xmin, xmax, balance
FROM accounts
ORDER BY id;

⌘/ctrl + enter

// challengewrite it yourself

Update account 1's balance, then show that its xmin now differs from account 2's — proving the UPDATE wrote a brand-new version.

UPDATE accounts SET balance = 175 WHERE id = 1;
SELECT id, xmin FROM accounts ORDER BY id;

UPDATE accounts SET balance = 175 WHERE id = 1;
SELECT id, xmin FROM accounts ORDER BY id;

⌘/ctrl + enter

// what you now understand

01MVCC keeps multiple versions of each row so readers never block writers and writers never block readers.
02A version's xmin/xmax (in the tuple header) record which transaction created and which deleted it; UPDATE writes a new version rather than editing in place.
03Visibility rule: a version is visible if its xmin is committed in your snapshot and its xmax is not.
04Isolation level = snapshot timing: Read Committed snapshots per statement; Repeatable Read snapshots once per transaction; Serializable adds SSI conflict detection.
05Postgres never allows dirty reads (no Read Uncommitted).
06Same-row writers still serialize; Repeatable Read raises a serialization error on a concurrent committed update.
07Dead versions are the cost of MVCC — reclaimed by VACUUM, which a long-open transaction can stall.

// self-test

Under Repeatable Read, transaction B reads a row, then transaction A updates and commits that row. What does B see on its next read?

// self-test

Why can Postgres let a reader run without taking any lock on the rows it reads?

// go deeper

Transaction Isolation (official docs) — the levels, phenomena, and SSI in detail
MVCC Introduction — the model and its guarantees
Routine Vacuuming — dead tuples, freezing, xid wraparound
heapam_visibility.c — the real visibility rules in the source

nextVACUUM & Bloat