Two-phase commit: PREPARE TRANSACTION

PostgreSQL implements a participant in a two-phase commit protocol. This scenario prepares one local transaction, terminates its originating backend, and resolves it from another session. It does not run a global coordinator, multiple databases, or a server-crash recovery test. The PostgreSQL 18 PREPARE contract stores prepared state on disk and describes a high probability of a successful later commit, not an unconditional inability to fail.

Session A prepares, M monitors and terminates A, and B later commits the prepared transaction. The configured max_prepared_transactions=10 and superuser access enable this demonstration; the feature is disabled by the default value of zero.

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 TRANSACTION

PREPARE 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"

M terminates A's backend. No global coordinator or server process is killed.

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 closed

The originating backend is gone. The prepared entry remains, and B's next NOWAIT request still fails. Server-restart durability is a manual contract, not an executed test here.

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 prepared transaction retains an old xid horizon. Check occupied tuple slots around VACUUM in ledger, an unrelated table updated after prepare. This does not imply all VACUUM work or every table's reclamation stops.

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 1

Four 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;
VACUUM

The next count checks that these four occupied tuple slots remain after VACUUM, not that all maintenance was a no-op.

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 runs outside a transaction block. B, a superuser in the same database, commits the prepared transaction by name.

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.6 · Run it yourself · Scenario source

A no longer sees its prepared update in a new standalone read. The prepared entry persists after A's backend terminates, and B's NOWAIT locking read fails with 55P03 both before and after termination. B commits the prepared transaction and reads balance 200. These are Demonstrated behaviors, not evidence that every caller has permission to resolve prepared work or that global atomicity has been exercised.

Why that durability is also the danger

The scenario updates the unrelated ledger row three times after prepare. It asserts four occupied tuple slots before and after VACUUM, then one after COMMIT PREPARED and another VACUUM. The retained transaction horizon constrains removal of these versions; it does not make every VACUUM task or every table's reclamation stop. It does not mean a prepared snapshot reads every intermediate version.

The PREPARE manual warns that long-lived prepared transactions interfere with storage reclamation and can contribute to wraparound shutdown. They retain their locks until resolved. Monitor pg_prepared_xacts and arrange explicit recovery of unresolved work. No automatic expiry is supplied by this protocol; the scenario tests no timeout or restart.

Should you use it?

The manual reserves this facility for external transaction managers. COMMIT PREPARED and ROLLBACK PREPARED must run outside a transaction block, in the same database, by the owner or a superuser. Only COMMIT PREPARED is exercised here. The outbox and sagas have different boundaries and recovery responsibilities; neither is a universal replacement for 2PC.

Further reading

• PostgreSQL 18: PREPARE TRANSACTION
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.