A Prepared Transaction Holds Its Lock Through a Full Server Restart

Run phase 1 of two-phase commit by hand — PREPARE TRANSACTION 'tx1' — and the transaction sits in doubt: owned by no session, yet still holding the row's exclusive lock. A second session's UPDATE blocks indefinitely; a full docker restart does not clear it — same row in pg_prepared_xacts, same lock. Only COMMIT PREPARED or ROLLBACK PREPARED frees it.

Concept

Run phase 1 of two-phase commit by hand and watch the hazard the book warns about become physical. In one psql session: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; PREPARE TRANSACTION 'tx1';. The transaction is now prepared — neither committed nor rolled back — and it is owned by no session, yet it still holds the row's exclusive lock. A second session's UPDATE ... WHERE id = 1 blocks indefinitely. It does not time out. And when you docker restart the whole Postgres server, the prepared transaction is still there — same row in pg_prepared_xacts, same lock, still blocking — because it was written durably to disk. Nothing short of an explicit COMMIT PREPARED 'tx1' or ROLLBACK PREPARED 'tx1' frees it. This is "holding locks while in doubt," the property that makes a lost 2PC coordinator a manual-recovery emergency rather than a self-healing blip.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Two-Phase Commit" / §"Holding locks while in doubt":

the transaction must continue to hold [its] locks ... until it is committed or aborted. ... if the coordinator has crashed ... these locks [are] held [for] the whole time ... the data cannot be read or written by other transactions.

The book describes an in-doubt participant that cannot release its locks until the coordinator returns to tell it commit or abort. Here you create exactly that in-doubt state on a real server — a PREPARE TRANSACTION with no coordinator coming back — and confirm the locks persist across a crash.

Prerequisites

The surprise is that "prepared" is a real, durable, session-less state that outlives a restart. These help; each line notes where it shows up.

1. Two-phase commit, the prepare phase — phase 1 asks each participant to prepare: do all the work, take all the locks, promise it can commit, and make that promise durable — but do not commit yet. It shows up as PREPARE TRANSACTION 'tx1', which is precisely phase 1 exposed as SQL.
2. The in-doubt (uncertain) state — after preparing, a participant has given up its own right to abort unilaterally; it must await the coordinator's phase-2 decision. It shows up as tx1 sitting in pg_prepared_xacts, owned by no session.
3. Locks held until resolution — a prepared transaction keeps every lock it acquired, because it may still have to commit those exact changes. It shows up as a second session's UPDATE blocking on Lock:transactionid.
4. Coordinator failure — if the coordinator dies after participants prepare but before it delivers the decision, the participants are stuck holding locks with no one to tell them what to do. We simulate the coordinator simply never coming back — and add a server crash on top.

Mental model

PREPARE TRANSACTION 'tx1' is the SQL form of 2PC's phase 1. It means: "I have done all my writes and taken all my locks; I promise I can commit; I have written that promise durably to disk; I am now waiting for someone to tell me commit or abort." Three consequences fall out of that promise:

• The transaction is owned by no session. After PREPARE, your connection is free and can even disconnect. The transaction is not gone — it is detached, living in the server, listed in pg_prepared_xacts.
• It still holds every lock. It might yet have to commit those writes, so it cannot let anyone else touch the rows it changed. A second writer to the same row waits.
• It is durable. The whole point of preparing is to survive a crash so the promise is still keepable afterward. A restart re-reads it from disk, locks and all.

Only COMMIT PREPARED 'tx1' or ROLLBACK PREPARED 'tx1' — 2PC's phase 2 — resolves it and releases the locks. The reader runs the psql sessions by hand; code/prepared_transaction_locks.py is the capture harness that proves the same thing end to end, restart included.

Setup

The dedicated container (note port 5433, and max_prepared_transactions, without which PREPARE TRANSACTION errors):

docker run -d --name ddia-ch08-2pc \
  -e POSTGRES_PASSWORD=study -e POSTGRES_HOST_AUTH_METHOD=trust \
  -p 5433:5432 postgres:16 -c max_prepared_transactions=10

Create the database, table, and one account:

psql "host=localhost port=5433 user=postgres dbname=postgres" \
  -c "CREATE DATABASE ex_ch08_2pc;"
