The Two-Phase Commit (2PC) Protocol

In a distributed database where data is partitioned across multiple independent nodes (e.g. Shard A holds User Accounts, Shard B holds Inventory, Shard C holds Payments), executing a single ACID transaction requires reaching unanimous agreement across all participants.

If even a single node fails or violates a constraint, all nodes must roll back. If all nodes succeed, all nodes must commit.

The classic protocol to solve this is the Two-Phase Commit (2PC) Protocol (Gray, 1978).

---

1. The Protocol Execution Step-by-Step

A 2PC transaction involves two distinct roles:

1. The Coordinator: Coordinates the transaction lifecycle, collects votes, and makes the final decision.
2. The Cohorts (Participants): The individual database nodes executing local queries and holding physical row locks.

---

2. The Critical Invariant: The Point of No Return

The moment the Coordinator flushes the COMMIT record to its own local disk WAL, the transaction is legally and irreversibly committed:

$\text{Decision Point}=\text{Coordinator WAL fsync}(\text{COMMIT})$

Even if every network cable is severed one microsecond later, or all servers crash simultaneously, the outcome of the transaction is permanently fixed:

• When nodes reboot and recover, they must read the Coordinator's WAL and execute the commit.
• A cohort in the PREPARED state is NOT allowed to abort unilaterally. It has surrendered its autonomy to the coordinator.

---

3. Why 2PC is a Blocking Protocol (The Coordinator SPOF)

The fatal flaw of Two-Phase Commit is that it is a Blocking Protocol.

If the Coordinator crashes after cohorts have entered the PREPARED state but before sending the DO_COMMIT / DO_ABORT message, the cohorts are stuck in limbo indefinitely:

Why Cohorts Cannot Decide Among Themselves:

Suppose Cohort 1 and Cohort 2 try to communicate:

• Both are in PREPARED.
• Did the coordinator crash before writing commit to disk, or after writing commit to disk?
• If Cohort 1 decides to abort, but the coordinator wrote COMMIT before dying and told Client A that the transaction succeeded, aborting violates Consistency and Durability!
• Therefore, cohorts must hold their row locks and block all conflicting queries until the coordinator is repaired or rebooted.

---

4. XA Transactions in PostgreSQL and MySQL

In production SQL databases, 2PC is exposed via the Open Group XA Standard:

In PostgreSQL, the equivalent commands are PREPARE TRANSACTION 'tx_id' and COMMIT PREPARED 'tx_id'.

---

5. Code Deep-Dive: Two-Phase Commit Coordinator Engine

---

6. Production Failure Postmortem: The Zombie Lock Outage

Incident Overview:

In 2020, a global logistics company experienced a 4-hour system-wide database lockup when an application server running a 2PC coordinator was OOM-killed during a high-volume batch job.

What Happened:

1. The coordinator sent PREPARE TRANSACTION 'batch_9921' to 4 PostgreSQL shards.
2. All 4 shards acquired exclusive row locks on the central shipments table and entered the PREPARED state.
3. Before the coordinator could write the commit record and send COMMIT PREPARED, the Linux kernel OOM-killer terminated the coordinator container.
4. Because PostgreSQL's PREPARE TRANSACTION locks survive client disconnections, the row locks remained active.
5. Hundreds of incoming user queries queued up behind the locked rows, exhausting the PostgreSQL connection pools (max_connections = 500) within 45 seconds $⟹$ Complete System Freeze.

Remediation:

• Built an automated monitoring daemon that scans pg_prepared_xacts for any prepared transaction older than 60 seconds and alerts oncall.
• Migrated long-running inter-service workflows from 2PC to the Saga Pattern with asynchronous compensating transactions.

---

7. Chapter Summary & 2PC Production Rules