Distributed transactions: two-phase commit

Why was a customer charged for an order that does not exist?

In spring the payments moved into a separate service with its own database, which only two people could touch. That way the auditors would see one small system instead of all of the kitchen’s software.

Placing an order now has two steps. First the order service asks the payments service to capture the money, which commits in the payments database. Then it reserves the dish and inserts the order, which commits in the orders database.

On Thursday at 12:31 a small fix was deployed. The order servers restarted one by one, in the middle of the lunch rush.

At four o’clock two printouts lie side by side. The payments database has 23 captures between 12:31 and 12:33 that have no order. One customer paid for a festival thali the kitchen never heard of. Each restart landed between the two commits: the money was saved, the order was not. If the two steps were swapped, a restart in between would leave an order with no payment, and the kitchen would cook for people who never paid.

What the business needs is clear: both commits or neither, the way one database gives both rows or neither inside one transaction. Java has had transaction managers that promise exactly that for twenty years, under the name XA, and the obvious move is to wrap both databases in one such transaction.

Before accepting that, look at what such a transaction has to do. Each database knows only its own data. If the two must agree, something has to decide for both, and each database has to do something while it waits for that decision.

XA uses a protocol called two-phase commit. It does make two databases agree, and its cost appears while they wait for the decision. Guess what a database does with its locks while it waits, then check.

Pause and predict In two-phase commit, both databases vote yes, then the coordinator crashes before it sends “commit”. What do the databases do?

Two commits never happen as one step

Inside one database a transaction is atomic because one write-ahead log decides it: the commit record is either on disk or not (lesson 2.2). Two databases have two logs. Commit them one after the other and there is always a moment, however short, when one has committed and the other has not. A crash in that moment leaves the two out of step.

Distributed transaction

One transaction whose changes live in more than one database or service, with the promise that all of them commit or none do.

For example: Capture ₹180 in the payments database and insert the order in the orders database, as one unit.

The classic answer is two-phase commit (2PC). One process, the coordinator, runs the transaction across several participants:

1. Prepare. When the work is done, the coordinator asks every participant: can you commit? A participant that is able to commit writes a prepare record to its own log, forces it to disk, keeps its locks, and votes yes. A yes vote is a promise: “I will commit if you tell me to, even if I crash first.”
2. Decide. If every vote is yes, the coordinator forces COMMIT to its own log; if any vote is no or missing, ABORT. The decision exists from the moment it is on the coordinator’s disk.
3. Commit. The coordinator tells every participant the decision, and keeps telling them until each acknowledges. Each commits or rolls back and releases its locks.

PostgreSQL can be a participant: PREPARE TRANSACTION 'order-8812' stores the transaction on disk and detaches it from the session, and COMMIT PREPARED or ROLLBACK PREPARED finishes it later from any session. The feature is off by default (max_prepared_transactions is 0), and the documentation is blunt: unless you are writing a transaction manager, you probably should not use it. The first attempt here turns it on and writes a small coordinator into the order service.

For a week it works. The next Thursday a deploy restarts the order service again at 12:30, while three transactions are prepared: both databases have voted yes, and the coordinator has not yet decided.

Pause and predict The coordinator is down for ninety seconds. What does the orders database do with the thali’s stock row, which a prepared transaction has locked?

A mental model

In doubt (a prepared transaction)

A participant that has voted yes and not yet heard the decision. It may neither commit nor roll back on its own, and it keeps every lock the transaction took.

For example: The orders database holds the thali’s stock row while the coordinator restarts. In PostgreSQL the transaction shows up in pg_prepared_xacts and survives a restart of the database itself.

Three details make the protocol correct, and each costs something:

• Forced log writes. Each participant forces its prepare record, and the coordinator forces its decision, before saying anything, because a promise held only in memory would be forgotten in a crash. That is two extra rounds of fsync on the critical path.
• Presumed abort. A coordinator that restarts and finds no decision in its log for a transaction may safely answer “abort”: it never told anyone “commit”. This lets it forget aborted transactions quickly.
• No unilateral decision after yes. A participant may abort on its own at any time before it votes yes (a no vote, a crash, a timeout). After yes, it waits. That rule is what makes 2PC atomic, and it is also exactly what makes it block.

The standard most databases and transaction managers implement is X/Open XA (1991): the interface between a transaction manager (the coordinator) and resource managers (the databases and queues), with the same prepare and commit calls.

What one distributed commit costs

With the simulator’s illustrative timings (1 ms per message, 2 ms per forced log write, 5 ms of work):

Three costs show up. Every transaction holds its locks about twice as long, so a hot row (the thali’s stock) can serve about half as many transactions a second. Every participant must be up for anything to commit, so availabilities multiply: three parts at 99.9% each give 99.7%, which is 30 days x 1,440 minutes x 0.3% ≈ 129.5 minutes of failure a month instead of 43.2. And a coordinator failure turns into locks held for as long as it is gone.

Keep two-phase commit, or keep each transaction inside one database?

The 2PC setup above gives correct results, and it blocks whenever its coordinator restarts. The accounts need both commits or neither, the kitchen needs orders that never wait on a lock, and both goals are worth keeping.

The first path is right in one setting: inside one distributed database, where a single vendor runs 2PC over replicated groups and the operator never sees it. Across two services owned by one small team, deployed on their own schedules, the coordinator is ordinary application code, and ordinary application code restarts at lunch. The second path is better only if one condition holds: the order and the payment must reliably end up in step, every time.

That last condition is the hard part. “Commit, then send a message” is two writes again, and a restart between them loses the message. The next lesson, the outbox pattern, is how to make that one write.

What to remember

1. Two databases have two logs, so their two commits can never happen as one step. A crash between them leaves them out of step.
2. Two-phase commit: prepare (force a prepare record, vote), decide (force the decision to the coordinator’s log), commit. The result is all or none, as long as every participant waits for the decision.
3. After voting yes, a participant is in doubt: it may not decide alone, and it holds its locks. A coordinator crash after the votes blocks every participant until it returns.
4. 2PC roughly doubles how long locks are held, and because every participant must be up, the availability of the whole is the product of theirs. Deciding by hand unblocks it and can break atomicity.
5. Non-blocking commit needs a replicated coordinator (Paxos Commit, Spanner). Between your own services, keep each transaction in one database and connect them with messages.

Do not make your own application code the coordinator of a transaction that spans two databases.

In the real world

The simulator runs one transaction with fixed timings. Real transaction managers handle thousands at once, retry decisions until every participant acknowledges, keep a recovery log of transactions in flight, and give operators tools to list and resolve in-doubt work. PostgreSQL warns that a forgotten prepared transaction also stops VACUUM from reclaiming space, and in the extreme can force the database to shut down to avoid transaction id wraparound.

The kitchen now keeps every transaction inside one database. The order and the payment agree eventually, through a message. Making that message impossible to lose is the next lesson.