Track Two-Phase Commit State with pg_prepared_xacts

calendar Jun 19, 2026 / · 7 min read · postgresql database administration sql queries transactions two-phase commit pg_prepared_xacts postgres dba  ·
Share on: linkedin copy

Track Two-Phase Commit State with pg_prepared_xacts

A prepared transaction that never gets committed or rolled back is one of the quieter ways a PostgreSQL database degrades. It keeps holding locks, it pins the transaction-ID horizon so VACUUM cannot reclaim dead rows, and nothing in normal monitoring screams about it. Left long enough it can drive the cluster toward a transaction-ID wraparound shutdown.

Purpose and Overview

Two-phase commit (2PC) splits a commit into two steps so an external coordinator can commit the same logical transaction across several resources atomically. In PostgreSQL the first step is PREPARE TRANSACTION 'gid', which writes the transaction's state to disk and assigns it a global identifier. The transaction is now durable but undecided: it survives a server restart and waits for a later COMMIT PREPARED 'gid' or ROLLBACK PREPARED 'gid', possibly from a different session.

That durability is exactly what makes an abandoned prepared transaction dangerous. If the coordinator crashes, the application is redeployed, or a distributed transaction manager loses track of a branch, the prepared transaction sits on disk indefinitely. It continues to hold every lock it acquired, and its transaction ID stays "in progress" from the cleanup machinery's point of view — so autovacuum cannot advance past it, and dead tuples newer than that XID pile up across the whole database.

The pg_prepared_xacts view is the single place to see these. It lists one row per prepared-but-undecided transaction with its numeric transaction ID, its global identifier (gid), when it was prepared, the owning role, and the database it belongs to. The prepared timestamp is the column that matters most: anything hours or days old is almost certainly orphaned rather than mid-flight.

Sample Code

 1SELECT
 2    gid,
 3    database,
 4    owner,
 5    transaction AS xid,
 6    prepared,
 7    now() - prepared AS age
 8FROM
 9    pg_prepared_xacts
10ORDER BY
11    prepared;

Notes: Works on every PostgreSQL version that supports two-phase commit (8.1+), provided max_prepared_transactions is greater than zero. The view is cluster-wide — it shows prepared transactions for all databases, not just the one you are connected to, though you can only COMMIT PREPARED or ROLLBACK PREPARED them while connected to their home database. An empty result is the healthy state on most systems.

Code Breakdown

The query is a straight read of the view with one computed column, ordered so the oldest — and therefore most suspicious — entries surface first.

The age Column

1now() - prepared AS age

Subtracting the prepared timestamp from now() yields an interval that reads directly as risk. A prepared transaction a few hundred milliseconds old is a live 2PC in flight; one that is several hours or days old has been abandoned by whatever was supposed to finish it. Sorting by prepared ascending puts the oldest at the top of the result.

gid, the Decision Key

1gid

The global identifier is the string you pass to COMMIT PREPARED or ROLLBACK PREPARED. It is assigned by whatever issued the PREPARE TRANSACTION — often an application server or an XA transaction manager — so its format reflects that system's conventions. You cannot resolve a stuck transaction without it.

database and owner

These two columns tell you where and as whom to act. Because resolution must happen from inside the transaction's home database, and because you must be the original owner or a superuser to decide it, both columns are operational prerequisites rather than decoration.

Key Two-Phase Commit Concepts

PREPARE TRANSACTION

PREPARE TRANSACTION 'gid' ends the current transaction's interactive phase and persists its work in an undecided state. After it runs, the session is no longer in a transaction block, but the work is neither visible to other sessions nor rolled back. It is held, durably, awaiting a verdict.

COMMIT PREPARED and ROLLBACK PREPARED

These are the verdicts. COMMIT PREPARED 'gid' finalizes the held work and makes it visible; ROLLBACK PREPARED 'gid' discards it. Either one releases the locks and removes the row from pg_prepared_xacts. Crucially, they can be issued from a different session than the one that prepared the transaction — that is the entire point of 2PC, and the reason an orphan can outlive the connection that created it.

max_prepared_transactions

This configuration parameter caps how many transactions can be prepared at once and must be greater than zero for 2PC to work at all. It defaults to zero on many builds, which disables the feature entirely. If your application does not use a distributed transaction manager, leaving it at zero is the safest setting — it makes orphaned prepared transactions impossible by construction.

Practical Applications

Routine Orphan Sweep

Schedule the audit query and alert on any row whose age exceeds a threshold appropriate to your workload — a few minutes is generous for most non-distributed systems. A persistent old entry is a signal that a coordinator failed to finish its job.

Diagnosing Blocked VACUUM

When autovacuum appears stuck and dead-tuple counts climb on tables that should be getting cleaned, check pg_prepared_xacts early. An old prepared transaction holds back the cleanup horizon for the entire cluster, so the symptom can appear far from the transaction's actual data.

Investigating Stuck Locks

If sessions are waiting on locks with no apparent blocker among active backends, a prepared transaction may be the holder. Because it has no live session, it will not appear in a casual scan of activity — but its locks are still in force, and this view is where you find the culprit.

Safe Resolution

Once you have confirmed a transaction is genuinely orphaned, connect to its home database as the owner or a superuser and issue ROLLBACK PREPARED 'gid' (or COMMIT PREPARED if the distributed outcome should have been a commit). Resolve only transactions you have positively confirmed are abandoned — committing or rolling back a live distributed branch can break atomicity for the larger transaction.

Version Compatibility

Two-phase commit and the pg_prepared_xacts view have been present since PostgreSQL 8.1, and the view's columns — transaction, gid, prepared, owner, database — have been stable across all currently supported releases through 18. The behavior that makes orphans harmful is equally long-standing: a prepared transaction's XID has always pinned the cleanup horizon, and prepared state has always survived restarts.

The one thing that varies by deployment is whether 2PC is enabled at all. Because max_prepared_transactions defaults to zero on many packaged builds, plenty of clusters cannot create prepared transactions — and on those the view is simply always empty. Confirm the setting before assuming the feature is in play.

Best Practices

  • Leave max_prepared_transactions at zero unless you need 2PC — if no external transaction manager coordinates commits, disabling prepared transactions removes the entire orphan failure mode.
  • Alert on prepared age, not just count — a brief prepared transaction is normal under 2PC; an old one is the problem. Threshold on now() - prepared.
  • Resolve from the home database as the owner — COMMIT PREPARED and ROLLBACK PREPARED only work while connected to the transaction's database, by the original owner or a superuser.
  • Confirm orphan status before deciding — never blindly roll back a prepared transaction that might still be a live distributed branch; verify the coordinator has actually given up.
  • Watch the link to VACUUM — treat unexplained autovacuum stalls and rising dead-tuple counts as a prompt to check this view.

References

Posts in this series