HardSQL#1628 min readJul 11, 2026
Two Phase Commit
practice
Hook: Interviewers love this question because it reveals whether you understand how a system keeps all databases in sync when one of them says yes and another says no.
Question: What is Two Phase Commit?
Answer: Two Phase Commit, or 2PC, is a distributed transaction protocol that makes multiple databases or services either all commit or all abort together. One coordinator asks each participant to prepare first; only if every participant agrees does the coordinator send the final commit. The big idea is atomicity, which means you do not get a partial result.
Interview-Ready Answer: I think of Two Phase Commit as a vote-and-go protocol. First, the coordinator asks every participant to prepare and hold its work, and each one votes yes or no. If every vote is yes, the coordinator records the final decision and tells everyone to commit; otherwise it tells everyone to roll back. That gives me all-or-nothing behavior across systems, but the trade-off is that prepared transactions can block resources if the coordinator fails before the final decision is delivered.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3ETwo%20Phase%20Commit%3C%2Ftext%3E%3C%2Fsvg%3E)
What it is
Detailed Explanation: Two Phase Commit is a protocol for a distributed transaction, meaning one logical transaction spans more than one resource manager. A resource manager is a system that can commit or roll back its own local work, such as a database. The goal is simple: either everybody makes the change, or nobody does. In SQL interviews, this usually comes up when a single database is not enough and you need correctness across multiple databases, shards, or external transactional systems.
How it works under the hood
- Begin the global transaction. The coordinator creates one logical transaction id and tracks every participant that must join.
- Phase 1: prepare. The coordinator asks each participant to prepare. A participant checks whether it can safely commit locally: constraints, locks, disk space, deadlocks, or any business rule that would make the change invalid.
- Each participant writes durable state. Before voting yes, it must force enough information to its write-ahead log so recovery can replay or undo the work later. Write-ahead logging means the log is written before the data pages are made durable.
- Each participant votes. If it is ready, it replies yes; otherwise it replies no. A yes vote means, 'I have everything I need to finish later, and I am holding the locks.'
- The coordinator decides. If every vote is yes, the coordinator logs the global commit decision durably. If any vote is no, or a timeout occurs, the coordinator logs abort.
- Phase 2: commit or abort. The coordinator sends the final decision to every participant. Each participant applies the decision, releases locks, and acknowledges completion.
- Recovery after failure. If a participant restarts and finds a prepared transaction in its log, it knows the local part was not finished. If the coordinator already recorded the final decision, recovery is straightforward. If the coordinator crashed before recording a decision, the participant is in an in-doubt state and may have to wait.
Why the blocking problem matters
The hardest part of 2PC is that it is blocking. Blocking means a participant may have to wait while holding locks, unable to finalize on its own. If the coordinator dies after everyone voted yes but before the final commit or abort message arrives, the participants cannot safely guess. Guessing can break atomicity, so they wait. That is why 2PC favors correctness over availability.
When and why to use it
Use 2PC when you truly need all-or-nothing semantics across multiple systems, such as a bank ledger and an audit store, or two databases that both must change together. Do not use it just because it sounds strict; the extra latency and blocking are expensive. In many product flows, a saga is better. A saga is a sequence of local transactions with compensating actions, which means 'undo' steps if a later step fails.
Comparison with common alternatives
| Approach | Guarantee | Main trade-off |
|---|---|---|
| 2PC | All commit or all abort | Can block on failure |
| Single DB transaction | Atomic inside one database | Does not span systems |
| Saga | Eventual consistency | Needs compensating logic |
Performance and edge cases
In terms of cost, 2PC is roughly O(n) in messages and log writes for n participants. A simple mental model is two network rounds: one to gather votes and one to deliver the decision. If you have 5 participants, the coordinator is at least talking to 5 systems during prepare and again during commit or abort, plus it must flush its own decision to durable storage. On a fast local network, that may be only a few milliseconds of extra overhead; across regions, it can become hundreds of milliseconds. Timeouts are system-specific, not standardized by SQL, so production teams often tune them in the seconds range based on latency and failure recovery plans.
Important edge cases: a participant can vote no, which should abort the whole transaction; the coordinator can crash after prepare, which causes the blocking problem; and a participant can recover later and find itself prepared but not finished, which is why prepared transactions must be tracked carefully. A dangerous special case is a heuristic decision, meaning one side makes a local guess after waiting too long. Heuristic decisions can break atomicity, so they are usually a last resort and treated as an incident.
In SQL systems, the idea shows up as distributed transaction support. Different databases expose it differently, but the same protocol idea applies: vote, decide, commit, recover.
Real-world story
Real-World Example: Imagine a checkout service for an online store. One database stores the order row, another stores the payment ledger, and a third stores inventory reservations. The business rule is simple: if the card is charged, the order and inventory reservation must exist too. That is exactly the kind of cross-system atomicity 2PC is designed for.
Now picture an outage. The coordinator prepares the order and payment participants successfully, but crashes before sending the final commit to inventory. Orders start showing up as 'pending', payment records say 'ready', and inventory rows stay locked. Customers see spinning checkouts or temporary charges that never turn into completed orders. On-call engineers may find logs with phrases like 'prepared transaction' or 'in-doubt transaction', and the database may show a growing list of transactions that cannot finish because the final decision is missing.
The bug usually comes from misunderstanding the protocol: someone assumes 'prepare' means 'already committed'. It does not. Prepare means 'I am ready and holding my local state, but I am still waiting for the coordinator's final decision.' That tiny distinction is what saves the system from partial writes.
SQL
-- Two Phase Commit in pure SQL style: this is a small local simulation of the
-- protocol, showing the same idea a distributed coordinator would enforce.
-- The success path commits only when both participants are ready.
-- The failure path shows that the whole transaction is rolled back when one
-- participant cannot proceed.
DROP TABLE IF EXISTS transfer_log;
DROP TABLE IF EXISTS ledger_a;
DROP TABLE IF EXISTS ledger_b;
CREATE TABLE ledger_a (
account_id INTEGER PRIMARY KEY,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE ledger_b (
account_id INTEGER PRIMARY KEY,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transfer_log (
transfer_id INTEGER PRIMARY KEY,
note TEXT NOT NULL
);
INSERT INTO ledger_a (account_id, balance) VALUES (1, 1000);
INSERT INTO ledger_b (account_id, balance) VALUES (1, 500);
-- Success path: both logical participants can prepare, so the coordinator
-- would allow the global commit.
BEGIN TRANSACTION;
-- Phase 1: participant A prepares its local change.
-- The WHERE clause is the local safety check: if the row does not satisfy the
-- rule, this participant would vote NO in a real 2PC implementation.
UPDATE ledger_a
SET balance = balance - 100
WHERE account_id = 1 AND balance >= 100;
-- Participant B prepares its matching change.
UPDATE ledger_b
SET balance = balance + 100
WHERE account_id = 1;
-- The coordinator records the decision after all participants are ready.
INSERT INTO transfer_log (transfer_id, note)
VALUES (1, 'commit: both participants voted yes');
COMMIT;
-- Failure path: participant A cannot support such a large debit, so the
-- coordinator must abort the whole transaction instead of leaving partial work.
BEGIN TRANSACTION;
SAVEPOINT prepare_phase;
-- This update affects zero rows because the balance check fails.
-- That is the SQL version of a participant saying, 'I cannot vote yes.'
UPDATE ledger_a
SET balance = balance - 1000
WHERE account_id = 1 AND balance >= 1000;
-- Even if another participant had already done local work, a real coordinator
-- would not finalize anything after a NO vote.
UPDATE ledger_b
SET balance = balance + 1000
WHERE account_id = 1;
-- Abort the whole transaction so no partial result survives.
ROLLBACK TO prepare_phase;
ROLLBACK;
-- Final state: only the committed transfer survives; the aborted one leaves
-- no trace.
SELECT 'ledger_a' AS table_name, account_id, balance
FROM ledger_a
UNION ALL
SELECT 'ledger_b' AS table_name, account_id, balance
FROM ledger_b
ORDER BY table_name, account_id;
SELECT *
FROM transfer_log
ORDER BY transfer_id;Follow-up & Tricky Questions:
- How is 2PC different from a normal SQL transaction? A normal transaction usually covers one database and one transaction manager. 2PC coordinates multiple resource managers so the final result is atomic across them.
- What happens if one participant votes no? The coordinator must abort the global transaction, and every participant rolls back its local changes.
- Why is 2PC called blocking? Because after prepare, participants may hold locks while waiting for the final decision. If the coordinator disappears at the wrong time, they cannot safely continue on their own.
- What does prepared mean? It means the participant has durably recorded enough information to finish later, but it has not yet been told to commit or abort.
- Where do you see 2PC in real SQL systems? In distributed transaction support, such as prepared transactions in PostgreSQL-style systems, XA-style coordination, or database transaction managers that sit above multiple engines.
- Is 2PC the same as ACID? No. 2PC helps with atomicity across multiple systems, but it does not magically give you high availability or good performance.
- Can a participant just decide to commit after a timeout? No. That is a heuristic decision and can create inconsistency; it is generally treated as an exceptional recovery action, not normal behavior.
Common Mistakes:
- Thinking prepare means commit. Correction: prepare only means the participant is ready and waiting; the final decision still has to arrive.
- Forgetting about locks. Correction: prepared transactions can hold locks for a long time, which can stall other work and create timeouts.
- Using 2PC for long business workflows. Correction: long flows are usually better handled with sagas, retries, and compensating actions.
- Ignoring the coordinator failure case. Correction: the most famous weakness of 2PC is that it can block if the coordinator dies after prepare.
Memory Hook: Think: 'Everybody raises a hand first, then the teacher says go.' No one leaves the room until every hand is up and the final signal arrives.
Cheat Sheet:
- 2PC gives all-or-nothing behavior across multiple resource managers.
- Phase 1 is prepare/vote; Phase 2 is commit/abort.
- Participants must durably log and hold locks before voting yes.
- The coordinator logs the final decision before telling others.
- It is atomic, but it can block if the coordinator fails.
- Use it only when you truly need strong cross-system consistency.
Practice Tasks:
- Draw a sequence diagram with one coordinator and three participants.
- Explain why a coordinator crash after prepare is the hardest failure mode.
- Compare 2PC and a saga for an order checkout flow and decide which one you would choose.