Two-phase commit: PREPARE TRANSACTION
Sagas gave up on atomicity across systems and engineered around it. Two-phase commit (2PC) is the other road: actually commit across multiple databases, by splitting COMMIT in two. Phase one, every participant prepares — gets its transaction to the point where commit can no longer fail, and promises to hold that pose. Phase two, a coordinator tells everyone to commit for real. PostgreSQL implements a participant's side natively, and the primitive is worth seeing even if you never deploy it. The manual, after PREPARE TRANSACTION, "the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested".
"No longer associated with the current session" is not a figure of speech. Three sessions carry the demo, so here is the whole path first: Session A is the participant that prepares, Session M is a monitor watching the server's state, and Session B is the unrelated session that later finishes the job.
A is one participant in a distributed transfer. It does its work, then PREPARES — phase one.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
A> PREPARE TRANSACTION 'transfer-42';
PREPARE TRANSACTIONPREPARE detached the transaction from the session: A itself no longer sees its own change.
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
M> SELECT gid, owner, database FROM pg_prepared_xacts;
gid | owner | database
-------------+----------+----------
transfer-42 | postgres | postgres
(1 row)The prepared transaction still holds its row locks — with no session attached.
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- lock_not_available
ERROR: 55P03: could not obtain lock on row in relation "accounts"Now the coordinator crashes: A's backend is killed outright.
M> SELECT pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity WHERE application_name = 'A';
terminated
------------
t
(1 row)
A> SELECT 1;
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closedThe session is gone. The prepared transaction is not — it survives anything short of COMMIT/ROLLBACK PREPARED, including a full server restart. And it still holds its locks:
M> SELECT gid FROM pg_prepared_xacts;
gid
-------------
transfer-42
(1 row)
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR: 55P03: could not obtain lock on row in relation "accounts"The locks are the visible damage. The quiet damage is the xid horizon: the orphan still counts as a running transaction, so no snapshot taken since it prepared can be ruled out. Watch it freeze VACUUM in ledger — a table the prepared transaction never touched.
B> UPDATE ledger SET n = 1 WHERE id = 1;
UPDATE 1
B> UPDATE ledger SET n = 2 WHERE id = 1;
UPDATE 1
B> UPDATE ledger SET n = 3 WHERE id = 1;
UPDATE 1Four versions of one row on the page — three of them dead.
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
4
(1 row)
B> VACUUM ledger;
VACUUMVACUUM ran, reported success, and reclaimed nothing. The orphan's horizon still covers every one of those versions.
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
4
(1 row)Phase two — any session can finish the job by name. B commits the orphan.
B> COMMIT PREPARED 'transfer-42';
COMMIT PREPARED
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)And the horizon moves. The same VACUUM, a moment later, finally collects the three dead versions.
B> VACUUM ledger;
VACUUM
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
1
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The session that created the transaction can't see its changes anymore — the transaction has left the session and lives on disk now. Then the scenario gets violent.
Everything this site showed about crashes so far — "PostgreSQL rolls back open transactions on disconnect" — stops at PREPARE. The killed backend took nothing with it: the transaction, its locks, its promise all survive, waiting for anyone to say COMMIT PREPARED 'transfer-42' or ROLLBACK PREPARED 'transfer-42'.
Why that durability is also the danger
The survival superpower has a flip side: nothing expires a prepared transaction. Crash recovery in chapter 1 was automatic; here, a coordinator that dies between the phases leaves orphans that hold row locks and pin the xid horizon exactly like a long transaction, until a human or a transaction manager resolves them.
The two VACUUM ledger calls are the part that catches teams out. ledger has nothing to do with the transfer: the orphan never read it, never wrote it, never locked it. VACUUM still declines to collect a single dead version there, because a prepared transaction counts as running and its snapshot might yet need every one of them. One forgotten gid freezes garbage collection across the whole database, and the table that bloats is rarely the table anyone was looking at. The manual is unusually stern: "It is unwise to leave transactions in the prepared state for a long time. This will interfere with the ability of VACUUM to reclaim storage, and in extreme cases could cause the database to shut down to prevent transaction ID wraparound", and "Keep in mind also that the transaction continues to hold whatever locks it held". Check pg_prepared_xacts whenever locks seem stuck and no session admits to holding them.
Should you use it?
Straight from the manual: "PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources". PostgreSQL even ships with the feature off: "Setting this parameter to zero (which is the default) disables the prepared-transaction feature" — this site's docker-compose sets max_prepared_transactions=10 precisely so this scenario can run. Unless an XA transaction manager owns both phases (including recovery of orphans!), the outbox and sagas give you the guarantees you actually need with failure modes you can sleep through.
So the primitive earns its keep strictly as a primitive: PREPARE TRANSACTION detaches a transaction from its session and makes it crash-proof, and COMMIT PREPARED or ROLLBACK PREPARED finishes it from any session, by name. Between the phases it holds every lock and blocks VACUUM, with no timeout and no automatic cleanup, so pg_prepared_xacts is the view you keep an eye on. It's a building block for external transaction managers, not an application tool — for anything you would actually ship, reach for the outbox and sagas first.