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.

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:

ScenarioWhat happened
Both prepare, then both commitafter 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 partthe 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 preparedboth 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 preparedboth 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:

OrchestrationChoreography
Howa central orchestrator (a state machine) tells each service what to do nexteach service reacts to events published by the others
Where the flow livesin one place, easy to read, test and monitorspread across services, implicit
Couplingservices depend on the orchestrator's commandsservices depend on each other's events, and the cycle risk grows with each new event
Fitsflows with many steps or branchestwo 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:

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:

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.

  1. 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.
  2. 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.
  3. 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:

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:

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

SituationReach for
The data can live in one databasea normal local transaction (option zero)
A few resource managers, in your control, all supporting XA, short operationstwo-phase commit
Several services, external APIs, long-running steps, availability mattersa 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 statesredesign 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

  1. Charging first and calling the next service with no plan for its failure (no compensation, no state).
  2. Treating a compensation as a rollback: assuming nothing observed the intermediate state, or that the undo always works.
  3. Non-idempotent steps and compensations, so a retry after a crash charges or refunds twice.
  4. Keeping the saga state only in memory.
  5. Updating the saga log and sending the next command as two separate writes.
  6. Running 2PC across services or providers that do not support it, or leaving prepared transactions around after a failure.
  7. No timeouts: one hung service holds a saga (and 2PC locks) forever.
  8. Putting irreversible steps early in the saga.
  9. Distributing a transaction because of how services were cut, when merging them would remove the need.

9. Things to mention in an interview

Questions interviewers like to add:

Quick recap

  1. Across databases there is no free atomicity: avoid the distributed transaction if one owner or eventual consistency will do.
  2. 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.
  3. 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.
  4. 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.
  5. Compensation is a new business action, not a rollback: it must be repeatable, and irreversible steps go last.
  6. 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.
  7. 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

  1. H. Garcia-Molina and K. Salem, Sagas, ACM SIGMOD 1987. The original definition of a saga and compensating transactions.
  2. PostgreSQL documentation, PREPARE TRANSACTION, with COMMIT PREPARED and ROLLBACK PREPARED. The participant side of two-phase commit, its locking and its cleanup warnings.
  3. Chris Richardson, Pattern: Saga. Orchestration and choreography, and the countermeasures for the missing isolation.
  4. 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

More guides