Distributed systems
Part 5 of 6 · Two-Phase Commit — Protocol, Coordinator & ParticipantsXA & Database 2PC - Postgres PREPARE, MySQL XA & Heuristic Decisions
X/Open XA, Postgres PREPARE TRANSACTION, MySQL XA, and heuristic commit or rollback.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Question ladder
L1
What is the difference between SQL PREPARE and PREPARE TRANSACTION?
Answer
PREPARE names a statement plan. PREPARE TRANSACTION parks a 2PC participant under a gid and holds locks across disconnect.
L2
Which XA call is the YES vote?
Answer
xa_prepare. The resource manager force-logs and holds locks. A read-only resource manager may return a read-only vote and leave phase 2.
L3
When is one-phase commit legal?
Answer
When a single resource manager is enlisted. The moment a second one enlists, the transaction manager prepares both unless one votes read-only.
L4
Why is max_prepared_transactions zero by default?
Answer
So a client cannot accidentally pin the xmin horizon and block vacuum. Enabling prepared transactions is a capacity and operations decision.
L5
Both databases are prepared and the app dies with no extra log. What do you run?
Answer
Nothing that finishes one side. Leave them prepared, restore the transaction manager log if you had one, or open an incident. Check both catalogs before any heuristic rollback.
L6
What does xa_recover return?
Answer
Prepared XIDs after a crash so the transaction manager can finish them. A transaction manager that loses its XID log leaks prepared transactions.
L7
A pool hands you a MySQL connection still inside an XA branch. What do you do?
Answer
Do not run application SQL on it. End or recover that XID first, or the next request's writes join a global transaction it does not know about.
Failure modes
Commit one side, then crash, with no decision log
The two resource managers can disagree and nothing durable says which outcome was chosen.
Forgotten prepare
Locks remain and vacuum cannot freeze tuples a prepared transaction might still need. That is an outage, not a leaked session.
Heuristic error swallowed by the driver
The resource manager already finished the branch alone. Hiding the error hides a split transaction.
One-phase on one branch and prepare-commit on another
That is a partial outcome. One-phase is only legal when that server never prepared.
Misconceptions
Postgres includes a distributed transaction manager.
It includes the participant. If you prepare on two instances, your application is the coordinator and must durable-log the gid list and the decision before either COMMIT PREPARED.
COMMIT PREPARED from any session is just a convenience.
It is also an admin foot-gun for heuristic commit. Restrict who can run it.
ROLLBACK PREPARED on both sides is always safe if the app died.
It is safe only if neither side has already committed. Check both pg_prepared_xacts and XA RECOVER first.
Interviewer traps
Mixing the prepared-statement statement with the 2PC statement.
Only PREPARE TRANSACTION, or XA PREPARE, holds locks across disconnect.
Inventing a second coordinator in the binlog.
The binary log has to agree with InnoDB about the XA outcome. Read the current crash-safety pages rather than adding another decider.
Design scenario
Same prompt for every reader.
Requirements
Either both commits happen or neither does. A process crash between the two commits must still be recoverable.
Traffic / scale
Tens of these transactions per second, two branches each.
Latency
The caller waits on prepare forces plus the transaction-manager decision force.
Consistency
Atomic commit across the two resource managers. Heuristic finish is an incident.
Availability
Prepared rows stay locked until the XID log says otherwise.
Failure assumptions
- The application process can die after both prepares and before either commit.
- An operator may be tempted to clear pg_prepared_xacts by hand.
Constraints
- Do not one-phase commit one side and prepare the other.
- Do not store the decision only in process memory.
Prompt
An order row in Postgres and a balance row in MySQL must move together. Both servers can prepare and recover. You operate them in one region.
API
Which component calls xa_prepare, xa_commit, and xa_recover?
Data
What is written before either resource manager is told to commit?
Architecture
Where does the XID log live, and who is paged on prepared age?
Who is allowed to be the coordinator
Prefer
A real transaction manager with a durable XID log
Prepare both resource managers, force the decision, then commit or roll back every prepared branch. Recovery reads xa_recover and pg_prepared_xacts and finishes the book.
- The decision hits stable storage before either side is told to commit.
- One-phase commit stays reserved for a single enlistment.
- Prepared age is paged before vacuum and lock queues stall.
Alternative
The application process as an unlogged coordinator
Prepare both, commit one, crash. You have invented the bug the coordinator log exists to prevent.
- There is no decision to replay.
- Manual COMMIT PREPARED on one side is a heuristic.
- A third-party HTTP API still cannot sit in xa_prepare.
Enlist, prepare, decide, recover
The SQL differs. The state machine does not.
- 1
Associate the work
xa_start, or a local BEGIN, binds this unit of work to a global transaction id. The application does ordinary SQL. xa_end finishes the association. - 2
Prepare is the vote
xa_prepare or PREPARE TRANSACTION force-logs and holds locks. A read-only resource manager can leave phase 2. One-phase commit is legal only when this is the only resource manager. - 3
Force the decision, then tell them
The transaction manager writes commit or abort for that XID before it calls xa_commit, COMMIT PREPARED, xa_rollback, or ROLLBACK PREPARED on either side. - 4
Recover on restart
xa_recover and pg_prepared_xacts list prepared branches. Finish them from the XID log. A branch with no log entry stays prepared until a human records a heuristic.
Overview
XA (X/Open Distributed Transaction Processing) is the API shape of 2PC between a transaction manager and resource managers. Postgres PREPARE TRANSACTION and COMMIT PREPARED, and MySQL XA PREPARE and XA COMMIT, are the same state machine exposed as SQL. The protocol rules are on prepare and commit and failures and recovery. This page is how those rules show up in engines, and how heuristic decisions break them on purpose.
X/Open actors
| Actor | Role in 2PC words |
|---|---|
| AP, application program | Begins and ends the global transaction. Should not talk to resource managers behind the transaction manager's back |
| TM, transaction manager | Coordinator. Calls prepare, then commit or rollback, on every enlisted resource manager |
| RM, resource manager | Participant. Database, sometimes a queue. Implements prepare, commit, rollback, and recover |
| CRM | Communication resource manager. Irrelevant until you are tracing a real transaction network |
The calls that matter:
xa_startassociates the thread's work with a global transaction id (XID).- The application does normal SQL.
xa_endfinishes the association.xa_prepareis the YES vote. The resource manager force-logs and holds locks. Read-only resource managers may return a read-only vote so the transaction manager drops them from phase 2. That vote is on optimizations.xa_commitorxa_rollbackdelivers the decision. There is a one-phase form of commit when only one resource manager is enlisted: the transaction manager skips prepare and commits that resource manager directly. Using one-phase across two resource managers is a bug.xa_recoverlists prepared XIDs after a crash so the transaction manager can finish them. This is not optional. A transaction manager that loses its log of XIDs leaks prepared transactions forever.
XIDs have a global transaction id and a branch id so one global transaction can have two branches on the same resource manager. Format flags are easy to get wrong in hand-rolled drivers. Prefer a real transaction manager over a homegrown coordinator unless you are ready to own recovery.
Postgres
PREPARE TRANSACTION is participant prepare, not SQL PREPARE (the prepared-statement feature). After it returns:
- The session's transaction is closed, but the work is not committed.
- Locks remain.
pg_locksstill shows them, owned by a prepared transaction. - The row versions stay live. Vacuum cannot freeze or remove tuples that a prepared transaction can still need. A forgotten prepare is a vacuum outage, not just a lock.
- The gid shows in
pg_prepared_xactsuntilCOMMIT PREPAREDorROLLBACK PREPARED. - Crash recovery reloads prepared transactions. They are not rolled back just because the backend died. That is the PREPARED log record winning over the default abort.
You must set max_prepared_transactions above 0 or PREPARE TRANSACTION fails. The default is 0 so an accidental prepare cannot pin xmin forever. Size it for the number of prepared transactions you can have in flight, often one per connection that might prepare, and remember each slot costs shared memory.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'order-8842-pg';
-- locks still held; gid is visible in pg_prepared_xacts
COMMIT PREPARED 'order-8842-pg';
-- or ROLLBACK PREPARED 'order-8842-pg' when the TM decided abortCOMMIT PREPARED is allowed from any session that can see the gid, not only the one that prepared. That is convenient and dangerous. It is an admin foot-gun for heuristic commit. Restrict who can run it.
Postgres does not include a distributed transaction manager. If you prepare on two Postgres instances, your application is the coordinator and must durable-log the gid list and the decision before it calls COMMIT PREPARED on either. A process that prepares both, then commits one, then crashes without a log, is a bad transaction manager.
MySQL
XA START 'order-8842';
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
XA END 'order-8842';
XA PREPARE 'order-8842';
-- crash here: the row stays locked, XA RECOVER lists the xid
XA COMMIT 'order-8842';
-- or XA ROLLBACK 'order-8842'XA RECOVER returns prepared branches after restart. The transaction manager must drive them. XA COMMIT with the one-phase keyword is only legal when this server never prepared, that is, a single-resource-manager commit. Calling one-phase commit on one branch and prepare-commit on another is how you get a partial outcome.
InnoDB stores the XA state so prepared transactions survive mysqld restart. The binary log has to agree with InnoDB about whether the XA transaction committed. That crash window is a sharp edge. Modern MySQL has tightened it. Do not invent a second coordinator inside the replica stream. If you need the exact version behavior, read the current XA and binary-log crash-safety pages rather than a blog from 2014.
Heuristic decisions
The XA spec assumes an operator, or a desperate resource manager, sometimes completes a prepared branch without the transaction manager.
| Flag | Meaning | Atomicity |
|---|---|---|
| Heuristic commit | This branch committed on its own | Broken if the transaction manager aborts other branches |
| Heuristic rollback | This branch rolled back on its own | Broken if the transaction manager commits other branches |
| Heuristic mixed | Parts of one branch went different ways | Already broken inside the resource manager |
| Heuristic hazard | The resource manager cannot tell whether it mixed | Treat as mixed until proven otherwise |
After a heuristic, the resource manager remembers it until the transaction manager calls forget. If your driver swallows the heuristic error and continues, you have hidden a split-brain transaction.
Postgres does not use the same XA SQLSTATE family, but ROLLBACK PREPARED while the other database already committed is the same act. MySQL can return heuristic errors from XA COMMIT or XA ROLLBACK when the server already unilaterally finished the branch.
Reconciliation is application-specific. Compare both ledgers by gid, compensate the way a saga would, or restore from a backup plus the log. There is no generic SQL that makes a heuristic mix atomic again. If you are in compensation territory, read Two-Phase Commit vs Sagas and the saga hub. Do not invent that cluster here.
Why teams turn XA off
- Two log forces plus two network rounds, often inside a user request.
- Locks held across process and network failure, not just across a local commit.
- The transaction manager is now a production dependency with its own disk. An application fleet whose recovery log is a single node reintroduces the coordinator crash this series exists to explain. If that log must survive one machine, it is a quorum log behind a coordinator, which is the tradeoffs page, not a Raft lecture.
- Connection pools must not reuse a connection that is still associated with an XID. Leaked associations prepare the wrong work.
max_prepared_transactionsleft high with a buggy client is a slow outage through the xmin horizon.- Heterogeneous XA fails in the ways each resource manager's timeout and heuristic policy fails, and those policies do not match.
Turn it off when the business can live with a saga or an outbox, when you can colocate the writes on one resource manager, or when one-phase commit against a single database is the truth. Keep it when you genuinely need all-or-nothing across two resource managers you operate and you will page on prepared-transaction age.
Decisions
- 1
1. Writes in two systems
- next2. Both can prepare and recover?
- ?
2. Both can prepare and recover?
- no3. Saga or outbox, do not fake XA
- yes4. Can both writes share one RM?
- 3
3. Saga or outbox, do not fake XA
- ?
4. Can both writes share one RM?
- yes5. One local transaction
- no6. TM with a durable XID log
- 5
5. One local transaction
- 6
6. TM with a durable XID log
- next7. Page on prepared age
- 7
7. Page on prepared age
- next8. A manual finish is a heuristic
- 8
8. A manual finish is a heuristic
Lesson map
XA prepare on two engines
Both engines are prepared and locked. The transaction manager has the xid list and has not announced the decision.
Architecture. Transaction manager Deciding. Postgres Locked · prepared. MySQL Locked · prepared
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB tm["Transaction manager Deciding"] postgres["Postgres Locked prepared"] mysql["MySQL Locked prepared"] tm -->|PREPARE| postgres tm -->|XA PREPARE| mysql tm -->|COMMIT PREPARED| postgres tm -->|XA COMMIT| mysql postgres -->|Recover| tm postgres -->|Lock held| tm mysql -->|Lock held| tm postgres -->|Heuristic| mysql postgres -->|Reconcile| mysql
Sandbox
Two resource managers have prepared. The only safe commits are those named by a durable decision, written before either side is told.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
If the log write happens after the first resource-manager commit, a crash between the two calls is a split outcome with nothing to recover from. That ordering bug is the whole XA transaction manager.
Pitfalls
- A connection pool that reuses an XA-associated session enlists the next request by accident.
- Leaving
max_prepared_transactionsat zero is the safe default. Raising it without an owner forpg_prepared_xactsis how vacuum stalls. - Heterogeneous timeouts do not agree. One resource manager's heuristic policy is not the other's.
Interview Q&A
What is the difference between SQL PREPARE and PREPARE TRANSACTION?
Answer
PREPARE creates a named statement plan. PREPARE TRANSACTION ends the session's transaction and parks a 2PC participant under a gid. Mixing the names is a miss. Only the second one holds locks across disconnect.
Why is the default of max_prepared_transactions zero?
Answer
So a client cannot accidentally pin the xmin horizon and block vacuum. Enabling XA on Postgres is an explicit capacity and operations decision, not a connection-string flag you flip on Friday.
Both databases are prepared and the app process dies with no extra log. What do you run?
Answer
You do not know the decision, so you must not COMMIT PREPARED on one side to finish the request. Leave them prepared, restore the transaction manager's decision from its log if you had one, or start an incident and a reconciliation if you did not. ROLLBACK PREPARED on both is a heuristic abort and is only safe if no side has already committed. Check both pg_prepared_xacts and XA RECOVER before touching either.
When is one-phase commit legal?
Answer
When a single resource manager is enlisted. That resource manager's commit is the global commit. The moment a second resource manager enlists, the transaction manager must prepare both, unless one votes read-only, and then commit.
A pool hands you a MySQL connection still inside an XA branch. What do you do?
Answer
Do not issue application SQL on it. End or recover that XID first. Otherwise the next request's writes join a global transaction the new request does not know about, and a later commit or rollback decides the wrong work.
Does Postgres ship a distributed transaction manager?
Answer
No. It ships the participant. Two PREPARE TRANSACTION calls on two servers still need an external coordinator that durable-logs gids and the decision. COMMIT PREPARED from a second session is enough power to commit heuristically, which is why that privilege should be tight.
What should a driver do with a heuristic error?
Answer
Surface it. The resource manager is telling you it already finished the branch without the transaction manager. Swallowing the error hides a split. Forget is how the transaction manager acknowledges the hazard after a human has a plan, not how you clear a log in silence.
When do you turn XA off?
Answer
When a participant cannot prepare, when both writes can live on one resource manager, or when the product accepts pending and undo. That last case is the existing saga series, including orchestration versus choreography and saga state machines. Keep XA when both sides are resource managers you operate and prepared-transaction age has an owner.
Write the two SQL sequences, Postgres and MySQL, through prepare. Then delete the transaction-manager process and list the only queries you are willing to run. Catalog reads are on the list. A one-sided commit is not.
Go deeper
- MySQL XA and XA statement syntax.
- Postgres: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, and pg_prepared_xacts.
- X/Open XA specification.
- CMU 15-445 on database 2PC in practice.