Two-phase commit vs sagas

The problem: moving a link between owners

An agency hands a campaign link over to its client: x7Kp moves from owner 4821 to owner 9310. Since Snip sharded (05.01 Sharding: range, hash, directory), each owner's list of links lives on the owner's shard, and these two owners are on different Postgres machines. The move is two writes in two databases:

• Delete (4821, x7Kp) from owner shard A.
• Insert (9310, x7Kp) into owner shard B.

Do one without the other and the link either vanishes from both dashboards or appears in both. Each database can make its own write atomic, but not the pair.

Two-phase commit

Two-phase commit (2PC) adds a coordinator that runs the transaction on every participant and then commits in two rounds:

1. Prepare. The coordinator asks each participant to get ready to commit. A participant that says yes has made its changes durable, holds its locks, and promises it can commit if told to, even after a crash. It may no longer decide to abort on its own.
2. Commit. If every participant voted yes, the coordinator records the decision and tells each to commit. If any voted no, it tells all to roll back.

Postgres supports the participant side directly. PREPARE TRANSACTION stores the transaction's state on disk, and COMMIT PREPARED or ROLLBACK PREPARED finishes it later, even from another session:

-- On owner shard A
BEGIN;
DELETE FROM links_by_owner WHERE owner_id = 4821 AND code = 'x7Kp';
PREPARE TRANSACTION 'move-x7Kp-a';

-- On owner shard B
BEGIN;
INSERT INTO links_by_owner (owner_id, code) VALUES (9310, 'x7Kp');
PREPARE TRANSACTION 'move-x7Kp-b';

-- Both prepared: the coordinator records "commit", then
COMMIT PREPARED 'move-x7Kp-a';   -- on A
COMMIT PREPARED 'move-x7Kp-b';   -- on B

Tested on PostgreSQL 16 with two databases (prepared transactions are off by default; max_prepared_transactions must be raised and needs a restart): both transactions showed up in pg_prepared_xacts, and after the commits shard A had no row for owner 4821 and shard B had (9310, x7Kp).

The weakness: a coordinator that disappears

Between prepare and commit, every participant is in doubt. It has promised to commit if told to, so it can't roll back on its own, and it can't commit without the decision. If the coordinator crashes at that moment, the participants wait, holding their locks, until it comes back.

That was the second test: a transaction prepared on shard B and then left alone, as if the coordinator had died. An ordinary update to the same row then waited on the prepared transaction's lock until it hit a 2-second lock timeout, and the prepared transaction stayed in pg_prepared_xacts until someone ran ROLLBACK PREPARED. Postgres's own documentation warns that leaving transactions prepared for long interferes with VACUUM, and that they keep holding whatever locks they took.

So classic 2PC is a blocking protocol, and it adds a round trip and holds locks across both phases. Making it practical means making the coordinator itself fault-tolerant.

How distributed databases make it safe

Databases that run transactions across many machines, such as Spanner and CockroachDB, use the same two-phase idea, but each participant, and the record of the decision, is itself a replicated group running consensus (08.06 Consensus and Raft, intuitively). A coordinator machine can die, and the decision is still on a majority of replicas, so another can finish the job.

CockroachDB, for example, keeps a transaction record in the range where the first write happened, and writes intents, provisional values that also act as exclusive locks, replicated through Raft. Its commit protocol, Parallel Commits, stages the record and checks that all the intents are in place, then resolves them asynchronously. From the application's point of view it's just a transaction.

2PC or saga?

In the end, Snip's link handover needs neither. The source of truth is the link's own row on its link shard, where owner_id changes in one ordinary local transaction. The owner lists are derived copies (05.01 Sharding: range, hash, directory), updated from a link.transferred event through the outbox (07.06 The outbox pattern and change data capture), and may lag a second behind. Often the best answer to "do I need 2PC?" is to arrange the data so that one database holds the truth and the rest follows. Where a change truly must be atomic across machines, use a database that provides distributed transactions rather than hand-rolling 2PC over separate shards.

The custom-domain setup (07.08 Workflows and sagas), which involves a payment provider, a certificate authority and a CDN, can't use 2PC at all: those services don't offer a "prepare" step, and holding locks for a day while DNS changes would be absurd. That's a saga.

Recap

Interview questions