Notestransactions🌱 seedling
XA Transactions
Also: XA · X/Open XA · JTA · Heterogeneous Distributed Transaction · PREPARE TRANSACTION
A 1991 X/Open standard for running [[Two-Phase Commit]] across different systems (two databases, or a database and a message broker), driven by an external transaction manager. It inherits 2PC's blocking and adds operational fragility.
How it works
- A transaction manager (often a library in the application server, e.g. behind Java's JTA) coordinates; each resource manager (database, broker) implements prepare/commit/rollback.
- Postgres:
PREPARE TRANSACTION,COMMIT PREPARED,ROLLBACK PREPARED; in-doubt ones show inpg_prepared_xacts. - MySQL:
XA START … XA END … XA PREPARE … XA COMMIT; in-doubt ones show inXA RECOVER.
Trade-offs
| Gains | Costs |
|---|---|
| atomicity across heterogeneous systems | coordinator log on an app server: lose it and participants stay in doubt |
| standard API across vendors | lowest common denominator: no cross-system deadlock detection |
| prepared transactions hold locks in other teams' databases |
In practice
- Postgres ships with
max_prepared_transactions = 0(disabled); its docs warn that long-prepared transactions keep their locks and blockVACUUM, in extreme cases risking transaction-ID wraparound. - MySQL supports XA only in InnoDB and warns against combining it with replication filters or statement-based binlogs.
Pitfalls
- Running a transaction manager without monitoring for old prepared transactions.
- Heuristic decisions (an operator forcing commit or abort) that disagree across participants.
Connections
- Contrasts with
- Transactional Outbox
- In real systems
- PostgreSQLMySQL
Recall
These cards are also in your review deck, scheduled with spaced repetition.