Distributed transactions in depth: two-phase commit, sagas, outbox

The interviewer says: "A transfer takes money from an account in one database and adds it to an account in another. It must not lose money or create money. What if a server dies in the middle? Why not one big transaction?"

A weak answer says "use a distributed transaction" or "use a saga" and stops. A strong answer says what each tool holds while it waits, what a dead server leaves behind, and how an undo works. It also says when no undo exists, and picks by the data and the lock time.

This is an SDE-3 question. It builds on lessons 92 and 95 and goes further.

A wedding planner phones three vendors with one question: "Can you come on Sunday?" Each says "Yes, I keep Sunday free for you." So no vendor takes other work for Sunday. Then the planner calls again: "Confirmed." If the planner disappears after the yes answers, the vendors wait. They cannot take other work, and they cannot be sure whether to come.

Two-phase commit, step by step

Two-phase commit is this call, between a coordinator and the participants, the databases that take part (lesson 95).

1. The coordinator asks every participant to prepare.
2. Each participant does the work and locks its rows. It writes a prepare record to its log, which is a promise to commit if told. Then it votes yes or no.
3. If all vote yes, the coordinator writes "commit" in its own log. This is the decision. Any no, or a timeout, gives "abort".
4. The coordinator tells everyone. Each participant finishes and releases its locks.

Sequence of two-phase commit. The coordinator asks the participant to prepare. The participant locks its rows, saves a promise and votes yes. The coordinator saves the decision, commit, and tells the participant, which then releases its locks. Between its yes vote and the commit the participant is in doubt: it must wait, holding its locks. Every participant does the same.

Look at each failure point.

An in-doubt participant cannot commit alone, because the decision may be abort. It cannot abort alone, because the coordinator may have decided commit and told others. It can ask the other participants. If one of them knows the decision, or never voted yes, the question is settled. If all of them are in doubt, all wait and hold their locks. Database manuals warn about this. A prepared transaction keeps its locks (PostgreSQL). In-doubt data may be locked for reads and writes until it is resolved (Oracle).

Repair: groups of copies

The weak point is one machine. So make the coordinator and each participant a group of copies that agree by consensus (lesson 80). Each prepare and commit record is saved by a majority. If the coordinator's leader dies, another copy has the log and finishes the decision. A dead machine no longer blocks everyone. This works only when every participant is a database you run with this support. A payment gateway or a bank cannot prepare.

Sagas in depth

Lesson 95 gave the saga: steps with compensations. Four more points.

• Order the steps. First come steps that can be undone (compensable). Then one pivot, the point of no return. After it, every step is retryable: it is idempotent and is retried until it works. After the pivot you never undo, you go forward.
• A compensation can fail too. Retry it until it works, with the same key. Raise an alarm after a limit. The saga stays in a "compensating" state.
• Who runs it. With orchestration, one controller holds the plan. It keeps the saga state in a row that says which step the saga is on. A controller that crashes reads the row and continues. With choreography, services react to events, so no row shows the whole saga. A stuck compensation is then hard to see, and orchestration is easier to watch for long sagas.
• Guard the middle. Other users see half-done work. Show a pending state. Use a semantic lock, a flag that says "a saga is working on this". Use updates that can be applied in any order, such as "subtract 500 if the balance is at least 500".

Outbox with an idempotent consumer

Delivery may repeat, but the effect must happen once. Each step does one local transaction: change the data and write an outbox row (lesson 92). A relay sends the row. It can poll the table, or read the database log with change data capture (lesson 92). The next service saves the message ID with a unique constraint in the same transaction as its effect (lesson 89). A repeat finds the ID and does nothing. This works when the effect is in the same database as the ID table. For an outside effect, such as an email, send the ID as the provider's key if it accepts one.

Choosing

PayEasy moves Rs 500 from Asha (database 1) to Ravi (database 2).

Assumptions (made up to teach with): 2,000 transfers per second at peak. A message trip takes 0.5 ms. A write saved by a majority takes 2 ms. A merchant account gets 100 transfers per second. A single database holds its locks for about 1 ms. 10,000,000 transfers a day. 0.5% fail at the credit step.