psql "host=localhost port=5433 user=postgres dbname=ex_ch08_2pc" -c "
  CREATE TABLE accounts (id int PRIMARY KEY, balance int);
  INSERT INTO accounts VALUES (1, 500);"

Open two psql sessions to ex_ch08_2pc on port 5433 — Session A and Session B. Do not read the transcripts below until you have made each prediction.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'tx1';
SELECT * FROM pg_prepared_xacts;

-- Session B
UPDATE accounts SET balance = balance + 1 WHERE id = 1;   -- (hangs)

-- meanwhile, from a third session:
SELECT wait_event_type, wait_event, state
FROM pg_stat_activity
WHERE query ILIKE 'UPDATE accounts%' AND wait_event_type = 'Lock';

docker restart ddia-ch08-2pc
# wait for readiness:
docker exec ddia-ch08-2pc pg_isready -U postgres

-- then reconnect and look again:
SELECT * FROM pg_prepared_xacts;
SELECT * FROM accounts WHERE id = 1;

-- Session B (reopened)
UPDATE accounts SET balance = balance + 1 WHERE id = 1;   -- (hangs again)

-- Session A
ROLLBACK PREPARED 'tx1';

-- afterward
SELECT * FROM pg_prepared_xacts;
SELECT * FROM accounts WHERE id = 1;

What you should see

• After PREPARE TRANSACTION 'tx1', the txn is prepared — listed in pg_prepared_xacts, owned by no session, its -100 not yet visible (balance still 500).
• A second session's UPDATE on the same row blocks indefinitely on Lock:transactionid — no timeout.
• After docker restart, pg_prepared_xacts shows the same row and the lock still blocks — the prepared txn was persisted.
• ROLLBACK PREPARED 'tx1' restores the balance to 500, empties pg_prepared_xacts, and the waiting session immediately proceeds (final balance 501 after its +1).

Why

Two-phase commit exists to make several participants agree, atomically, on commit-or-abort. Phase 1 is the prepare vote: the coordinator asks each participant "can you commit?", and a participant that answers yes has made a binding promise — it must be able to commit later even if it crashes in the meantime. To keep that promise, the participant does everything commit needs except the final flip: it performs the writes, acquires the locks, and forces the whole prepared state to durable storage. Crucially it may not release those locks, because it might still be told to commit those exact writes and must present them intact. It also may not unilaterally abort, because it might be told to commit. Between the vote and the decision it is in doubt — and the only safe thing to do while in doubt is hold on to everything. PREPARE TRANSACTION is that state made concrete: the row lock stays held, the changes stay pending, and the whole thing is fsync'd so a crash cannot lose it.

That durability is exactly what turns a lost coordinator into a catastrophe. Because the prepared state survives crashes by design, nothing automatic can clean it up: not a timeout (the participant swore to wait), not a restart (the promise is on disk precisely so a restart preserves it). If the coordinator dies after participants prepare but before it broadcasts the decision, every prepared participant is frozen — holding locks that block other transactions — until a human reads pg_prepared_xacts, discovers the coordinator's intent, and issues COMMIT PREPARED or ROLLBACK PREPARED by hand. The safety property (never lose a promised commit) and the liveness hazard (locks stuck until someone resolves them) are the same mechanism seen from two sides.

Go deeper

• Commit instead. Re-run through Step 3, then COMMIT PREPARED 'tx1'. Predict the balance (400, the -100 finally applied) and confirm the waiting Session B then adds its +1 to 401 — the other half of phase 2.
• Be the human recovering a lost coordinator. After preparing, SELECT gid, prepared, owner, database FROM pg_prepared_xacts is all the information a recovering operator has. Decide from it alone whether to commit or roll back — this is the "heuristic decision" real 2PC systems dread, because guessing wrong silently diverges the participants.
• Why this motivates avoiding 2PC. Time how long other work is blocked while tx1 sits in doubt (it is unbounded). This is the standard argument for sagas / idempotent retries / at-least-once messaging over distributed 2PC: they trade 2PC's clean atomicity for designs that never leave a participant holding locks on a promise no one will redeem.

Sources: DDIA 2e, Ch. 8, §"Atomic Commit and Two-Phase Commit (2PC)", §"Coordinator failure", §"Holding locks while in doubt" · PostgreSQL 16 manual: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, pg_prepared_xacts.