All or nothing, across two databases
Inside one database, a transaction makes several changes happen together or not at all. Take 100 from one account and add it to another: if anything fails in between, both changes are undone.
Now put the two accounts in different databases, at different banks. Each database can guarantee its own change. Nothing guarantees them together.
Example
What can go wrong
Bank A takes 100 out. The network fails before Bank B is told to add it. The money has vanished.
Or do it the other way round: Bank B adds 100 first, then Bank A discovers the account is empty. Money has been created.
Whatever order you pick, a failure in the middle leaves the two databases disagreeing.
What is needed is a way for separate systems to reach one shared decision: everybody commits, or nobody does. The classic method is two-phase commit, often written 2PC.
The idea: promise first, act second
A wedding ceremony works the same way. The officiant asks each person in turn, "do you?". Only when both have said yes are they declared married. Nobody is half married.
One extra participant, the coordinator, plays the officiant.
100 must move from an account at Bank A to an account at Bank B. Each bank has its own database.
Phase 1: prepare
The coordinator asks every participant: can you commit this?
Each participant does all the work short of making it final. It checks the change is valid, locks the rows involved, and writes the pending change to disk. Then it votes yes or no.
A yes vote is a serious promise. It means: I can definitely commit this if you tell me to, even if I crash and restart in the meantime.
Phase 2: commit or abort
- If every vote is yes, the coordinator decides commit and tells everyone.
- If any vote is no, or someone fails to answer, the coordinator decides abort and tells everyone.
100 must move from an account at Bank A to an account at Bank B. Each bank has its own database.
One no is enough to cancel the whole thing. Bank A had done its preparation and simply undoes it. Neither database changed.
The weakness: it can get stuck
There is one dangerous window: after the participants have voted yes, and before they hear the decision.
100 must move from an account at Bank A to an account at Bank B. Each bank has its own database.
Bank A voted yes. It is now bound by its promise.
- It may not abort on its own: the coordinator might have decided commit and already told Bank B.
- It may not commit on its own: the coordinator might have decided abort.
All it can do is wait, holding its locks. The accounts involved are frozen for everyone else until the coordinator returns and reads its decision back from its own log. Two-phase commit is called a blocking protocol for this reason.
100 must move from an account at Bank A to an account at Bank B. Each bank has its own database.
Good at
- Gives a true all-or-nothing change across separate databases
- Simple to understand: collect promises, then decide
Where it bites
- If the coordinator fails at the wrong moment, participants are stuck holding locks
- Slow: several round trips and disk writes for every transaction
- Locks are held for the whole exchange, which blocks other work
- Every participant has to be up for anything to commit
What people use instead
Because of those costs, many systems avoid a single transaction across services and accept being briefly inconsistent.
Sagas. Break the work into steps, each a normal local transaction. If a later step fails, run compensating steps that undo the earlier ones: refund the payment, release the seat.
Example
Booking a trip as a saga
- Reserve the flight. 2. Reserve the hotel. 3. Charge the card.
The card is declined at step 3. The saga then cancels the hotel and cancels the flight.
For a moment the customer had a flight and no payment. That is the price: the middle states are visible, and every step needs an undo.
Reliable messaging. Make the change locally and, in the same local transaction, record a message saying what must happen elsewhere. Deliver that message until it is acknowledged, and make the receiving side safe to retry. See Idempotency and Safe Retries.
Two-phase commit is still used where strict all-or-nothing really is required, typically between databases run by one organisation. Modern distributed databases use it internally, with the coordinator itself replicated so that losing one machine does not freeze everything.
Key takeaways
- Two-phase commit makes a change happen in several databases together, or in none.
- Phase 1: the coordinator asks everyone to prepare, and each votes yes or no.
- Phase 2: if all voted yes, everyone is told to commit. Otherwise everyone aborts.
- A yes vote is a binding promise, so a participant that has voted cannot decide alone.
- If the coordinator fails after the votes, participants are blocked, holding locks, until it returns.