Understanding Database Transactions: From ACID Basics to Distributed Protocols

Relational Database Transactions

Most developers first encounter transactions while learning relational databases such as MySQL, SQL Server, or Oracle. A transaction is a group of operations that must be executed as a single logical unit so that the system moves from one predictable, consistent state to another; either all operations succeed or none do, with any partial work rolled back.

ACID stands for Atomicity, Consistency, Isolation, Durability.

Atomicity : all operations succeed or none are applied.

Consistency : integrity constraints (primary keys, foreign keys, NOT NULL, UNIQUE, CHECK, etc.) are never violated.

Isolation : concurrent transactions do not see each other's intermediate states, preventing dirty reads; typical isolation levels are READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE.

Durability : once a transaction commits, its changes survive crashes and power loss.

UserA.account -= 100
UserB.account += 100

The example shows a simple money transfer where both debit and credit must succeed together; atomicity guarantees that either both balances are updated or neither is.

Consistency: A transaction is a correct transformation of the state. The actions taken as a group do not violate any of the integrity constraints associated with the state. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee that the transaction meets all application‑level expectations.

Distributed Transactions

When data volume exceeds the capacity of a single relational database, systems resort to vertical or horizontal sharding, NoSQL stores, or micro‑service architectures where each service may own its own (often heterogeneous) database. Traditional locking and logging mechanisms no longer suffice, and distributed transactions must coordinate across multiple nodes.

The core challenge is the CAP trade‑off: in the presence of network partitions, a system must choose between strong consistency and high availability.

Two‑Phase Commit (2PC)

2PC is a classic, strongly consistent, centralized protocol involving a coordinator and multiple participants.

Phase 1 (Prepare): the coordinator asks all participants if they can commit.

Phase 2 (Commit): if every participant votes “yes”, the coordinator instructs all to commit; otherwise the transaction is aborted.

Both the coordinator and participants rely on durable logs so that, after failures, the protocol can reach a consistent outcome.

Three‑Phase Commit (3PC)

3PC adds an extra “pre‑commit” phase and timeout mechanisms to avoid the blocking problem of 2PC, at the cost of additional messages and latency.

Try‑Confirm‑Cancel (TCC)

TCC splits a business operation into three stages: Try (reserve resources and perform checks), Confirm (execute the operation idempotently), and Cancel (release reserved resources idempotently). It provides strong consistency while improving scalability because the protocol lives at the business‑service layer.

Message‑Based Distributed Transactions

These approaches break a distributed transaction into a local transaction and one or more asynchronous messages.

Local Message Table

The local transaction writes both the business changes and a message record in the same database, guaranteeing atomicity via the database’s transaction log.

begin transaction;
update User set account = account - 100 where userId = 'A';
insert into message(userId, amount, status) values('A', 100, 1);
commit transaction;

Transactional Message

A message broker that supports transactional messages coordinates the local transaction and the message send in a two‑phase commit, ensuring that either both succeed or both fail.

One‑Phase Commit (1PC)

1PC is used in primary‑secondary replication sets where a write is considered committed as soon as the primary persists it; there is no explicit rollback step for nodes that fail, so temporary inconsistencies may appear but are eventually resolved by replaying logs.

Reflection and Summary

In many real‑world scenarios, guaranteeing only atomicity is sufficient; isolation and durability are handled by the local transaction engine, while distributed systems rely on eventual consistency, compensation logic, or idempotent retries. Understanding the trade‑offs among 2PC, TCC, and message‑based patterns helps architects choose the right consistency model for their applications.