Understanding Database Transactions: ACID, Distributed Transactions, and Common Solutions

Transactions provide a mechanism that groups all operations of a business activity into an indivisible unit, ensuring that either all operations succeed or none do (All‑or‑Nothing).

The four ACID properties of database transactions are:

Atomicity – a transaction is all‑or‑nothing.

Consistency – the database remains in a valid state before and after the transaction.

Isolation – concurrent transactions do not interfere with each other.

Durability – once committed, changes survive crashes.

In MySQL InnoDB, ACID is enforced by undo logs (for atomicity and consistency), redo logs (for durability), and row‑level locks (for isolation).

Distributed transactions arise when a single logical operation spans multiple services or databases located on different nodes. They are needed to maintain data consistency across micro‑services, sharded databases, or geographically separated data centers.

The CAP theorem (Consistency, Availability, Partition tolerance) explains why a distributed system cannot simultaneously guarantee all three properties; designers must choose between CP or AP configurations. BASE (Basically Available, Soft state, Eventually consistent) is an alternative model that relaxes strong consistency in favor of availability.

Common distributed‑transaction solutions include:

2PC (Two‑Phase Commit) : a coordinator asks participants to prepare and then to commit. It provides strong consistency but suffers from a single‑point‑of‑failure coordinator, blocking resources, and possible inconsistency under network partitions.

TCC (Try‑Confirm‑Cancel) : splits work into a try phase (resource reservation), a confirm phase (final execution), and a cancel phase (compensation). It avoids coordinator bottlenecks and supports idempotent compensation.

Local Message Table : tasks are recorded in a local table and later dispatched asynchronously, turning a large transaction into smaller, reliable ones; suitable for scenarios where eventual consistency is acceptable.

MQ Transaction (e.g., RocketMQ) : embeds the local‑message‑table idea inside a message broker, using a prepared‑message phase followed by local transaction execution and final commit.

Saga : decomposes a long transaction into a series of local transactions with compensating actions; it provides eventual consistency but cannot guarantee isolation.

The article concludes that distributed transactions should be used only when necessary, and the choice of solution depends on whether strong consistency or eventual consistency better fits the business requirements.