# DDIA Ch. 8: a prepared transaction holds its lock through a full server restart

## 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.

### Where to learn the prerequisites

- **2PC, prepare, in-doubt, and held locks (#1–#4):** DDIA §"Atomic Commit and
  Two-Phase Commit (2PC)", §"Coordinator failure", and §"Holding locks while in
  doubt".
- **How Postgres exposes phase 1 (#1–#3):** the PostgreSQL manual on
  [`PREPARE TRANSACTION`](https://www.postgresql.org/docs/16/sql-prepare-transaction.html),
  [`COMMIT PREPARED`](https://www.postgresql.org/docs/16/sql-commit-prepared.html) /
  [`ROLLBACK PREPARED`](https://www.postgresql.org/docs/16/sql-rollback-prepared.html),
  and the `pg_prepared_xacts` view.

If only one is new, make it #3 — that a prepared transaction *keeps its locks*
is the whole reason a lost coordinator is catastrophic.

## Environment

The behaviors here (in-doubt txn holds its lock, blocks a second session, and
survives a full restart) are 100% reproducible:

- macOS 26.5.2 (Darwin 25.5.0), arm64
- PostgreSQL 16.14 (Debian, aarch64) in Docker, started with
  `max_prepared_transactions=10` (a `PREPARE TRANSACTION` fails without it)
- Sessions are real `psql` connections on **port 5433**; the restart is a real
  `docker restart`
- The `transaction` id (`759` below) and the `prepared` timestamp in
  `pg_prepared_xacts` vary run to run; the *behavior* — same row before and after
  the restart, still holding the lock — does not

The full orchestration was captured via `psycopg[binary]` on Python 3.12 (uv) by
`code/prepared_transaction_locks.py`, which drives the same sequence — including
the `docker restart` and a blocking second session on a thread — so the printed
transcript is real.

## 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.

## Steps

### Step 1 — prepare tx1, then look for it (Session A)

**Predict.** In Session A run the three statements below. After
`PREPARE TRANSACTION 'tx1'`, is the transaction committed? Rolled back? Gone? And
what does `SELECT * FROM pg_prepared_xacts;` show?

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

**Observe.**

```
BEGIN
UPDATE 1
PREPARE TRANSACTION

 transaction | gid |           prepared            |  owner   |  database
-------------+-----+-------------------------------+----------+-------------
         759 | tx1 | 2026-07-23 13:51:29.583796+00 | postgres | ex_ch08_2pc
(1 row)
```

**Neither committed nor rolled back — it is *prepared*, and Session A is now
free.** The transaction detached from your connection and became a durable,
session-less object the server tracks in `pg_prepared_xacts`. Notice the `-100`
is nowhere visible yet — `SELECT balance FROM accounts WHERE id=1` still returns
`500`, because tx1 has not committed.

### Step 2 — a second writer blocks (Session B)

**Predict.** In Session B, update the *same* row: `UPDATE accounts SET balance =
balance + 1 WHERE id = 1;`. Does it succeed, error, wait a few seconds and time
out, or hang forever?

```
-- 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';
```

**Observe.**

```
 wait_event_type |  wait_event   | state
-----------------+---------------+--------
 Lock            | transactionid | active
(1 row)
```

**Session B blocks — and stays blocked.** It is waiting on `Lock:transactionid`:
the lock tx1 took when it wrote row 1. There is no timeout on this wait; Session B
will sit here for as long as tx1 remains in doubt. The in-doubt transaction has
frozen the row.

### Step 3 — restart the whole server (does that clear it?)

**Predict.** A prepared transaction with no session, holding a lock, looks like
an orphan. Surely restarting the database clears it? Run:

```
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;
```

(The restart kills Session B's connection — that's expected; reopen it in
Step 4.)

**Observe.**

```
 transaction | gid |           prepared            |  owner   |  database
-------------+-----+-------------------------------+----------+-------------
         759 | tx1 | 2026-07-23 13:51:29.583796+00 | postgres | ex_ch08_2pc
(1 row)

 id | balance
----+---------
  1 |     500
```

**Still there — the *same* row, same transaction id, same prepared timestamp.**
The restart did not clear it; Postgres re-read the prepared transaction from disk
on startup, locks and all. The balance still reads `500`: tx1's uncommitted `-100`
survived too, still pending. Durability is not a bug here — it is the entire
point of preparing.

### Step 4 — a fresh writer *still* blocks; then resolve it (Sessions B & A)

**Predict.** Reopen Session B and run the same `UPDATE ... WHERE id = 1`. Does it
block again? Then, in Session A, run `ROLLBACK PREPARED 'tx1';`. What happens to
the balance, and to the blocked Session B?

```
-- 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;
```

**Observe.**

```
-- Session A:
ROLLBACK PREPARED

-- Session B, which had been hanging, immediately returns:
UPDATE 1

-- pg_prepared_xacts is now empty:
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

-- balance: rollback undid the -100 (back to 500); Session B's +1 then applied:
 id | balance
----+---------
  1 |     501
```

**The rollback frees the lock, and Session B — blocked all this time — completes
the instant tx1 resolves.** `ROLLBACK PREPARED` undid tx1's `-100`, so the
balance returned to `500`; Session B's long-waiting `+1` then committed, giving
`501`. `pg_prepared_xacts` is empty; the in-doubt state is gone. Only phase 2
could end it.

## 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.

**The boundary — only distributed/2PC transactions can enter this state.** An
ordinary single-node transaction never becomes in-doubt: it commits or aborts
atomically at one `COMMIT`, and a crash before that point simply rolls it back on
recovery, releasing its locks. There is no window where it holds locks with no
owner and no decision pending. That window opens *only* when you split the commit
into prepare-then-decide — i.e. when a coordinator needs several participants to
agree — which is why `PREPARE TRANSACTION` requires you to opt in with
`max_prepared_transactions > 0` and is otherwise disabled. The hazard is
intrinsic to distributed atomic commit, not to transactions in general.

### Go deeper

1. **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.
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.
3. **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.
```