Making several databases commit or roll back together. It works, and the reason almost nobody uses it is worth understanding precisely.
Prerequisites: Transactions, Failure Modes Time to read: ~20 minutes
Transfer $100 from an account in database A to an account in database B.
DB A: UPDATE accounts SET balance = balance - 100 WHERE id = 'ayesha'; COMMIT;
DB B: UPDATE accounts SET balance = balance + 100 WHERE id = 'bilal'; COMMIT;
🚨 If the process dies between the two commits, $100 has vanished. There’s no shared transaction — each database committed independently and neither knows about the other.
This is the same shape as the dual-write problem and it appears everywhere once you have more than one datastore: two shards, two microservices, a database and a message broker, a database and a payment provider.
The classic solution. A coordinator drives all participants to a unanimous decision.
sequenceDiagram
participant C as Coordinator
participant A as DB A
participant B as DB B
Note over C,B: PHASE 1 — PREPARE
C->>A: prepare?
C->>B: prepare?
Note over A: do the work, write to the log,<br/>acquire locks, but DON'T commit
A-->>C: yes, ready
B-->>C: yes, ready
Note over C,B: PHASE 2 — COMMIT
Note over C: all said yes → decision: COMMIT<br/>(written durably to the coordinator's log)
C->>A: commit
C->>B: commit
A-->>C: done
B-->>C: done
The key property of the prepare phase: a participant that answers “yes” is making a binding promise that it can commit — it has done the work, written it durably, and acquired the locks. It has given up the right to abort unilaterally. It must now wait for the coordinator’s decision, and it must be able to honour either outcome even if it crashes and restarts.
If anyone says no, or times out, the coordinator sends abort to everyone.
📐 That’s the whole protocol. It’s correct — it genuinely gives you atomicity across independent systems.
1. Coordinator sends "prepare" to A and B.
2. Both reply "yes." Both are now holding locks, waiting.
3. The COORDINATOR CRASHES before sending the decision.
4. A and B wait. They cannot commit (maybe the other said no).
They cannot abort (maybe the other said yes and the decision was commit).
THEY HOLD LOCKS INDEFINITELY.
In-doubt participants are stuck, and they can’t resolve it by talking to each other — neither knows what the coordinator decided. The locked rows are unavailable to everyone until the coordinator recovers and reads its log.
🚨 This is why 2PC is called a blocking protocol, and it’s the single most important thing to know about it. A coordinator failure doesn’t just fail the transaction — it can freeze parts of your database until a human intervenes.
Its log is the source of truth for the decision. Lose it and you have permanently in-doubt transactions requiring manual reconciliation. Making the coordinator highly available means replicating it with consensus — at which point you’ve built a substantially complex system.
📐 Two round trips to every participant, plus durable log writes at each step:
Single-database commit: ~1 ms
2PC across 3 participants: ~10–50 ms (same datacenter)
2PC across regions: ~300–600 ms
And locks are held for the whole duration, so the contended throughput of any hot row collapses.
Every participant must be available for the transaction to commit. Five participants at 99.9% each gives 99.5% for the transaction. → Availability arithmetic
You made the system less available by making it more consistent — exactly the CAP trade, made concrete.
XA (the standard distributed transaction API) is supported unevenly, performs poorly, and is notoriously awkward to operate. Most modern databases and virtually no message brokers or HTTP APIs support it properly.
3PC adds a pre-commit phase between prepare and commit, so participants learn the intended decision before it’s final. If the coordinator dies after pre-commit, participants know it was going to commit and can proceed themselves.
⚖️ It’s non-blocking under crash failures — but only if the network is synchronous.
🚨 And it’s unsafe under network partitions, which is the failure mode you actually care about. Two partitions can reach different conclusions: one side sees “pre-commit was sent, proceed,” the other sees “no pre-commit, abort” — and you get an inconsistent outcome, which is worse than blocking. It also adds a third round trip.
Result: 3PC is essentially not used in practice. It’s worth knowing the name and the one-line reason — “it trades blocking for the risk of inconsistency under partition, which is a worse trade” — and nothing more.
🚨 Design so that atomic operations stay within one transactional boundary.
🎙️ “If ordering and inventory need to commit atomically on every request, that’s a strong hint they belong in the same service and the same database. Needing a distributed transaction is usually a signal that the boundary is in the wrong place.”
That’s the single most valuable thing to say about this topic in an interview.
A sequence of local transactions, each with a compensating action to undo it.
1. Reserve inventory → compensate: release inventory
2. Charge the card → compensate: refund
3. Create the shipment → compensate: cancel shipment
If step 3 fails, run the compensations for steps 2 and 1 in reverse.
✅ No locks held across services. No blocking. Each step is a normal local transaction. ❌ No isolation — intermediate states are visible to other transactions. And compensations are business logic you must write and test.
This is the dominant pattern in microservice architectures. → Saga Pattern — the next chapter
For the specific case of “update the database and publish an event,” this is exact and simple: write the event to an outbox table in the same local transaction, and have a relay publish it. → CDC
Perform operations independently, make them idempotent so retries are safe, and run a periodic reconciliation job to detect and fix discrepancies.
🚨 This is how real payment systems work. Banks, card networks, and payment processors don’t run distributed transactions across organizational boundaries — they exchange messages, retry on ambiguity, and reconcile daily. “Eventually consistent with automated reconciliation” is the industry’s actual answer for money, which surprises people. → Idempotency
CockroachDB, Spanner, TiDB, and YugabyteDB do provide distributed ACID transactions across shards — implemented with consensus (Raft/Paxos) per shard plus a distributed commit protocol, rather than classic 2PC with a fragile coordinator.
⚖️ They pay for it in latency (a cross-region transaction costs cross-region round trips) and cost, but the coordinator is consensus-replicated so the blocking problem is largely solved.
🎙️ “If we genuinely need cross-shard ACID, I’d use a database that provides it natively rather than implementing 2PC over independent databases — the coordinator being consensus-backed is what makes it survivable.”
It’s not universally wrong:
🚨 Never across organizational or network boundaries — a third-party API cannot participate in your transaction, and you’d be handing a stranger the ability to freeze your database.
| Approach | Gain | Cost |
|---|---|---|
| 2PC | True atomicity across systems | Blocking on coordinator failure; SPOF; latency; availability multiplies down |
| 3PC | Non-blocking under crashes | Unsafe under partition; extra round trip; unused in practice |
| Avoid (colocate data) | No distributed transaction at all | Constrains sharding and service boundaries |
| Sagas | No locks, no blocking, scales | No isolation; compensation logic to write and test |
| Outbox | Exact for the DB+event case | Only solves that specific case |
| Idempotency + reconciliation | Robust, simple, no coordination | Temporary inconsistency; reconciliation to build |
| NewSQL | Native distributed ACID | Latency, cost, ecosystem maturity |
1. Break a naive dual write. Two Postgres instances and a script updating both. Add a
sys.exit() between the commits. Run it. Look at both databases — money has vanished. This makes
the problem visceral in about five minutes, and it’s the motivation for everything else.
2. See 2PC block. Postgres supports prepared transactions (max_prepared_transactions > 0):
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'txn1'; -- prepared, but not committed
-- Now, from another session:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- BLOCKS. Indefinitely.
SELECT * FROM pg_prepared_xacts; -- there it is, in doubt
COMMIT PREPARED 'txn1'; -- or ROLLBACK PREPARED — releases it
Leave the prepared transaction open and watch the row stay locked. That’s exactly what happens when a coordinator dies, and experiencing the block is far more convincing than reading about it.
3. Implement a saga. Three services with local transactions and compensating actions. Make step 3 fail and watch the compensations run. Then make a compensation fail and work out what you’d do — that’s the hard part nobody mentions.
4. Measure the latency cost. Time a local commit versus a 2PC commit across two databases. Then add 100 ms of artificial network latency between them and measure again.