Postgres: atomicity and locking — who waits for whom, and when

Postgres: atomicity and locking — who waits for whom, and when#

How Postgres guarantees a transaction's "all or nothing" — going down to the xid, the tuple's xmin/xmax, and the commit bits in pg_xact — and what happens, timeline by timeline, when two transactions (or a migration) fight over the messages table.

Every example uses this project's messages table and can be reproduced against the Postgres from docker-compose.yml using two psql terminals.

1. Atomicity: a transaction is an indivisible unit#

Everything between BEGIN and COMMIT either becomes permanent at once or disappears at once. There is no intermediate state visible to anyone outside. If any command fails midway, ROLLBACK undoes everything — including the parts that had already "succeeded" inside the transaction.

PATH A — EVERYTHING
BEGIN ─► INSERT msg #1 ─► INSERT msg #2 ─► INSERT msg #3 ─► COMMIT
                                                            └─ ✓ all 3 messages become
                                                               visible together, at the
                                                               same instant
 
PATH B — NOTHING
BEGIN ─► INSERT msg #1 ─► INSERT msg #2 ─► INSERT msg #3 ─► ROLLBACK
                                           └─ fails         └─ ✗ msgs #1 and #2 vanish
                                              (constraint)     too — they never existed
                                                               as far as the world knows

This is exactly what protects this app's batch insert: a batch of 500 messages in one INSERT ... VALUES (...), (...), ... goes in whole or not at all — never "half a batch".

2. Dictionary: each term on its own, before any flow#

Before the flow, the warning that prevents the most common confusion: transaction ≠ version. A transaction is a session executing commands — it gets an xid. A version is a physical copy of the row on disk. Row id = 42 is logically one row, but during an UPDATE it exists as two physical copies at the same time.

A detail about xmin/xmax: they are hidden system columns — absent from SELECT *, but you can ask for them by name on any table (along with ctid, cmin, cmax, and tableoid; cmin and cmax share a single physical header field, t_cid). There is no column called xid: a row doesn't have "an xid", it has two fields that reference transaction xids.

3. The movie: UPDATE on id = 42, with each terminal's real queries#

Initial state — how row 42 sits on disk#

SELECT xmin, xmax, ctid, id, status FROM messages WHERE id = 42;
 
--  xmin | xmax | ctid  | id | status
-- ------+------+-------+----+--------
--   640 |    0 | (0,1) | 42 | sent

How to read it: there is one single physical copy of row 42, created by the transaction with xid 640 (long since committed). xmax = 0 means "no transaction has marked me for replacement". ctid (0,1) is the physical address: page 0, slot 1.

Step 1 — Terminal A opens a transaction and runs the UPDATE#

BEGIN;
SELECT pg_current_xact_id();  -- → 812  (the xid A just received)
UPDATE messages SET status = 'read' WHERE id = 42;  -- UPDATE 1 (returns immediately)

What changed. In pg_xact: entry 812 → in_progress is born, and A's transaction locks its own xid. On disk, three writes — zero removals:

the disk now holds TWO physical copies of the same row 42:
 
 copy   | xmin | xmax | ctid  | status | meaning
--------+------+------+-------+--------+---------------------------------------------
 old    |  640 |  812 | (0,2)*| sent   | stamped "812 wants to replace me"
 new    |  812 |    0 | (0,2) | read   | just created; only counts if 812 commits
 
 * the old copy's ctid was updated to point at the new one — the old→new chain

Why. Nothing was destroyed because transaction 812 may still fail. If Postgres edited the copy in place, a rollback would have to write the old value back (an undo log — the Oracle way). The Postgres way makes giving up free: just declare xid 812 dead and its stamps stop counting.

[!NOTE] Stamping xmax is an in-place mutation — but only of the header. The data (status='sent') stays intact, so concurrent readers never see half-written content; and the stamp is reversible for free, because its meaning lives in pg_xact, not in the tuple.

