A $10 transfer changes one account by −$10 and another by +$10. Atomic commitment makes both changes succeed or fail together.
Here, “distributed” means the accounts live on separate database shards. A transaction local to one shard cannot protect changes on both. MySQL XA transactions use two-phase commit (2PC) to coordinate multiple transactional resources.
Two-Phase Commit
Two-Phase Commit (2PC) uses a coordinator to agree on one outcome across participating shards. In MySQL XA, a client transaction manager fills that role. At a high level, it follows this process:
- Prepare: The coordinator asks every shard to PREPARE and waits for each vote (yes or no).
- Commit or abort: If all vote yes, the coordinator records and sends COMMIT; otherwise, it records and sends ABORT. Each shard applies the decision, records the outcome, and acknowledges it.
After every shard votes yes, an unavailable coordinator can leave prepared shards blocked. They cannot safely choose commit or abort from a timeout alone. If the coordinator stays unavailable, the wait has no bound. When it recovers, it checks its durable transaction log and resends any recorded decision; the shards keep their locks until they receive and apply it.
In this account-row example, a prepared transaction holds locks on the rows it touched. Work on unrelated rows can continue, while writes that need those locked rows must wait for the decision.
Scroll horizontally to read the full diagram.
Two-Phase Commit
Prepare both participants, then deliver one durable commit decision.
Drag to orbit · Scroll to zoom · Focus the view for arrow keys, +/− and Home
1 / 7 Two participants
A transaction manager acts as coordinator for Shards A and B, collecting their votes before choosing one outcome for the transfer.
Three-Phase Commit
Three-Phase Commit (3PC) adds a pre-commit round before the final decision to reduce blocking when the coordinator fails.
With bounded message and processing delays and no network partition, 3PC can avoid the coordinator-failure blocking problem of 2PC. Outside those assumptions, a timeout cannot reliably distinguish a crashed participant from an unreachable one.
During a network partition, a participant that received PRE-COMMIT may time out and commit while participants that did not receive it abort, violating atomicity. The extra phase allows progress only while the timing and connectivity assumptions hold.
Three-phase commit adds a Pre-commit round before the final decision.
Scroll horizontally to read the full diagram.
Three-Phase Commit
Connected participants recover after both acknowledge the additional phase.
Drag to orbit · Scroll to zoom · Focus the view for arrow keys, +/− and Home
1 / 12 Two participants
A transaction manager acts as coordinator for Shards A and B, collecting their votes before choosing one outcome for the transfer.
Transactions Across Replicated Shards
Sharding spreads data across groups. Replication stores multiple copies of each group's data. A transaction that spans replicated shards needs one consistent outcome across the groups.
Production distributed databases combine these effects in different ways:
- Google Spanner replicates each split with Paxos and uses two-phase commit for transactions that span splits. TrueTime supports its commit-wait step.
- In TiDB and TiKV, each TiKV Region is replicated with Raft. In the standard 2PC path, TiDB's TiKV client prewrites mutations and then commits them; TiKV's storage code writes the prepared lock and records the commit.
- CockroachDB replicates key ranges with Raft and uses its Parallel Commits protocol, an atomic commit optimization for transactions across ranges.
The demo transfers $10 from Shard A to Shard B. Each shard has three replicas, and two replicas form a quorum: enough copies to safely record a decision. A's leader also coordinates the transfer.
- Lock both accounts. A and B hold their account locks while the transfer is pending.
- B prepares and tells A it is ready. B stores PREPARE on two replicas before sending its Yes vote to A.
- A records the decision. A stores COMMIT on two replicas, together with its debit and the commit timestamp. The decision is now fixed.
- Both shards apply the transfer. After a short clock-related wait, A applies the debit and sends COMMIT to B. B records it on two replicas, applies the credit, and releases its lock. The balances finish at $90 and $110.
This is a simplified version of Spanner’s protocol (§4.2.1): A skips its own PREPARE record, and the clock-related wait is specific to Spanner. Replicas with blank records can catch up later. The displayed balances show internal progress; a consistent read cannot return the temporary $90/$100 pair.
Transactions Across Replicated Shards
Prepare within B's replica group, decide within A's group, then apply.
Drag to orbit · Scroll to zoom · Focus the view for arrow keys, +/− and Home
1 / 10 Replicate each participant
Each shard has three voting replicas. A's leader also coordinates this two-shard transaction.