In-Doubt Transactions, Held Locks, and Why Timeouts Cannot Help

A prepared transaction is the strangest object a database can hold. It has been written to disk, it owns locks, it has a snapshot that other transactions must respect, and yet it has no owner and no outcome. The client that started it may be gone, the coordinator that will decide it may be unreachable, and the local server has no rule that produces an answer. This state is called in-doubt, and everything about two-phase commit’s operational cost follows from it.

Why the local server cannot resolve it

The temptation is to impose a timeout. A prepared transaction that has waited more than N seconds gets rolled back, the locks are released, the system moves on. This is wrong for a reason that is worth stating precisely, because the wrongness is not a matter of degree.

Suppose participant A times out and rolls back while the coordinator has already decided commit. Participant B is healthy, receives the commit, and commits. The transaction is now half-applied. A’s changes are gone; B’s are durable. No later step can repair this, because A has forgotten the transaction and cannot reapply it: the original values it would need to recompute the update are themselves gone. Atomicity is not merely delayed; it is destroyed.

The only local information a prepared participant has is its own prepare record and its own vote. Both say yes. Neither says what the transaction’s fate is, because the fate depends on votes it cannot see. A participant could ask its peers, and if any peer has received the decision, the answer propagates. That is a legitimate and useful resolution path. But if the coordinator is unreachable and no peer has heard, the answer does not exist anywhere in the system, and a timeout is a guess that can be wrong.

So the participant waits. The wait is bounded below by the coordinator’s recovery time, and in the worst case by human intervention. Meanwhile every lock it holds is a lock no one else can take.

The lock cascade

The damage from one in-doubt transaction is not confined to the rows it touched. Consider a transaction that updated an index entry, a heap tuple, and a row in a secondary table. Those locks block other transactions, which block others, until the waiting set spans a large fraction of the working set. In a system with a lock manager that has a fixed number of lock slots, an in-doubt transaction can also consume memory that other sessions need.

Two mechanisms make this visible. First, lock waits appear as a graph, and a wait-for cycle caused by a prepared transaction is not resolvable by aborting a victim, because the prepared transaction is not a candidate for victim selection: it has already voted. Deadlock detection that would normally break the cycle by aborting a participant instead finds a cycle it cannot break, which is a distinct condition from ordinary deadlock.

Second, the waiters accumulate. A server with a connection limit will refuse new work once enough sessions are parked on locks held by the prepared transaction, which converts a single stuck commit into an availability event for everything that touches the same rows. The blast radius is proportional to how central those rows are, not to how many rows the transaction changed.

PostgreSQL makes the state inspectable through pg_prepared_xacts, which lists the transaction identifier, the prepare time, the owner, and the database for each prepared transaction on the server. The prepare timestamp is the operationally important column: it is the only signal that a prepared transaction has been waiting longer than the coordinator’s normal recovery window, which is when an operator should start asking whether the coordinator’s log survived.

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;

What the coordinator owes the participant

The protocol’s resolution path runs through the coordinator’s log, so the participant’s wait is really a wait for a durable read. Three outcomes are possible.

The coordinator is alive or recovers, reads its log, and finds a commit record. It resends the commit. The participant applies and releases. This is the common case and it costs one round trip plus the coordinator’s recovery time.

The coordinator recovers and finds no record at all. Under presumed abort the answer is abort, and the participant rolls back. This is also clean, and it is why the default matters: total loss of the coordinator’s log still produces a consistent outcome, as described in presumed abort and presumed commit.

The coordinator’s log is lost in a way that leaves the decision ambiguous: the coordinator has records for some transactions from the same period but not this one, or the log was restored from a backup that predates the decision. Now the absence of a record is not evidence of abort, because a record may have existed and been lost. The protocol has no rule for this. An operator must decide, and the decision must be applied identically to every participant, which means enumerating them from the coordinator’s pre-crash records and issuing the same outcome to all.

That last case is the reason distributed transaction deployments treat the coordinator’s log as the most critical storage in the system, and the reason some systems refuse to run two-phase commit across independent failure domains without a replicated decision log.

Heuristic outcomes and the price of guessing

Most transaction managers offer an escape hatch. When a participant cannot reach the coordinator within a configured window, it may resolve the transaction itself and mark the result as heuristic. The XA interface names these states explicitly: a heuristic commit, a heuristic rollback, and a heuristic end where the resource did some mix of the two. The naming is honest: the word heuristic marks the outcome as a guess made without the decision.

The value of heuristics is availability. A resource that resolves its in-doubt transactions after a timeout keeps serving, and the system degrades from “everything touching those rows is blocked” to “some transactions may be inconsistent”. The cost is that the inconsistency is now a data repair problem, and it is undetectable from inside any single resource: each resource believes it did the right thing.

Because the mismatch is invisible locally, a resource that supports heuristics must record the outcome and expose it. XA’s xa_recover returns a list of prepared transaction identifiers, and a heuristic outcome appears in that list with a distinct flag so that an external tool can find the divergent transaction and reconcile it. Without that enumeration, a heuristic rollback is indistinguishable from a normal rollback and the divergence is never found.

The design question is who is allowed to guess. A resource acting alone is guessing. A transaction manager that has heard from a majority of participants and knows the vote tally is not guessing in the same way: if any participant voted no, the decision is abort regardless of the coordinator’s log, because a commit requires unanimity. That is a genuine recovery path, and it is why a transaction manager should be able to reconstruct a decision from participant votes when its own log is gone.

Reducing the window instead of resolving it

Since the blocking window cannot be eliminated within the protocol, the practical approaches shrink it or make it survivable.

Co-locating the coordinator with the participant that holds the most locks removes one remote dependency for the critical participant. If the coordinator process dies with the participant’s process, both restart together, and the decision is read from a log on the same machine.

Shortening the interval between vote and decision is the other lever. The coordinator should decide as soon as the last vote arrives, without batching decisions for later, because a decision delayed is a lock held. Some implementations defer the commit record write until several transactions can be flushed together, which reduces log bandwidth but lengthens the in-doubt interval for every transaction in the batch. The trade is explicit: fewer flushes against longer lock holds.

Limiting the number of participants limits the probability that at least one is unreachable at decision time. A transaction that spans two nodes has one remote failure to worry about; one that spans twenty has nineteen. Since the failure probability compounds, designs that partition data so that most transactions touch one node are worth more than any protocol optimisation, which is the argument for the sharding strategies discussed under partitioning.

Finally, some systems avoid the protocol entirely for transactions that do not need it. A transaction that writes to one node and reads from another needs no prepare at all if the read can be repeated or is not part of the atomic unit. Distinguishing those cases is a schema and application design question, but it removes the largest class of transactions from the blocking risk.

What the state tells you about the system

An in-doubt transaction is a probe into the system’s failure model. Its existence means the coordinator’s decision is not yet available; its duration means the coordinator has been unavailable longer than expected; the number of them means the coordinator’s failure affected a batch of transactions rather than one.

The operational rule that follows is to treat prepared transaction age as a first-class signal rather than an error to be cleared. A single prepared transaction older than the coordinator’s normal restart time is either a coordinator that is down and will return, or a coordinator that has lost its log and will not. Those two cases require opposite actions, and the only way to distinguish them is to look at the coordinator’s storage rather than at the participant’s lock table.