MVCC & Transactions

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.

Try this exact sequence and watch the last step:

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:

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;

Every row shares one xmin (the inserting transaction) and has xmax = 0. Now update one row and look again — both its ctid (physical address) and its xmin change, because the row you see is a physically new tuple:

SELECT ctid, xmin, balance FROM accounts WHERE id = 1;   -- version A, e.g. (0,1)

UPDATE accounts SET balance = balance + 25 WHERE id = 1;

SELECT ctid, xmin, balance FROM accounts WHERE id = 1;   -- version B: new ctid, new xmin

The visible row for id = 1 jumped to a new ctid (a different slot on the page) with a fresh xmin. The old version (balance 100) is still sitting at its original ctid, now dead — its xmax stamped with the updating transaction. A plain SELECT only ever shows you live versions, which is why the visualizer above is the way to actually see the dead ones pile up.

Snapshots decide what you see

When a statement (or transaction) starts it takes a snapshot — a compact record of "which transactions count as committed right now." It's not a copy of any data; it's three small pieces, printed as xmin:xmax:xip_list:

SELECT pg_current_xact_id()                    AS my_xid,
     pg_current_snapshot()                   AS snapshot,        -- xmin:xmax:xip
     pg_snapshot_xmin(pg_current_snapshot()) AS oldest_running,  -- the xmin part
     pg_snapshot_xmax(pg_current_snapshot()) AS next_xid;        -- the xmax part

Run it twice — my_xid climbs, because every transaction is handed a new, ever-increasing 32-bit id. The snapshot's three parts split that id space into three zones:

• xmin — the oldest transaction still running when the snapshot was taken. Every id below it is already decided (committed or aborted), so those never need checking.
• xmax — the next id that will be assigned. Every id at or above it is in the future and therefore invisible.
• xip_list — the handful of ids between xmin and xmax that were still in progress at snapshot time. Their work is invisible too.

Now the famous visibility rule is just arithmetic on those numbers. A row version is visible to you when:

• its xmin is committed and not in the future — below xmax and not in xip_list (or it's your own transaction) — and
• its xmax is 0, or names a transaction that isn't visibly committed (nobody you can see has deleted it).

That's the entire mechanism: a couple of integer comparisons per tuple, no locks. Because your snapshot is frozen at one instant, another transaction's in-flight or later-committed work is simply out of range — you literally can't see it.

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.

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. Watch it happen: 2,000 rows, updated three times, and the file quadruples even though the live row count never changes:

DROP TABLE IF EXISTS churn;
CREATE TABLE churn (id int, v int);
INSERT INTO churn SELECT g, g FROM generate_series(1, 2000) g;
SELECT pg_relation_size('churn') AS bytes, count(*) AS live_rows FROM churn;  -- ~72 KB, 2000

UPDATE churn SET v = v + 1;   -- each UPDATE leaves 2,000 dead versions behind
UPDATE churn SET v = v + 1;
UPDATE churn SET v = v + 1;
SELECT pg_relation_size('churn') AS bytes, count(*) AS live_rows FROM churn;  -- ~288 KB, still 2000

Four versions of every row on disk, exactly one of them alive. That dead space is the tax MVCC charges for lock-free reads. VACUUM (and autovacuum) walks the pages and marks dead-tuple space reusable once no snapshot can still see them.

Your turn

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

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

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