Understanding Database Transactions, ACID Properties, and Two‑Phase/Three‑Phase Commit Protocols

Data sharing and concurrent programming are common needs in computer applications; the transaction mechanism of databases helps backend developers write correct concurrent programs by relieving mental burden, thanks to the ACID properties (Atomicity, Consistency, Isolation, Durability).

While single‑node transactions involve elements on one machine, distributed transactions span multiple machines (e.g., transferring money between accounts stored on different MySQL instances). A distributed transaction is a global transaction composed of several local sub‑transactions, each preserving ACID, but guaranteeing ACID for the global transaction is non‑trivial.

Two‑Phase Commit (2PC) is a protocol that coordinates the commit of a distributed transaction using a coordinator and multiple participants. In the prepare phase , the coordinator logs the intent and sends a prepare T message; participants check if they can commit, log, and reply with ready T or abort T . In the commit phase , if all participants are ready, the coordinator logs and sends commit T , after which participants log and apply the changes.

The protocol’s outcome can be visualized as a voting process where each participant holds a veto power. If all votes are affirmative, the transaction commits; otherwise it aborts. After the final state is determined, further actions such as roll‑forward (for committed) or rollback (for aborted) are required, and locks must be released.

Fault tolerance is achieved by handling network partitions (retry, idempotence, timeout) and crashes of coordinators or participants (high availability via primary‑backup, Paxos or Raft groups). Recovery procedures involve scanning logs: depending on which logs exist (e.g., only a C record, or both C and A records), the system can decide to terminate, roll forward, or roll back the transaction.

When a transaction is in an in‑doubt state due to insufficient information, recovery may require consulting other participants or waiting for the coordinator to become available. The possible states are outstanding, committed, aborted, or complete, and the correct action depends on the logs observed.

Three‑Phase Commit (3PC) extends 2PC by adding a backup coordinator and replicating commit information to at least k coordinators, allowing the system to tolerate multiple coordinator failures.

Combining Replicated State Machines (RSM) with 2PC provides high availability: the coordinator’s log is replicated via a Paxos group, enabling automatic failover and leader election.

Finally, separating the transaction processing layer from the storage layer (e.g., Percolator over Bigtable) allows a stateless transaction service that leverages reliable distributed storage for high availability.