Two-Phase Commit (2PC) & Its Limits

When a business transaction spans multiple independent, heterogeneous databases (e.g., deducting inventory in PostgreSQL, debiting a card in MySQL, and issuing loyalty points in Oracle), standard single-database ACID commits are impossible.

Two-Phase Commit (2PC) (standardized in the XA specification) is a distributed algorithm that guarantees Atomicity across multiple nodes: either all participating databases commit the transaction, or all databases abort and roll back.

The 2PC Protocol Execution:

• Phase 1: Prepare (Voting Phase):
  1. The Transaction Coordinator assigns a global transaction ID and sends a PREPARE message to all participating databases (cohorts).
  2. Each cohort executes the SQL query up to the commit point, writes the changes to its local Write-Ahead Log (WAL), locks the affected rows with exclusive locks, and responds with either VOTE_COMMIT (if prepared safely) or VOTE_ABORT (if constraints failed or deadlock occurred).
• Phase 2: Commit / Abort (Decision Phase):
  1. Unanimous Consent: If every cohort voted VOTE_COMMIT, the coordinator writes GLOBAL_COMMIT to its local persistent log and broadcasts GLOBAL_COMMIT to all cohorts. Cohorts commit the changes, release their row locks, and send an ACK.
  2. Single Veto / Timeout: If any cohort votes VOTE_ABORT or fails to respond within a timeout, the coordinator logs GLOBAL_ABORT and instructs all cohorts to roll back their prepared transactions and release locks.