Step 2 — what each terminal sees NOW (A hasn't committed)#

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   812 |    0 | read      ← A sees its own write: the new copy

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   640 |  812 | sent      ← answers IMMEDIATELY, no blocking

Reaction. Look at B's result: it returns the old data (sent), yet xmax = 812 already shows up — B literally sees the stamp A's transaction left. B then consulted pg_xact: "812 is in progress" → the death stamp doesn't count yet → serve the old copy. The new copy is also on disk, but invisible to B, because its xmin = 812 hasn't committed.

Why zero waiting. To answer, B only compared integers and consulted pg_xact. At no point did it touch the lock manager. Readers contend with writers over nothing — this is where the MVCC of section 5 comes from.

Step 3 — Terminal B tries to WRITE the same row#

UPDATE messages SET status = 'archived' WHERE id = 42;
-- ...hangs here: no error, no response, cursor stuck...

SELECT locktype, transactionid, granted FROM pg_locks WHERE granted = false;
 
--    locktype    | transactionid | granted
--  transactionid |           812 | f        ← B is waiting for TRANSACTION 812 to finish

Reaction. B found the old copy with xmax = 812 in progress — the same thing the reader saw. But writing requires knowing the outcome: if 812 commits, the valid copy is the new one; if it aborts, the old one. Since 812's future is undecidable right now, B sleeps waiting for xid 812's lock — see the pg_locks output: it isn't waiting for "row 42", it's waiting for transaction 812.

Why sleep instead of erroring. At the default isolation level (READ COMMITTED) Postgres queues concurrent writes rather than rejecting them. And there is no polling — the lock manager wakes B the instant 812 releases its lock.

Step 4 — Terminal A commits#

COMMIT;
-- in TERMINAL B, at that same instant, on its own:
-- UPDATE 1        ← the stuck command from step 3 unblocked and completed

What changed (with synchronous_commit = on, the default — and in order):

1. The commit record is written and flushed to the WAL — durability.
2. The 2 bits flip: pg_xact[812] = committed — the durable verdict that tuple checks will consult from now on.
3. The transaction removes itself from the shared list of running transactions (the ProcArray) — and this is the atomic visibility switch: snapshot creation is serialized against that removal, so every new snapshot taken after it sees all of 812's rows at once.

Neither copy of the row was touched — both stayed byte-for-byte the same. Finally, A releases xid 812's lock.

[!NOTE] There is even a tiny window between steps 2 and 3 in which pg_xact already says "committed" but readers still treat 812 as in progress — the ordering of the visibility checks in the source code exists precisely because of it. And visibility is always per snapshot: a REPEATABLE READ transaction whose snapshot predates the commit still sees nothing, by design.

Reaction. In a chain: new readers redo step 2's arithmetic with the same numbers, but now 812 = committed flips both answers — the old copy is officially dead, the new one officially alive. And B wakes up: it follows the old copy's ctid to the new one, re-evaluates WHERE id = 42 against it (the mechanism is called EvalPlanQual), it still matches, and applies its UPDATE — creating a third copy. After B's commit (xid 813):

SELECT xmin, xmax, status FROM messages WHERE id = 42;
--  xmin | xmax | status
--   813 |    0 | archived   -- B's 'archived' was applied ON TOP OF A's 'read'

Why this is atomicity. Had transaction 812 written 500 messages, that would be 500 new copies, all with xmin = 812. A single commit changes the verdict for all 500 simultaneously — O(1) cost, not O(rows written).

[!WARNING] Under REPEATABLE READ, the "B wakes up and reapplies" step doesn't exist: B fails with ERROR: could not serialize access due to concurrent update, and the application must retry the transaction.

Step 5 — alternate universe: what if A ran ROLLBACK (or the connection dropped)?#

ROLLBACK;   -- a dropped connection has the same effect

What changed. Only this: pg_xact[812] = aborted, and the xid lock is released. Again, no copy of the row is touched.

Reaction. The xmax = 812 stamp on the old copy is voided (its author aborted), so it remains the valid version. The new copy (xmin = 812) becomes invisible garbage forever — VACUUM collects it later. B unblocks all the same, sees that 812 failed, and applies its UPDATE onto the old copy: the final archived is built from sent; A's read never existed.

Why. Commit and rollback cost the same — updating the xid's status. Atomicity's "undo everything" undoes nothing; it merely never makes it official. That's why rollback is instantaneous even for a transaction that wrote millions of rows.

4. MVCC: why reads never get stuck behind writes#

With xmin/xmax in place, reading becomes pure visibility arithmetic — no locks at all. Each query uses its snapshot and applies the rule: a tuple is visible if xmin is committed and outside my snapshot, and xmax is 0, aborted, or still in progress.

Who blocks whom, on the same row:

INSERTs of new rows (this app's message-buffer case) contend with no one at the row level — which is why concurrent batches scale well. The exceptions: UNIQUE and exclusion-constraint checks (and INSERT ... ON CONFLICT's speculative path), where the second inserter waits on the first inserter's xid.

5. The lock queue: (approximate) arrival order#

If three transactions want to write the same row, a queue forms. Each one wakes when the previous finishes — and before applying its UPDATE, re-evaluates the WHERE against the newest version of the row.

        ┌────────────────┐      waits        waits
        │   row id = 42  │ ◄─── [ T2 ] ◄─── [ T3 ]
        │  🔒 lock: T1   │     1st in line  2nd in line
        └────────────────┘

Precision matters here: arbitration happens through a heavyweight tuple lock that guarantees no starvation and serves in approximate arrival order — strict FIFO is not guaranteed (for instance, a session already holding conflicting locks may be inserted ahead in the queue to avoid deadlocks).

To watch who is stuck waiting on locks, live:

SELECT pid, wait_event_type, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

6. Deadlock: when waiting becomes a cycle#

If T1 holds row A and wants row B, while T2 holds row B and wants row A, nobody ever wakes up:

   T1 (holds 🔒 row A) ───── waits for row B ────► T2 (holds 🔒 row B)
        ▲                                                 │
        └───────────────── waits for row A ───────────────┘

A waiter runs a cycle check after deadlock_timeout (default 1s). In a "hard" deadlock, the first waiter to run the check and find the cycle normally aborts itself with ERROR: deadlock detected — but the documentation warns that which transaction gets aborted should not be relied upon. ("Soft" deadlocks, solvable by reordering the wait queue, are resolved without aborting anyone; and if the blocker is an autovacuum, it gets canceled instead.) The victim gets a full rollback — atomicity guarantees it is undone in its entirety.

Classic prevention: make every transaction touch rows in the same order (e.g., always by ascending id) — with no crossed ordering, no cycle is possible.

7. Migrations: when they fail, and when they wait#

In Postgres, DDL is transactional — ALTER TABLE, CREATE INDEX, DROP obey the same atomicity as data (rare among databases; MySQL doesn't do this). The catalog (pg_class, pg_attribute) consists of ordinary MVCC tables: DDL atomicity is the SAME machinery as section 3's xmin/xmax.

Case 1 — the migration fails midway#

BEGIN;
ALTER TABLE messages ADD COLUMN read_at timestamptz;
CREATE INDEX messages_read_at_idx ON messages (read_at);
UPDATE messages SET read_at = now() WHERE status = 'read';  -- ✗ fails here
ROLLBACK;  -- the column AND the index vanish from the catalog together

Notable exceptions that cannot run inside a transaction block (the list is not exhaustive): CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, ALTER SYSTEM, CREATE/DROP DATABASE, CREATE/DROP TABLESPACE. If a CREATE INDEX CONCURRENTLY fails midway, it leaves behind an index marked INVALID — ignored by queries but still paying overhead on every write. There is no rollback: recover by dropping it and retrying (the recommended path), or with REINDEX INDEX CONCURRENTLY.

[!WARNING] Prisma Migrate (used in this project) does NOT wrap a migration in a transaction on Postgres. If a migration fails midway, it can be left half-applied: it is recorded as failed in the _prisma_migrations table, blocks subsequent migrate deploy runs (error P3009), and you reconcile manually with prisma migrate resolve --applied <name> (after finishing it by hand) or --rolled-back <name> (after reverting it by hand). For true all-or-nothing, add explicit BEGIN;/COMMIT; to the migration's generated SQL.

Case 2 — the migration waits: the ACCESS EXCLUSIVE pileup#

reporting  ─►  [ long SELECT on messages (analytics, 90s) ── ACCESS SHARE ]───── done
                                                                                  │
migration  ─►      [ ALTER TABLE messages ... ══ WAITING ═══════════════ ]──[ runs in ms ]─ COMMIT
                     wants ACCESS EXCLUSIVE: conflicts even with ACCESS SHARE     │
                                                                                  │
your app   ─►          [ ALL new SELECT/INSERTs ══ QUEUED ═══════════════ ]──[ app back to normal ]
                         compatible with the report, but queued behind the ALTER

The real danger: the migration hasn't executed anything yet — the mere request for ACCESS EXCLUSIVE takes the app down, because new requests from other sessions queue behind the waiting ALTER even though they are compatible with the currently-held lock (otherwise the ALTER could never get in; the main exception is a session that already holds a lock on the table).

The standard production defense — fail fast instead of damming up the app:

SET lock_timeout = '2s';
ALTER TABLE messages ADD COLUMN read_at timestamptz;
-- if the lock isn't acquired within 2s:
-- ERROR: canceling statement due to lock timeout  (atomic rollback)
-- → retry, instead of holding the whole app hostage to a 90s report

Table-level locks per command (the ones that matter):

* Most forms. Some take weaker locks: VALIDATE CONSTRAINT, SET (storage parameters), and ATTACH PARTITION take SHARE UPDATE EXCLUSIVE; ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE (on both tables). And since PostgreSQL 11, ADD COLUMN ... DEFAULT <non-volatile value> is metadata-only, no table rewrite (no-default columns were already fast before that); a volatile DEFAULT such as clock_timestamp(), a generated column, or an identity column still forces a full rewrite.

8. See it with your own eyes#

UPDATE really does create a new copy#

SELECT xmin, xmax, id FROM messages WHERE id = 1;  -- note the xmin
UPDATE messages SET status = status WHERE id = 1;  -- "no-op" update
SELECT xmin, xmax, id FROM messages WHERE id = 1;  -- xmin CHANGED!

Listing the dead copies (which SELECT hides)#

Through normal SQL you can't — the visibility rule filters dead copies out before they reach you. But the pageinspect extension (requires superuser; extensions are per-database) opens the raw page:

CREATE EXTENSION IF NOT EXISTS pageinspect;
 
SELECT lp AS slot, t_xmin, t_xmax, t_ctid AS points_to
FROM heap_page_items(get_raw_page('messages', 0));
 
--  slot | t_xmin | t_xmax | points_to
-- ------+--------+--------+-----------
--     3 |    640 |    813 | (0,4)   ← old copy: killed by 813, points to its successor
--     4 |    813 |      0 | (0,4)   ← new copy: alive

Practical warnings:

• Dead copies disappear fast: autovacuum and HOT pruning (a mini-cleanup any query may trigger while passing through the page) recycle them. For the demo: ALTER TABLE messages SET (autovacuum_enabled = false); — re-enable afterwards. Note: that disables routine autovacuum, but anti-wraparound autovacuum still runs.
• To just count without opening pages: SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'messages'; (n_dead_tup is an estimate from the statistics collector, not an exact count).
• Run VACUUM messages; and inspect again: the dead copies' slots show up empty — that's MVCC's garbage being collected.

The xid is 32 bits — and that has a price#

The xid counter wraps around (~4 billion values, a usable horizon of ~2 billion). VACUUM periodically "freezes" old tuples (sets a frozen bit in the header) so wraparound can't corrupt visibility — which is why autovacuum must never be truly turned off.

And hint bits: when a reader consults pg_xact and learns "640 committed", it writes a bit onto the tuple itself ("xmin confirmed") so future readers skip the pg_xact lookup entirely. Yes — a SELECT can physically write to a page. It's just memoization of an immutable fact.

Summary#

Atomicity: an entire transaction's verdict lives in a single place (its xid's status), so commit and rollback are O(1) — no tuple is touched; the final visibility switch is the transaction leaving the global list of running transactions, against which every new snapshot is serialized. Locking: a write waits for a write on the same row, sleeping on the xid lock of whoever got there first, until that transaction commits or rolls back — reads never join that queue, thanks to MVCC. And in migrations, the whole world can end up waiting on an ACCESS EXCLUSIVE — hence lock_timeout on all production DDL, and beware: Prisma Migrate does not give you all-or-nothing for free.