Distributed Transactions

Hook: Interviewers love this because it tests whether you know the difference between “my transaction succeeded” and “the whole business action succeeded across systems.”

Question: What is a distributed transaction in SQL, and why is it harder than a normal transaction?

Answer: A distributed transaction is one logical transaction that touches more than one database or resource manager, but still needs to behave like one all-or-nothing unit. The hard part is making sure every participant either commits or rolls back together, even if a network link fails or a server crashes. SQL systems usually solve this with two-phase commit, which is reliable but slower and can block during failures.

Interview-Ready Answer: I’d say a distributed transaction is a single business operation that spans multiple databases or services, but still needs atomicity, meaning either everything commits or nothing does. The classic SQL solution is two-phase commit: a coordinator asks each participant to prepare, then only commits if everyone votes yes. It gives strong consistency, but it adds extra network round trips, holds locks longer, and can block if a node crashes at the wrong time, so I only use it when I truly need hard all-or-nothing guarantees across systems.

What it is

A distributed transaction is a transaction that spans multiple participants or resource managers (a database, queue, or other system that owns data and locks). The goal is still ACID atomicity: either every participant commits the change, or every participant rolls it back. This is much harder than a local SQL transaction because the coordinator cannot trust one server’s word alone; it has to coordinate and verify every node.

How it works under the hood

1. Start one global transaction. A coordinator creates a transaction ID and tracks all participants. Think of the coordinator as the “project manager” for the whole business action.
2. Each participant does local work. Every database makes tentative changes and holds locks, but those changes are not final yet.
3. Coordinator sends PREPARE. Each participant writes a durable log record saying, “I am ready to commit if asked.” Durable means written to disk so the decision survives a crash.
4. Each participant votes yes or no. If one node cannot guarantee durability or sees an error, it votes no.
5. If all votes are yes, coordinator commits. It logs the final decision, then sends COMMIT to everyone. If any vote is no, it sends ROLLBACK instead.
6. Participants finish recovery safely. After commit or rollback, they release locks and clear the prepared state. If a crash happened earlier, recovery reads the log and continues from the last durable decision.

Why it is tricky

The classic protocol is two-phase commit, or 2PC. It is strong, but it has a blocking problem: if a participant has prepared and then the coordinator disappears before sending the final decision, that participant may have to wait, holding locks, until recovery tells it what to do. That is why distributed transactions are safe but operationally expensive.

Compared with alternatives

Use a distributed transaction when correctness matters more than latency, and when losing atomicity would create money loss, duplicate inventory, or legal inconsistency. Avoid it for chat messages, analytics, or loosely coupled workflows where eventual consistency is acceptable.

Performance and operational cost

2PC is usually O(n) in the number of participants: each participant adds coordination, log flushes, and lock time. In practice, even on a fast LAN, a single extra durable round trip per node can turn a tiny write into tens of milliseconds; across regions, it can become 100 ms or more. That matters because locks stay held while the transaction is prepared, which can reduce throughput and create queueing. In PostgreSQL, two-phase commit exists, but prepared transactions are disabled unless max_prepared_transactions is set above zero; many teams leave it off because dangling prepared transactions are an operational hazard.

Edge cases interviewers like

• Coordinator crash after prepare: participants are safe but may be blocked until recovery reveals the decision.
• Network partition: a node may not know whether to commit or abort, so “just retry” is not enough.
• Partial failure after local commit: if one side commits and the other does not, you no longer have atomicity; this is exactly what distributed transactions try to prevent.
• Timeouts: too short causes false aborts; too long holds locks and hurts concurrency. Real systems often use timeouts in the 5–30 second range, depending on risk tolerance.

Memory model: “Prepare first, decide once, release last.” If you can replay those three ideas, you can reconstruct 2PC under pressure.

Real-World Example: Imagine a checkout service where the orders database lives in one SQL cluster and the inventory database lives in another. A customer clicks “Buy Now,” and the system must create the order row, reserve stock, and record payment state as one business action. If the order commits but inventory fails, the store can oversell. If inventory reserves but the order rolls back, stock gets stuck and nobody can buy it. In production, a misunderstanding here often shows up as “pending” orders that never finish, locks on hot SKU rows, and logs with repeated prepare/timeout messages. Users complain that they were charged but never got confirmation, or that the last few items in stock disappear and never come back until an operator manually cleans up prepared work.

Follow-up & Tricky Questions:

• How does two-phase commit work? First the coordinator asks everyone to prepare, then it sends the final commit or rollback decision. The important detail is that participants durably log their prepared state before voting yes.
• Why are distributed transactions slow? They add extra network hops, extra disk flushes, and longer lock holding time. That is why they can be correct but still hurt throughput.
• What happens if one participant fails after prepare? The transaction may block until recovery or a final decision is found in the log. The key point is that prepared state is not the same as committed state.
• When would you use a saga instead? Use a saga when business steps can be compensated later, like canceling a shipment or refunding a payment. It scales better than 2PC, but it gives eventual consistency instead of strict atomic commit.
• How do retries fit in? Retries must be idempotent, meaning repeating the same request should not create duplicate effects. Distributed transactions do not remove the need for idempotency because clients and coordinators can still retry after a timeout.
• Is 2PC the same as ACID? No. 2PC is one protocol that helps implement atomic commit across nodes, but ACID is the larger property set for transactions.
• Does a distributed transaction guarantee zero duplicates? Not by itself at the business level. You still need unique keys, idempotency tokens, or deduplication to handle client retries and message replays.
• Can I just use COMMIT on each database separately? No, because a failure between the two commits creates split-brain data. That is exactly the bug distributed transactions are meant to avoid.

Common Mistakes:

• Thinking distributed transactions are “just a bigger local transaction.” Correction: they add coordination, network failure, and recovery complexity.
• Assuming prepare means committed. Correction: prepared means “ready to commit,” not “done.”
• Using 2PC for everything. Correction: for many workflows, sagas or the outbox pattern are simpler and faster.
• Ignoring lock duration and timeouts. Correction: prepared transactions can block hot rows and create cascading latency.

Memory Hook: “Prepare, vote, decide.” Picture a choir: everyone practices first, the conductor asks for a yes, and only then does the whole choir perform. If one singer says no, nobody goes on stage.

Cheat Sheet:

• Distributed transaction = one logical transaction across multiple participants.
• Classic protocol = two-phase commit, or 2PC.
• Goal = atomicity across nodes: all commit or all roll back.
• Big trade-off = strong consistency vs higher latency and blocking risk.
• Common alternatives = saga, outbox, idempotent writes.
• Operational warning = prepared transactions hold locks and need cleanup.

Practice Tasks:

• Write a local SQL transaction that inserts into two tables and verify rollback removes both rows.
• Add a unique constraint or check constraint and reason about what would happen if one participant rejected the change during prepare.
• Describe, out loud, how you would replace 2PC with a saga for an order-and-payment workflow.