An operations team notices that after a brief network blip, several database connections show 'in-doubt' or 'prepared' transactions that never resolved, and those transactions are holding row locks that are blocking other queries. In the context of two-phase commit / XA transactions, what causes this, and what are the operator's options to resolve it?
answer
- prepared/in-doubt transaction state
- DBA_2PC_PENDING / pg_prepared_xacts
- coordinator recovery sweep replays decision
- heuristic commit/rollback = manual guess
- network blip, not just crash, can trigger it
basics
~20 sA brief network blip cut the connection between the coordinator and a database right after it said 'ready to commit', so the database is stuck waiting and holding locks. An operator can wait for the coordinator to reconnect and resolve it automatically, or manually force a commit/rollback if it's urgent.
solid answer
~50 sThis is a textbook in-doubt/prepared-transaction situation: a participant voted COMMIT (durably logging that promise and taking locks), then lost contact with the coordinator before receiving the final outcome. The database can't safely guess, so the transaction sits 'prepared,' holding locks until it learns the real answer. The safest fix is to restore connectivity and let the coordinator's normal recovery process replay its durable decision to the participant — most XA-compliant systems do this automatically once the link comes back. If the coordinator itself is gone or unreachable for a long time and the locks are causing real damage, an operator can issue a heuristic commit or rollback directly against the database, but this is a manual override taken without proof of the true decision, so it carries a real risk of ending up inconsistent with whatever the coordinator actually decided, and usually requires a follow-up reconciliation check.
go deeper
Should recognize that a stuck 'in-doubt' transaction is holding locks and blocking other work, and that waiting for reconnection is usually the fix.
Should explain what the prepared state durably contains (locks + redo/undo log) and why the participant genuinely cannot resolve it without outside information.
Should know the concrete recovery mechanisms — coordinator recovery sweep vs manual heuristic commit/rollback — and the risk (heuristic mixed outcomes) the manual path introduces.
Should discuss prevention at the systems level — coordinator HA, alerting/dashboards, timeout policy — and connect recurring incidents like this to the broader architectural decision to move workloads off 2PC/XA toward Sagas.
## The three things to connect This scenario is the operational face of the coordinator/participant uncertainty inherent to two-phase commit, and it's common enough in real XA deployments that most databases ship first-class tooling for it. Understanding it requires connecting three things: 1. what 'prepared' actually means on disk, 2. why a network blip specifically (not just a coordinator crash) triggers it, and 3. what recovery options actually exist. ## What is sitting on disk **What 'prepared'/'in-doubt' means.** When a participant database votes COMMIT during the prepare phase of 2PC, it: - durably writes the transaction's redo/undo information to its own log so it can apply or discard the change later, and - acquires and holds the row/table locks the transaction needs. At that point the transaction is 'prepared' — visible in tools like Oracle's `DBA_2PC_PENDING` view or PostgreSQL's `pg_prepared_xacts` — and it stays there until the participant receives an explicit COMMIT or ROLLBACK from the coordinator. The participant cannot infer the answer from anything local; the durable prepared state must survive indefinitely, exactly as-is, until told otherwise. ## The trigger is smaller than a crash **Why a brief network blip is enough.** This failure mode doesn't require a full coordinator crash — any interruption in the coordinator-to-participant channel around the moment the outcome is sent has the same effect. If the coordinator sends COMMIT but the blip drops the message (or acknowledgment) before it's confirmed, the participant is left prepared and waiting; the coordinator, not having received an ack, may also treat the transaction as unresolved on its side, but if the blip happened while the coordinator briefly lost the participant, it can't tell 'participant is down' from 'participant is fine but unreachable.' A blip lasting even a few seconds, badly timed, can leave a prepared transaction that requires deliberate recovery rather than resolving itself instantly once the network heals. ## Letting recovery run **Automatic recovery — the normal, safe path.** Once connectivity is restored, a well-implemented transaction manager performs recovery by scanning its own durable transaction log for any transaction it doesn't have a confirmed ack for, and re-sending the recorded outcome to the participant. The participant simply applies it and releases the locks — no new decision is made, only the original one is finally delivered. In the vast majority of real incidents, the fix genuinely is 'wait for the network to heal and the coordinator's recovery sweep to run'; most XA managers run this sweep periodically and it's non-destructive by construction, because it's just replaying a decision that was already fixed before anything went wrong. ## When an operator has to guess **Manual/heuristic recovery — the risky fallback.** The problem arises when the coordinator itself is gone for an extended period, and the held locks are actively causing business damage: - blocking a maintenance window, - queuing unrelated transactions, - contributing to an outage. In that case, an operator can manually force the participant to resolve the prepared transaction: most databases expose an explicit **heuristic commit** or **heuristic rollback** command against the specific transaction ID. This is explicitly called 'heuristic' because it's a guess made without the coordinator's authoritative record — the operator is choosing based on business judgment rather than proof. If the guess doesn't match what the coordinator (or other participants) actually decided, the transaction ends up in a heuristically-inconsistent state, which most XA implementations explicitly flag (a 'heuristic exception' or 'heuristic mixed' status) so that whoever eventually reconciles it knows to check manually rather than trust the system's usual guarantees. ## Keeping it from happening **Prevention and mitigation in production.** Teams operating 2PC/XA in practice: - invest in making the coordinator itself highly available (so blips don't turn into extended outages), - keep prepared-transaction dashboards/alerts so stuck transactions get noticed within minutes rather than hours, and - set conservative-but-not-excessive prepare timeouts so a transaction doesn't sit prepared for an unbounded time before someone even looks at it. Because the risk and operational overhead of this scenario recur, it's one of the concrete, practical reasons many teams migrate cross-service transactional workloads away from XA/2PC toward Sagas, where a failure never leaves a database silently holding locks waiting on a remote party's decision.
- Why can't the participant database just check if its own local transaction succeeded and use that to decide commit vs rollback?Its own local part always 'succeeded' in the sense that it finished preparing and durably logged its readiness — that's precisely why it's stuck in the prepared state. The actual outcome depends on whether every other participant also voted YES, which is information only the coordinator collected and recorded, not something visible from this participant's local state alone.
- What's a 'heuristic mixed' outcome, and why is it worse than a plain heuristic commit or rollback?It happens when different participants in the same distributed transaction were heuristically resolved in different directions — one operator forced a commit while another, unaware, forced a rollback on a different participant of the same transaction. It's worse because the transaction is now genuinely inconsistent across systems in a way that can't be fixed by picking one side; someone has to manually reconcile the actual data difference.
- How do prepared-transaction dashboards and alerts reduce the real-world impact of this failure mode?They shrink the time between a transaction getting stuck and a human noticing, which matters because the damage (blocked locks cascading to unrelated queries) accumulates the longer it sits unresolved. Catching it within minutes lets an operator restore connectivity or make an informed heuristic call before it snowballs into a broader outage.
Like a courier who has already signed for a package (committed to deliver it) but then loses phone signal before hearing whether the recipient actually wants it delivered or returned — they sit holding the package until the signal comes back and someone tells them the real answer, or a supervisor makes an unverified call to just deliver or return it anyway.
saying these in an interview costs you the question
- Assumes a stuck prepared transaction will just resolve itself instantly with no operator awareness needed
- Doesn't know a heuristic commit/rollback is a manual guess, not a verified decision
- Thinks the participant can determine the correct outcome from its own local state alone
- Confuses a prepared transaction with a normal open/uncommitted transaction
- Unaware that this can be triggered by a brief network issue, not only a hard coordinator crash