Step 1: two-phase commit over copies. Timeline in ms: 0 prepare is sent. At 0.5 the locks are taken and the prepare record is saved by a majority, done at 2.5. The vote arrives at 3.0. The decision is saved, done at 5.0. Asha hears "done" at 5.5. The commit reaches the participants at 5.5 and is saved at 7.5. Locks are held from 0.5 to 7.5: 7.0 ms. A single database holds them about 1 ms.

Step 2: a hot row. One row can take 1,000 / 7.0 = 143 transfers per second. The merchant needs 100, so the row is 70% busy. A local saga step holds the row 2 ms: 1,000 / 2 = 500 per second, and the row is 20% busy.

Step 3: a dead coordinator. A participant is in doubt from its vote (2.5) until the decision reaches it (5.5): 3 ms. In doubt at any time: 2,000 x 0.003 = 6 transfers, 12 rows locked. This answers the predict box: neither. With one coordinator machine down for 30 seconds, a locked merchant row blocks 100 x 30 = 3,000 transfers. With a group, a new leader takes about 1 second (assumption; lesson 80 has timeouts of 150 to 300 ms): 100 transfers wait.

Step 4: the saga. Steps: debit Asha (compensable), credit Ravi (the pivot), write the statement entry (retryable). The credit fails for 0.5% of 10,000,000 = 50,000 transfers a day. Each runs the compensation, which credits Asha back. Assumption: a compensation call fails 2% of the time. Expected calls: 50,000 / (1 - 0.02) = 51,020, so 1,020 extra. Five failures in a row for one compensation: 0.02 to the power 5 = 3.2 in 1,000,000,000.

Step 5: the middle. The credit comes 200 ms after the debit (assumption). Money in flight: 2,000 x 0.2 = 400 transfers x Rs 500 = Rs 200,000 (2 lakh). Show state DEBITED, and count it as "in transit" in reports. Asha has Rs 600 and starts two transfers of Rs 500. If both read 600, the balance goes to -400. With "debit 500 only if the balance is at least 500", the second fails.

Step 6: repeats. Assumption: the relay crashes 3 times a day (lesson 92), and each crash sends one transfer twice. Without a saved transfer ID: 3 x Rs 500 = Rs 1,500 of new money each day. With the ID: Rs 0.

Step 7: choose. Between two of our own databases, two-phase commit works if the lock time is acceptable (7.0 ms). A saga works if a pending state is acceptable. To an outside bank: a saga, because the bank cannot prepare. Order placed, then an email: an outbox.

What companies wrote

• Google (the Spanner database paper, OSDI 2012): each participant is a group of copies that agree by Paxos, a consensus method like Raft. The group leaders run two-phase commit, and the paper says this eases the availability problem. It reports that 50 participants is reasonable and that latency rises noticeably at 100 (the paper's figures). Link: https://research.google.com/archive/spanner-osdi2012.pdf (opens in a new tab)
• Netflix wrote in October 2026 (post listed 14 Oct 2026) that its workflow engine keeps the state of each workflow in a durable store. So long work survives failures. An older engine design changed state in several places, and competing updates could leave a workflow inconsistent. Link: https://netflixtechblog.com/netflix-conductor-the-next-chapter-41ad21067649 (opens in a new tab)

Two-phase commit over copies solves all-or-nothing across databases that you run, without a single machine that can block everyone. It costs lock time (7.0 ms against 1 ms) and a majority wait at every step. A saga solves outside providers and long steps. It costs compensations and a visible middle (Rs 200,000 in flight). I would change to a saga when a participant cannot prepare, or when a hot row cannot give 7 ms of lock time.

"A participant that voted yes can time out and abort by itself." The coordinator may have decided commit and told others. In step 3, neither choice is safe alone.

"Putting the coordinator in a group removes the cost of two-phase commit." It removes the blocking by one dead machine. Locks, majority waits and the participant count stay.

"A saga that fails just runs the undo steps, and they always work." A compensation can fail. In step 4, 1,020 calls a day were retries. After the pivot there is no undo at all.

1. What does "in doubt" mean in two-phase commit, and why can the participant not decide alone? What is the pivot in a saga?
2. Two-phase commit holds a row for 8 ms, and a local saga step holds it for 2 ms. A merchant row gets 100 transfers per second. (a) What is the highest rate for each? (b) There are 4,000,000 transfers a day and 0.5% need a compensation, which fails 5% of the time and is retried until it works. How many calls a day?
3. A team wants one two-phase commit across its own database and a card provider's API. What do you say?