SQL 119 🛢️ Two-Phase Commit (2PC) in Distributed Transactions
Two-Phase Commit is the classic atomic commitment protocol for distributed transactions. It exists to solve a single problem: how do multiple independent resource managers—databases, message queues, transactional caches—agree to either commit or abort a transaction as a unit, even when some of them fail during the process? The protocol is not a consensus algorithm in the Raft or Paxos sense, though it is often discussed alongside them. It is a coordinator-driven voting mechanism with a known and well-documented weakness: it is a blocking protocol, and if the coordinator fails after participants have voted to commit, those participants hold their locks indefinitely until the coordinator recovers .
This chapter covers the two phases, the message flow, the failure modes that make 2PC controversial, and the optimizations that reduce its cost in practice. It also covers the X/Open XA standard, which is the interface specification that allows heterogeneous resource managers to participate in a 2PC transaction.
Key point: 2PC guarantees atomicity, not availability. Once a participant votes “yes,” it cannot unilaterally abort. If the coordinator fails after collecting the votes but before broadcasting the decision, every participant that voted yes is blocked, holding locks, until the coordinator recovers .
Why 2PC exists
The atomicity problem. A transaction that spans two databases must either commit on both or abort on both. If it commits on one and aborts on the other, the data is inconsistent. In a single database, atomicity is guaranteed by the write-ahead log and the local transaction manager. In a distributed system, there is no single log and no single manager. 2PC exists to coordinate the local commit decisions of independent managers into a global decision .
The agreement problem. Participants cannot simply commit when they are ready. If participant A commits and participant B then fails before committing, the transaction has partially committed and cannot be rolled back on A. The protocol requires all participants to agree to commit before any of them actually does. The first phase collects the votes; the second phase broadcasts the decision. This two-step structure is what makes atomicity possible across independent nodes .
The standard interface problem. Before the X/Open XA specification, every distributed transaction implementation used its own interface for enlisting resources and coordinating commits. An application that wanted to coordinate a database and a message queue had to write vendor-specific code for each. XA standardized the interface between a transaction manager and resource managers, allowing heterogeneous components to participate in a single 2PC transaction . This standardization is why 2PC remains relevant despite its known limitations.
The blocking trade-off. 2PC was designed for correctness, not availability. When a participant votes “yes,” it promises to follow the coordinator’s decision, whatever it is. It cannot time out and abort, because the coordinator may have already decided to commit. This promise is what makes the protocol correct. It is also what makes it blocking: the participant has no way to resolve the transaction unilaterally if the coordinator is unavailable . The trade-off is deliberate. Systems that cannot tolerate blocking use different protocols or architectures.
a. The Two Phases and Message Flow
The protocol assumes a designated coordinator and a set of participants. The coordinator drives the transaction; the participants execute local operations and vote. The protocol begins after the application has issued its final statement and requested a commit .
Phase 1: Prepare (Voting). The coordinator sends a PREPARE message to every participant. Each participant executes the transaction locally up to the point of commit, writing undo and redo records to its log. If the local work succeeds, the participant forces a “prepared” record to stable storage and votes YES. If anything fails, it votes NO and aborts locally . The forced log write is critical: once a participant votes YES, it must be able to commit even if it crashes and restarts before receiving the decision. The prepared state in the log is the promise it made.
Phase 2: Commit or Abort. The coordinator collects the votes. If any participant voted NO, or if a timeout expires, the coordinator decides to abort. It forces an abort record to its own log and broadcasts ROLLBACK to all participants. Each participant undoes its changes and releases its locks . If all participants voted YES, the coordinator decides to commit. It forces a commit record to its log and broadcasts COMMIT. Each participant applies its changes, releases locks, and sends an acknowledgement. The coordinator completes the transaction when all acknowledgements are received .
The message complexity is approximately 3N − 3 for N participants: N−1 prepare messages, N−1 votes, and N−1 decision messages . The two forced log writes—the participant’s prepared record and the coordinator’s decision record—are the durability points that allow recovery after a crash.
b. Failure Modes and the Blocking Problem
The protocol’s failure behavior is what distinguishes it from more modern consensus protocols. The severity of a failure depends on when it occurs and which node fails.
Coordinator fails before sending PREPARE. Participants have not voted. They can safely abort locally. No blocking occurs.
Coordinator fails during Phase 1. Participants that have not yet voted can abort. Participants that have voted YES are in the prepared state and must wait for the coordinator. If the coordinator recovers, it can read its log, determine that it had not yet decided, and abort. If the coordinator is permanently lost, the prepared participants block .
Coordinator fails after collecting all votes but before broadcasting the decision. This is the worst case. The coordinator has all YES votes but has not yet forced a decision to its log. Participants are prepared and waiting. If the coordinator recovers and finds no decision record, it can abort. If the coordinator is permanently lost, the participants cannot determine whether the coordinator had decided to commit or abort before failing. They block indefinitely, holding locks on the data they modified .
Participant fails after voting YES. The participant’s prepared state is durable. When it recovers, it contacts the coordinator to learn the decision. If the coordinator is available, the participant resolves the transaction. If the coordinator is also unavailable, the participant blocks .
The blocking property is inherent. A participant that has voted YES cannot unilaterally decide. It has promised to follow the coordinator. The only way to break the block is for the coordinator to recover, for an administrator to manually force a decision, or for a more sophisticated recovery mechanism (such as presumed abort) to reduce the window. The XA standard provides a manual resolution path, but it requires administrative intervention and carries the risk of violating atomicity if the decision is wrong .
c. Optimizations and the XA Standard
Several optimizations reduce the cost and frequency of blocking without changing the fundamental protocol.
Presumed Abort. If a participant crashes and recovers, and its log contains no prepared record for a transaction, it can assume the transaction was aborted. This eliminates the need for the coordinator to broadcast abort messages to participants that voted NO or never voted, and it removes the forced abort record from the participant’s log in the common case . The optimization is effective when many transactions abort, which is common in practice.
Presumed Commit. The symmetric optimization for commit-heavy workloads. The coordinator writes a collecting record before sending PREPARE, and participants do not force prepared records. Recovery assumes commit unless a log record indicates otherwise. The trade-off is additional coordinator-side logging and a more complex recovery path .
Read-only participants. A participant that only reads data can vote READ and drop out of the transaction after Phase 1. It does not need to participate in Phase 2 because it has no changes to commit or roll back. This reduces message count and lock duration for read-only resource managers .
XA and the transaction manager. The X/Open XA specification defines the interface between a transaction manager (TM) and resource managers (RMs). The TM drives the 2PC protocol; the RMs execute local operations and vote. XA-compliant databases and message brokers can participate in a single global transaction regardless of vendor . The TM maintains a log of its decisions for recovery. If the TM crashes, its log allows it to resolve in-doubt transactions when it restarts. If the TM’s log is lost, manual intervention is required, and the XA specification provides an escape hatch for administrators to force commit or rollback—at the risk of violating atomicity .
Complete Example Session
-- ============================================
-- PART 1: TRANSACTION INITIATION
-- ============================================
-- Application begins a distributed transaction
-- spanning a database and a message queue
BEGIN DISTRIBUTED TRANSACTION;
-- ============================================
-- PART 2: LOCAL WORK ON EACH PARTICIPANT
-- ============================================
-- Participant A (database): UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Participant B (message queue): ENQUEUE 'transfer' WITH amount = 100;
-- Each participant has executed locally but not committed
-- ============================================
-- PART 3: PHASE 1 — COORDINATOR SENDS PREPARE
-- ============================================
-- Coordinator: PREPARE to all participants
-- Participant A: forces prepared record, votes YES
-- Participant B: forces prepared record, votes YES
-- Coordinator receives all votes
-- ============================================
-- PART 4: COORDINATOR DECIDES COMMIT
-- ============================================
-- Coordinator forces COMMIT record to its log
-- This is the durability point for the global decision
-- ============================================
-- PART 5: PHASE 2 — BROADCAST DECISION
-- ============================================
-- Coordinator: COMMIT to all participants
-- Participant A: applies changes, releases locks, sends ACK
-- Participant B: applies changes, releases locks, sends ACK
-- ============================================
-- PART 6: COORDINATOR COMPLETES
-- ============================================
-- After all ACKs, coordinator writes END record
-- Transaction metadata can be discarded
-- ============================================
-- PART 7: FAILURE — COORDINATOR CRASHES AFTER VOTES
-- ============================================
-- Coordinator has all YES votes but crashes before logging decision
-- Participants are in prepared state, holding locks
-- They cannot abort unilaterally
-- They poll the coordinator or wait for recovery
-- ============================================
-- PART 8: RECOVERY — COORDINATOR RESTARTS
-- ============================================
-- Coordinator reads its log
-- Finds no COMMIT or ABORT record
-- It can safely decide to ABORT
-- Sends ROLLBACK to all participants
-- ============================================
-- PART 9: PRESUMED ABORT OPTIMIZATION
-- ============================================
-- Participant crashes and recovers
-- Log contains no prepared record for transaction X
-- It assumes transaction X was aborted
-- No need to contact coordinator
-- ============================================
-- PART 10: XA INTERFACE
-- ============================================
-- TM: xa_start, xa_end (associate transaction with RM)
-- TM: xa_prepare (Phase 1)
-- TM: xa_commit / xa_rollback (Phase 2)
-- RM: returns XA_OK, XA_RDONLY, or XAER_* error codesThe ten parts trace the successful path and the failure path. Part 7 is the blocking scenario: participants have voted yes and cannot proceed without the coordinator. Part 8 shows recovery when the coordinator’s log is intact. Part 9 shows the presumed abort optimization that reduces recovery cost.
Quick Reference
Protocol Phases
| Phase | Coordinator Action | Participant Action |
|---|---|---|
| Prepare | Send PREPARE to all | Execute locally, force log, vote |
| Commit | Force decision, broadcast | Apply or undo, release locks |
Failure Behavior
| Failure Point | Participant State | Outcome |
|---|---|---|
Before PREPARE | Not prepared | Can abort locally |
| During Phase 1 | Some prepared, some not | Prepared wait for coordinator |
| After all votes, before decision log | All prepared | Blocked if coordinator lost |
| After decision log | All prepared | Recoverable from log |
| Participant crash after YES | Prepared | Wait for coordinator on restart |
XA Functions
| Function | Purpose |
|---|---|
xa_start | Associate transaction with RM |
xa_end | End association |
xa_prepare | Phase 1 prepare |
xa_commit | Phase 2 commit |
xa_rollback | Phase 2 rollback |
Best Practices
✅ Do This:
-- Force the coordinator's decision to stable storage before broadcast
-- This is the durability point for the global transaction -- ✅-- Use presumed abort to reduce abort-path overhead
-- No forced abort record on participants that did not prepare -- ✅-- Keep transactions short to minimize lock hold time
-- Long-running 2PC transactions amplify the blocking problem -- ✅-- Monitor in-doubt transactions
-- Oracle: DBA_2PC_PENDING view -- ✅❌ Don’t Do This:
-- Allow participants to timeout and abort after voting YES
-- Violates atomicity if coordinator decided to commit -- ❌-- Assume 2PC provides high availability
-- It provides atomicity; blocking is by design -- ❌-- Use 2PC across high-latency links without understanding the cost
-- Every phase requires a round-trip to every participant -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Locks held indefinitely | Coordinator failed after votes | Recover coordinator or manually resolve |
| In-doubt transactions accumulate | Coordinator log lost or corrupt | Use DBA_2PC_PENDING to identify and resolve |
| 2PC considered “too slow” | Every phase is a network round-trip | Keep transactions short; use presumed abort |
| Partial commit detected | Manual force was wrong | Review XA recovery procedures |
| Heterogeneous resources fail | One RM does not support XA properly | Verify XA compliance before enlisting |
Real-World Examples
1. Coordinator Sends PREPARE
PREPARE to Participant A, Participant B, Participant C2. Participant Votes YES
Participant forces prepared record, sends YES to coordinator3. Coordinator Logs Decision
Coordinator forces COMMIT record to stable storage4. Coordinator Broadcasts COMMIT
COMMIT to all participants5. Presumed Abort on Recovery
Participant finds no prepared record → assumes abort6. XA Interface
TM calls xa_prepare() on each RM during Phase 17. Oracle In-Doubt Resolution
SELECT * FROM DBA_2PC_PENDING;
COMMIT FORCE 'transaction_id';8. Read-Only Participant Drops Out
Participant votes READ → does not participate in Phase 29. Message Complexity
N participants → approximately 3N − 3 messages10. Blocking Scenario
Coordinator fails after all YES votes → participants blockVisual
The Two Phases
┌──────────────────────────────────────────────────────────────┐
│ COORDINATOR PARTICIPANTS │
│ │
│ PHASE 1: PREPARE │
│ ┌─────────────────┐ │
│ │ Send PREPARE │───────────────►│ Execute locally │
│ │ to all │ │ Force prepared log │
│ └─────────────────┘ │ Vote YES or NO │
│ │◄────────────────────────│ │
│ │ Votes │ │
│ ▼ │ │
│ ┌─────────────────┐ │ │
│ │ All YES? │ │ │
│ └────────┬────────┘ │ │
│ │ YES │ │
│ ▼ │ │
│ PHASE 2: COMMIT │ │
│ ┌─────────────────┐ │ │
│ │ Force COMMIT │ │ │
│ │ to log │ │ │
│ └────────┬────────┘ │ │
│ ▼ │ │
│ ┌─────────────────┐ │ │
│ │ Send COMMIT │───────────────►│ Apply changes │
│ │ to all │ │ Release locks │
│ └─────────────────┘◄───────────────│ Send ACK │
│ │
│ The coordinator's forced decision is the global commit point│
│ │
└──────────────────────────────────────────────────────────────┘The Blocking Scenario
┌──────────────────────────────────────────────────────────────┐
│ COORDINATOR PARTICIPANTS │
│ │
│ ┌─────────────────┐ │
│ │ Send PREPARE │───────────────►│ Vote YES │
│ └─────────────────┘◄───────────────│ Prepared, holding │
│ │ │ locks │
│ ▼ │ │
│ ┌─────────────────┐ │ │
│ │ CRASH before │ │ │
│ │ logging decision│ │ │
│ └─────────────────┘ │ │
│ │ │
│ ┌─────────────────┐ │ │
│ │ (recovering) │ │ WAITING │
│ │ Log intact? │ │ Cannot abort │
│ │ No decision │ │ Cannot commit │
│ │ → ABORT │───────────────►│ Release locks │
│ └─────────────────┘ │ │
│ │
│ If coordinator is permanently lost: │
│ Participants block indefinitely, holding locks. │
│ Manual intervention required. │
│ │
└──────────────────────────────────────────────────────────────┘Presumed Abort Optimization
┌──────────────────────────────────────────────────────────────┐
│ PARTICIPANT CRASHES AND RECOVERS │
│ │
│ ┌─────────────────┐ │
│ │ Read log for │ │
│ │ transaction X │ │
│ └────────┬────────┘ │
│ │ │
│ ┌─────┴─────┐ │
│ ▼ ▼ │
│ ┌──────┐ ┌──────┐ │
│ │Found │ │No │ │
│ │prepare│ │prepare│ │
│ │record│ │record│ │
│ └──┬───┘ └──┬───┘ │
│ │ │ │
│ ▼ ▼ │
│ Contact Assume │
│ coord. ABORT │
│ for (no log │
│ decision write needed) │
│ │
│ Most transactions that fail never reach prepared state. │
│ Presumed abort eliminates unnecessary recovery work. │
│ │
└──────────────────────────────────────────────────────────────┘XA Interface
┌──────────────────────────────────────────────────────────────┐
│ APPLICATION │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ TRANSACTION │ Drives 2PC protocol │
│ │ MANAGER (TM) │ Logs decisions │
│ └────────┬────────┘ │
│ │ XA interface │
│ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ RM: Database│ │ RM: Queue │ │ RM: Cache │ │
│ │ (XA-compl.) │ │ (XA-compl.) │ │ (XA-compl.) │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │
│ XA standardizes the interface between TM and RMs. │
│ Heterogeneous resources participate in one transaction. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Phases | Prepare (voting), Commit (decision) |
| Coordinator | Drives protocol, logs decision |
| Participant vote | YES (prepared) or NO (abort) |
| Durability point | Coordinator’s forced decision log |
| Blocking condition | Coordinator fails after votes, before decision log |
| Presumed abort | No prepared record → assume abort |
| Read-only optimization | Participant votes READ, drops out |
| XA standard | Interface between TM and RMs |
| Message complexity | Approximately 3N − 3 for N participants |
Key takeaways:
- 2PC guarantees atomicity, not availability. The protocol ensures that all participants commit or none do. It does not ensure that the system remains available when the coordinator fails. The blocking property is a deliberate trade-off, not a bug .
- Once a participant votes YES, it cannot abort. The forced prepared record is a promise to follow the coordinator’s decision. This promise is what makes atomicity possible across independent nodes, and it is what causes blocking when the coordinator is unavailable .
- The coordinator’s decision log is the global commit point. If the coordinator crashes before forcing this record, it can safely abort on recovery. If it crashes after forcing the record, it can recover the decision and complete the protocol .
- Presumed abort reduces recovery cost. Participants that crash without a prepared record assume the transaction was aborted. This eliminates unnecessary log writes and coordinator communication in the common case where transactions fail before preparing .
- XA is the interface, not the protocol. The X/Open XA specification defines how a transaction manager and resource managers interact during 2PC. It does not change the protocol’s blocking behavior; it standardizes how heterogeneous resources participate in it .
- Modern distributed databases mitigate 2PC blocking with replication. Google Spanner, for example, replicates each participant using Paxos, so a single participant failure does not prevent the 2PC protocol from completing . The blocking problem is addressed at the participant layer, not by changing 2PC itself.
Remember: Two-Phase Commit is the foundational protocol for distributed atomic transactions, and its limitation is as well-known as its guarantee. It ensures that independent resource managers either all commit or all abort. It does not ensure that the coordinator remains available, and when the coordinator fails at the wrong moment, participants block and hold locks. The protocol is correct, but it is not highly available. Systems that need both atomicity and availability either replicate the coordinator and participants (as Spanner does) or avoid distributed transactions altogether. For the workloads where 2PC is appropriate—short transactions, reliable coordinators, and a tolerance for occasional manual intervention—it remains the standard mechanism for cross-resource atomicity.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!