MVCC, snapshots and isolation levels

PostgreSQL implements isolation with multi-version concurrency control (MVCC). An UPDATE does not overwrite a row: it marks the old version as deleted and writes a new version elsewhere. Readers that started earlier keep seeing the old version, so readers never block writers and writers never block readers. Row-level waits happen only when two transactions write or lock the same row.

Row versions: xmin, xmax, ctid

Every row version (tuple) carries hidden system columns:

• xmin — the id of the transaction that created this version.
• xmax — the id of the transaction that deleted or locked it, or 0.
• ctid — its physical position, (page, item).

1CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);2INSERT INTO accounts VALUES (1, 100), (2, 50), (3, 75);

1CREATE TEMP TABLE before AS SELECT id, ctid::text AS ctid_before FROM accounts;2 3UPDATE accounts SET balance = balance + 1 WHERE id = 2;4UPDATE accounts SET balance = balance + 1 WHERE id = 2;5 6SELECT a.id,7       b.ctid_before,8       a.ctid::text AS ctid_now,9       a.xmin = pg_current_xact_id()::xid AS created_by_this_tx,10       a.xmax::text AS xmax11FROM accounts a12JOIN before b USING (id)13ORDER BY a.id;

The updated row moved twice, each time to a new slot; the old versions are now dead tuples. VACUUM (normally autovacuum) later reclaims dead tuples that no running transaction can still see. That is why a long-running transaction that holds an old snapshot or transaction id causes bloat across the whole database: no row version deleted after that point can be cleaned up until it ends.

Snapshots

A snapshot records which transactions count as finished: every id below xmin has finished, every id from xmax up had not yet finished (so counts as invisible), and the list in between names the ones still running when the snapshot was taken. A row version is visible when its creator committed and is visible to the snapshot, and its deleter is not (or there is none). PostgreSQL exposes the snapshot test directly; here it is on a literal snapshot 100:105:100,102, meaning 100 and 102 were still running:

1SELECT x AS txid,2       pg_visible_in_snapshot(x::text::xid8, '100:105:100,102'::pg_snapshot) AS visible3FROM generate_series(99, 106) AS x4ORDER BY x;

Isolation levels, PostgreSQL edition

What follows about two concurrent transactions is PostgreSQL's documented behaviour; the sandbox runs one session at a time, so the example below only shows how a level is chosen.

• READ COMMITTED (the default): each statement takes a fresh snapshot. Two SELECTs in one transaction can see different data. An UPDATE that finds its target row changed by a just-committed transaction re-checks its WHERE on the newest version and updates that, which is why SET balance = balance - 10 is safe here while reading the balance into the application and writing back a computed number loses updates.
• REPEATABLE READ: one snapshot for the whole transaction, taken at its first statement. In PostgreSQL this also prevents phantom reads. If you try to update a row that a concurrent transaction changed after your snapshot, you get a serialization failure (SQLSTATE 40001) instead of a silent overwrite.
• SERIALIZABLE: repeatable read plus detection of dangerous read/write patterns (serializable snapshot isolation). Any outcome is one some serial order could have produced; the price is that transactions can fail with 40001 and the application must retry them.
• READ UNCOMMITTED is accepted but behaves as READ COMMITTED: PostgreSQL never shows uncommitted data.

The level must be set before the transaction's first query:

1CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$2BEGIN3  EXECUTE stmt;4  RETURN 'ok';5EXCEPTION WHEN OTHERS THEN6  RETURN SQLSTATE || ': ' || SQLERRM;7END $$;

1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;2 3SELECT current_setting('transaction_isolation')         AS this_transaction,4       current_setting('default_transaction_isolation') AS server_default,5       attempt('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE') AS change_after_a_query;

Common mistakes

• Assuming BEGIN alone freezes the data you read; at READ COMMITTED each statement sees newly committed rows.
• Read-modify-write in application code under READ COMMITTED, which loses concurrent updates; use an atomic UPDATE ... SET x = x + ..., SELECT ... FOR UPDATE, or a stricter level.
• Using REPEATABLE READ or SERIALIZABLE without retry logic for 40001.
• Leaving a transaction idle in an open connection after it has written something, or while it holds a REPEATABLE READ snapshot: vacuum cannot clean up behind it, and busy tables bloat.

What interviewers ask

• How do readers and writers avoid blocking each other in PostgreSQL? MVCC: writers create new row versions, readers use a snapshot to pick the version that was committed when it was taken.
• What is the difference between READ COMMITTED and REPEATABLE READ? Snapshot per statement versus per transaction; RR raises 40001 on a concurrent update instead of applying it to newer data.
• Why does table bloat happen and how do you prevent it? Dead tuples from updates and deletes; autovacuum, and no long-running or idle transactions holding the cleanup horizon back.