CS Fundamentals Trainer · Guides
Distributed transactions: two-phase commit and sagas
Why one business operation across several databases is hard, two-phase commit (and how it blocks), sagas with compensations and their missing isolation, and idempotent steps with an outbox, all run on PostgreSQL and small Python services.
22 min read
Placing an order looks like one action, but it touches several systems: the order service records the order, the payment service takes the money, and the inventory service reserves the goods. Each system has its own database, and inside one database a transaction gives you all-or-nothing behaviour for free. Across databases nobody gives you that, and the failures are the interesting part: the payment succeeds and the stock reservation fails, or the server dies between the two. This guide covers what to do instead of hoping: first, avoiding the problem; then two-phase commit (and why it blocks); then sagas, which trade isolation for availability; and the details that make a saga survive crashes. I ran the database behaviour on PostgreSQL 16 and the saga on small Python services, and the code is in the appendix. Each result is one run on a small machine, so the behaviours matter and the times do not.
1. The problem: one business operation, several owners
A transaction in a single database has four guarantees (ACID): atomicity (all of it or none of it), consistency (constraints hold), isolation (concurrent transactions do not see each other's half-finished work) and durability (committed means saved). See transaction isolation levels for the third one.
The moment two separate databases, or a database and an external API such as a payment provider, must change together, there is no single place that can make the whole thing atomic. Plain code looks fine and fails in the gaps:
charge the card // succeeds
reserve the stock // fails: out of stock
Now the customer has paid for nothing. Swap the two lines and a crash after the reservation leaves goods held for an order that was never paid. This is the dual-write problem, and the options below differ in what they give up.
2. Option zero: do not distribute the transaction
The cheapest distributed transaction is the one you do not have. Before reaching for a protocol, ask whether one database can own the invariant.
- Put the data that must change together in one database. If "an order and its stock reservation" must be atomic, they probably belong to one service and one schema. Splitting services along the wrong line is the usual reason for needing this guide.
- Keep the invariant on one side. Reserve the stock first (a local transaction), then ask for payment, and treat "payment failed" as a business event that releases the reservation, instead of trying to commit both at once.
- Accept eventual consistency where the business does. Sending a receipt, updating a search index or a dashboard does not need to commit with the order. Write an event into the same database in the same transaction (the outbox, see message queues and delivery guarantees) and let it propagate.
Only when the invariant truly spans owners do the next two tools apply.
3. Two-phase commit: all or nothing, with a price
Two-phase commit (2PC) is the classic protocol for an atomic commit across several participants (databases or resource managers), run by a coordinator (a transaction manager).
Diagram: Two-phase commit
In phase 1 each participant does the work, makes it durable, and votes. A "yes" is a promise: from now on the participant can no longer decide alone, it must be able to commit even if it crashes and restarts, and it keeps its locks until it hears the decision. In phase 2 the coordinator, after recording the decision durably, tells everyone to commit (if all voted yes) or abort (if any voted no).
PostgreSQL exposes the participant side as PREPARE TRANSACTION, COMMIT PREPARED and ROLLBACK PREPARED (it must be enabled with max_prepared_transactions, which is 0 by default). It is meant for transaction managers, not for application code. I ran a transfer across two databases in one PostgreSQL server (a bank and a stock database) acting as the coordinator myself:
| Scenario | What happened |
|---|---|
| Both prepare, then both commit | after the prepare the changes were not visible (bank 100, stock 1); after COMMIT PREPARED both were (bank 70, stock 0) |
| One participant cannot do its part | the stock update violated its CHECK (qty >= 0) and failed; the coordinator ran ROLLBACK PREPARED on the bank, which stayed at 70: the all-or-nothing outcome |
| The coordinator dies after both prepared | both transactions stayed in pg_prepared_xacts; another customer's UPDATE of the same account waited and gave up at a 2-second lock_timeout; a plain SELECT still returned the old value, because readers are not blocked |
| The database restarts while prepared | both prepared transactions were still there after the restart; a recovering coordinator finished them with COMMIT PREPARED (bank 40, stock 4) |
The third row is the weakness of 2PC: it is a blocking protocol. A participant that voted yes cannot unilaterally commit or abort, because it does not know what the others decided, so it holds its locks until the coordinator returns. Every writer that touches those rows queues behind it. Prepared transactions also keep old row versions alive, which stops cleanup (vacuum) from reclaiming space, so a forgotten one slowly harms the whole database. Alternatives that avoid the single point of failure exist (consensus-based commit, three-phase commit), but they are rare in application stacks.
Cost. Each participant writes to disk twice (at prepare and at commit) instead of once, and the coordinator adds its own log and message round trips. In a loop of 1,000 single-row commits on one connection, a plain commit took 0.27 to 0.32 s, and PREPARE TRANSACTION plus COMMIT PREPARED took 0.46 to 0.51 s, about 1.6 times as long per participant. That container's disk syncs quickly and there is no network, so on real hardware with real latency the gap is larger, and the longer locks are held the more other transactions wait.
When 2PC is a poor fit. It needs every participant to support it, and many do not: most message brokers, a payment provider's HTTP API, a third-party service and many managed databases cannot take part. It couples the availability of all participants (one slow participant slows everyone), and it is a bad match for long-running work. It fits best inside one organisation, with a few participants that all support XA (a standard interface for it), short transactions and a reliable coordinator. Sagas are the usual answer outside that case.
4. Sagas: a sequence of local transactions with undo steps
A saga (Garcia-Molina and Salem, 1987) replaces one big distributed transaction with a sequence of local transactions, each committed on its own. If a step fails, the saga does not roll back (the earlier steps are already committed and visible); it runs compensating transactions that undo the business effect of the steps already done, in reverse order.
Diagram: A saga with a compensation
There are two ways to coordinate the steps:
| Orchestration | Choreography | |
|---|---|---|
| How | a central orchestrator (a state machine) tells each service what to do next | each service reacts to events published by the others |
| Where the flow lives | in one place, easy to read, test and monitor | spread across services, implicit |
| Coupling | services depend on the orchestrator's commands | services depend on each other's events, and the cycle risk grows with each new event |
| Fits | flows with many steps or branches | two or three steps with simple rules |
Prefer orchestration once a flow has more than a few steps. In choreography nobody can answer "where is order 42 right now?" without joining logs from every service.
Compensation is not rollback
A rollback makes the change never have happened. A compensation is a new business action that offsets an earlier one, and the earlier effect was visible in the meantime. Consequences:
- Some things cannot be undone: an email already sent, a parcel already shipped, money already paid out. A compensation may be an apology, a return label or a credit instead. Put such steps last, after the steps that can still fail.
- Compensations must be repeatable (idempotent) and must eventually succeed, because there is nothing to fall back on if the undo fails. In the test the refund failed twice (the payment service was unavailable) and succeeded on the third attempt; the saga simply kept retrying it.
- Steps fall into three kinds. Compensatable steps come first and can be undone. The pivot is the step after which the saga will complete: once it commits, no compensation is run. Retriable steps come after it and must keep being retried until they succeed. Design the order of steps around this: do the risky, undoable things before the pivot.
5. What a saga gives up: isolation
Every step commits immediately, so other transactions can see the intermediate states: a saga gives up isolation, and what it offers in place of atomicity is weaker than a transaction's: either every step completes, or compensations run that are meant to bring the business back to a valid state. Whether they do depends on the invariants being designed in (what is a valid state, what each compensation restores) and on every compensation and recovery path actually succeeding. No saga framework guarantees eventual consistency by itself. The missing isolation is visible in the test. A reader sampled the account balance while a saga ran that charged 30 and then failed on the stock step; it saw 10 (after the charge) and then 40 again (after the refund) over about a tenth of a second. For a moment the customer had less money than at the start or at the end. Other anomalies follow: two sagas updating the same record can overwrite each other (a lost update), and a saga can read data that another saga is about to compensate.
The standard countermeasures, from cheapest to heaviest:
- Semantic lock: a status that marks a record as being in the middle of a saga (
PENDING), which other operations check and refuse to act on (or wait for). The order stayedPENDINGuntil the last step in the test, so a reader could tell it was not final. - Commutative updates: design operations so that order does not matter (
balance + 30andbalance - 30can be applied in any order), which makes a compensation safe even after other changes. - Reservations (try, confirm, cancel): the first step does not take the resource, it reserves it (hold 30 on the card, hold one book) and a later step confirms or releases the hold. Others see the resource as unavailable and not as taken then given back.
- Re-read before you write: check that a value is unchanged before overwriting it, with a version number (the same idea as
If-Matchin designing a REST API).
Which anomalies matter depends on the business. A booking system must not sell the same seat twice (use a reservation); a dashboard that briefly shows a lower balance may not matter.
6. Making every step survive crashes
The orchestrator is a program, and programs crash. Three habits turn the sketch above into something you can run.
- Persist the saga's progress. Store the saga id, the current step and its state (
RUNNING,COMPENSATING,DONE,COMPENSATED) in a database, updated after each step. After a restart the orchestrator reads it and continues. An in-memory state machine is lost with the process. - Make each step idempotent, keyed by the saga id. The worst crash is after the step happened but before it was recorded. In the test the orchestrator died right after charging the card and before writing "charge done". The log still said step 1, so on restart it ran the charge again; the payment service had recorded
charge:<saga id>in the same transaction as the balance change, recognised the repeat and did nothing. The balance was 40 after the crash and 40 after the resume, not 10. For an external provider, pass a derived idempotency key, as in designing a REST API. - Send commands reliably. If the orchestrator updates its saga log and then publishes a message to a service, those are two writes again. Write the command to an outbox in the same transaction as the saga state and let a relay deliver it at least once (message queues and delivery guarantees); receivers must tolerate duplicates.
The limits of idempotency keys
An idempotency key makes a repeat harmless only within what the receiver promises. For an outside service such as a payment provider, check three things:
- Scope. A key is usually tied to one account and one endpoint, and many providers reject the same key with different parameters. Derive keys from the saga id and the step (
order-<id>-charge), never reuse them between operations. - Retention. Providers remember keys for a limited time (often about a day, see their documentation). A retry after the key has expired is a new request and can charge twice. A saga that can wait longer than the retention period (a retry queue after an outage, a manual retry) must not rely on the key alone.
- Ambiguous outcomes. A timeout does not say whether the charge happened. The honest state is unknown, not failed. Record it as its own state (
CHARGE_UNKNOWN), then resolve it by asking the provider (look up the payment by your own reference, or wait for its webhook) before compensating or retrying. Run a reconciliation job that compares your records with the provider's reports, and treat any difference (a charge you have no order for, an order with no charge) as a case for repair. Idempotency prevents most duplicates; reconciliation finds the rest.
The demonstration is not a production orchestrator
The Python sample in the appendix exists to show the ideas (persisted progress, idempotent steps, compensation, retry). A production workflow needs more:
- Concurrency control. Two workers must never run the same saga at once: a lease or a version column checked on every update, so that a stalled worker that wakes up cannot overwrite the work of the one that took over.
- Durable retries and timers. The sample retries in a loop in memory. A real one stores the next attempt time, backs off with jitter, survives restarts, and enforces the timeout on every step.
- Observability. A saga id in every log line and trace span, metrics for sagas by state and age, and an alert for sagas that stop making progress (see observability).
- Versioned workflow state. Sagas can run for hours or days, so the code that resumes them may be newer than the code that started them. The stored state and the step definitions need versions, and a deploy must not strand sagas that are halfway.
- Permanently failed compensations. Decide in advance what happens when an undo can never succeed: a dead-letter state, an alert, a manual repair tool, and an audit trail.
This is the main reason to use a workflow engine rather than writing the loop yourself.
Other things a production saga needs: a timeout on each step (a service that never answers must lead to compensation, not an eternal wait), a way to see and fix stuck sagas (a dashboard, an alert, a manual retry), and a decision about what a failed compensation means (usually a human handles it). Workflow engines such as Temporal, AWS Step Functions or Camunda keep the state, retries and timers for you, which is why many teams use one instead of building this loop themselves.
7. Choosing
| Situation | Reach for |
|---|---|
| The data can live in one database | a normal local transaction (option zero) |
| A few resource managers, in your control, all supporting XA, short operations | two-phase commit |
| Several services, external APIs, long-running steps, availability matters | a saga (orchestrated, with idempotent steps) |
| A side effect that only needs to happen eventually (receipt, index update) | an outbox and an idempotent consumer |
| The business cannot tolerate even brief intermediate states | redesign the boundary, or use reservations |
In an interview, the strong answer starts with "can we avoid a distributed transaction?" and names the price of each option: 2PC blocks and needs every participant's support; sagas are available but not isolated and need compensation design.
8. Common mistakes
- Charging first and calling the next service with no plan for its failure (no compensation, no state).
- Treating a compensation as a rollback: assuming nothing observed the intermediate state, or that the undo always works.
- Non-idempotent steps and compensations, so a retry after a crash charges or refunds twice.
- Keeping the saga state only in memory.
- Updating the saga log and sending the next command as two separate writes.
- Running 2PC across services or providers that do not support it, or leaving prepared transactions around after a failure.
- No timeouts: one hung service holds a saga (and 2PC locks) forever.
- Putting irreversible steps early in the saga.
- Distributing a transaction because of how services were cut, when merging them would remove the need.
9. Things to mention in an interview
- Ask first whether the transaction can be avoided (one owner, or eventual consistency).
- Explain 2PC: prepare then commit, the promise a "yes" vote makes, why it blocks when the coordinator fails, and that many systems cannot participate.
- Explain a saga: local transactions plus compensations, orchestration versus choreography, no isolation, and the countermeasures (semantic lock, reservation).
- Explain how it survives crashes: persisted state, idempotent steps keyed by saga id, outbox for commands, timeouts.
- Name the order of steps: compensatable first, pivot, retriable last.
Questions interviewers like to add:
- "Why not just use 2PC everywhere?" It blocks while the coordinator is down, ties together the availability of every participant, and external services do not support it.
- "What if the compensation fails?" Retry it (it must be idempotent); if it keeps failing, the saga is stuck and needs an alert and a human or a different business remedy.
- "How do you stop two sagas from interfering?" Semantic locks, reservations, commutative updates or version checks, chosen for the anomaly that matters.
- "A service crashed after doing its work but before replying. What happens?" The orchestrator repeats the step; an idempotency key makes the repeat harmless.
- "Is a saga eventually consistent?" Only if it is designed to be: the system reaches a valid state when the saga completes or all its compensations succeed, and is visibly inconsistent in between. A compensation that fails for good, or an invariant nobody wrote a compensation for, leaves the data wrong until someone repairs it.
Quick recap
- Across databases there is no free atomicity: avoid the distributed transaction if one owner or eventual consistency will do.
- 2PC: prepare (a durable promise, locks held), then commit. In the test the changes were invisible until
COMMIT PREPARED, and a dead coordinator left both transactions holding their locks, surviving even a database restart. - 2PC costs about 1.6 times the disk work per participant in the test and, more importantly, blocks writers while in doubt; many systems cannot take part.
- A saga is a sequence of committed local transactions with compensations; it has no isolation, so intermediate states are visible (the balance showed 10 then 40), and it restores a valid state only if the invariants and every compensation path were designed for it.
- Compensation is a new business action, not a rollback: it must be repeatable, and irreversible steps go last.
- Persist saga state, key every step by the saga id, and send commands through an outbox; a repeated charge after a crash left the balance unchanged.
- Order steps as compensatable, pivot, retriable; add timeouts and a way to see stuck sagas.
Appendix: the experiment code
Run these yourself. The first needs a PostgreSQL server started with max_prepared_transactions above 0 (for example postgres -c max_prepared_transactions=20); the second needs only Python 3.
PostgreSQL: a two-phase commit by hand
Create two databases, bank (table accounts(id int primary key, balance int check (balance >= 0)), one row with balance 100) and stock (table stock(item text primary key, qty int check (qty >= 0)), one row ('book', 1)). Then, as the coordinator, run each line against the database named in the comment:
-- in database bank
BEGIN; UPDATE accounts SET balance = balance - 30 WHERE id = 1; PREPARE TRANSACTION 'o1-bank';
-- in database stock
BEGIN; UPDATE stock SET qty = qty - 1 WHERE item = 'book'; PREPARE TRANSACTION 'o1-stock';
-- both voted yes: look at them, and note that SELECT still shows the old values
SELECT gid, prepared FROM pg_prepared_xacts;
-- phase 2, in each database
COMMIT PREPARED 'o1-bank'; -- in bank
COMMIT PREPARED 'o1-stock'; -- in stock
-- to abort instead, when one side failed
ROLLBACK PREPARED 'o1-bank';
To see the blocking, prepare both and stop there: then SET lock_timeout = '2s'; UPDATE accounts SET balance = balance - 1 WHERE id = 1; from another session waits and fails. Restart the server and pg_prepared_xacts still lists them.
Python: an orchestrated saga with failure injection
Save as saga.py and run python3 saga.py. It prints the four scenarios described above. It is a teaching sample: it has one process, one worker, in-memory retries and no versioning, and omits everything listed under "The demonstration is not a production orchestrator".
import os, sqlite3, tempfile, threading, time
tmp = tempfile.mkdtemp()
def db(name):
return sqlite3.connect(os.path.join(tmp, name), isolation_level=None, check_same_thread=False, timeout=5)
orders, pay, stock, log = db("orders"), db("payments"), db("stock"), db("saga_log")
orders.execute("create table orders(id text primary key, status text)")
pay.execute("create table accounts(id int primary key, balance int)")
pay.execute("create table done(step text primary key)") # which (saga, step) already ran
pay.execute("insert into accounts values (1, 100)")
stock.execute("create table items(name text primary key, qty int)")
stock.execute("create table done(step text primary key)")
stock.execute("insert into items values ('book', 1)")
log.execute("create table saga(id text primary key, step int, state text)") # durable progress
class Crash(Exception): pass
fail = {"stock": False, "refund_times": 0, "crash_after_charge": False}
# the local transactions of each service; each is idempotent per saga id
def create_order(sid):
orders.execute("insert or ignore into orders values (?, 'PENDING')", (sid,))
def charge(sid, amount):
pay.execute("begin")
if pay.execute("select 1 from done where step=?", (f"charge:{sid}",)).fetchone():
pay.execute("commit"); return # a repeat does nothing
pay.execute("update accounts set balance = balance - ? where id=1", (amount,))
pay.execute("insert into done values (?)", (f"charge:{sid}",))
pay.execute("commit")
def refund(sid, amount):
if fail["refund_times"] > 0:
fail["refund_times"] -= 1; raise ConnectionError("payment service unavailable")
pay.execute("begin")
if pay.execute("select 1 from done where step=?", (f"refund:{sid}",)).fetchone():
pay.execute("commit"); return
pay.execute("update accounts set balance = balance + ? where id=1", (amount,))
pay.execute("insert into done values (?)", (f"refund:{sid}",))
pay.execute("commit")
def reserve(sid):
if fail["stock"]: raise ValueError("out of stock")
stock.execute("begin")
if stock.execute("select 1 from done where step=?", (f"reserve:{sid}",)).fetchone():
stock.execute("commit"); return
n = stock.execute("update items set qty = qty - 1 where name='book' and qty > 0").rowcount
if not n: stock.execute("rollback"); raise ValueError("out of stock")
stock.execute("insert into done values (?)", (f"reserve:{sid}",)); stock.execute("commit")
def confirm(sid): orders.execute("update orders set status='CONFIRMED' where id=?", (sid,))
def cancel(sid): orders.execute("update orders set status='CANCELLED' where id=?", (sid,))
def retry(fn, *a): # compensations must eventually succeed
for attempt in range(1, 10):
try: return fn(*a), attempt
except ConnectionError: time.sleep(0.05)
raise RuntimeError("giving up: needs a human")
def run_saga(sid, amount=30):
row = log.execute("select step, state from saga where id=?", (sid,)).fetchone()
if not row: log.execute("insert into saga values (?, 0, 'RUNNING')", (sid,)); row = (0, "RUNNING")
step, state = row
def mark(s, st="RUNNING"): log.execute("update saga set step=?, state=? where id=?", (s, st, sid))
try:
if step < 1: create_order(sid); mark(1)
if step < 2:
charge(sid, amount)
if fail["crash_after_charge"]: fail["crash_after_charge"] = False; raise Crash()
mark(2)
if step < 3: reserve(sid); mark(3)
confirm(sid); mark(4, "DONE")
except ValueError as e: # a step failed for a business reason
mark(step, "COMPENSATING")
_, tries = retry(refund, sid, amount)
cancel(sid); mark(step, "COMPENSATED")
print(f" {sid}: {e}; refunded after {tries} attempt(s), order cancelled")
def snap():
bal = pay.execute("select balance from accounts").fetchone()[0]
qty = stock.execute("select qty from items").fetchone()[0]
st = dict(orders.execute("select id, status from orders").fetchall())
return f"balance={bal} stock={qty} orders={st}"
print("1. happy path"); run_saga("s1"); print(" ", snap())
print("2. stock runs out at step 3 -> compensate"); fail["stock"] = True
fail["refund_times"] = 2; run_saga("s2"); print(" ", snap()); fail["stock"] = False
print("3. the orchestrator crashes after charging but before recording it, then restarts")
stock.execute("update items set qty = 5"); fail["crash_after_charge"] = True
try: run_saga("s3")
except Crash: print(" crashed; money already taken:", snap())
run_saga("s3"); print(" resumed:", snap())
print("4. what a reader sees while a saga is in flight (balance sampled every 2 ms)")
seen = []
stop = threading.Event()
def reader():
r = db("payments")
while not stop.is_set():
seen.append(r.execute("select balance from accounts").fetchone()[0]); time.sleep(0.002)
def slow_reserve(sid): time.sleep(0.1); raise ValueError("out of stock")
reserve = slow_reserve
t = threading.Thread(target=reader); t.start(); before = pay.execute("select balance from accounts").fetchone()[0]
run_saga("s4"); time.sleep(0.02); stop.set(); t.join()
print(f" balance before {before}; values seen during the saga: {sorted(set(seen))}; after {pay.execute('select balance from accounts').fetchone()[0]}")
Sources
- H. Garcia-Molina and K. Salem, Sagas, ACM SIGMOD 1987. The original definition of a saga and compensating transactions.
- PostgreSQL documentation, PREPARE TRANSACTION, with COMMIT PREPARED and ROLLBACK PREPARED. The participant side of two-phase commit, its locking and its cleanup warnings.
- Chris Richardson, Pattern: Saga. Orchestration and choreography, and the countermeasures for the missing isolation.
- Pat Helland, Life beyond Distributed Transactions: an Apostate's Opinion, CIDR 2007. Why large systems avoid distributed transactions and how entities and messages replace them.
Practise this topic: System Design System Design › Distributed systems