Understanding MySQL Transactions: ACID, Isolation Levels, and Distributed Coordination

Why Transactions?

Transactions are crucial in order systems, banking, and many other scenarios where a series of operations must either all succeed or all fail, ensuring data consistency and preventing partial updates.

What Is a Transaction?

A transaction is a logical unit of work that is treated as an indivisible whole; it either commits entirely or rolls back completely.

ACID Properties

Atomicity : All operations succeed or none do.

Consistency : The database remains in a valid state after the transaction.

Isolation : Concurrent transactions do not interfere with each other, governed by isolation levels.

Durability : Once committed, changes survive crashes.

Isolation Levels

The isolation level defines how visible a transaction’s changes are to others. MySQL supports four levels:

READ UNCOMMITTED : Allows dirty reads; changes are visible before commit.

READ COMMITTED : Prevents dirty reads but allows non‑repeatable reads; this is the default for many databases.

REPEATABLE READ : Prevents non‑repeatable reads; MySQL’s default. Uses MVCC and gap locks to also avoid phantom reads.

SERIALIZABLE : Highest isolation; forces transactions to execute sequentially, incurring the greatest lock overhead.

Illustrations of how each level affects data visibility are shown in the images below.

MySQL Transaction Implementation

InnoDB is the default MySQL storage engine and supports transactions with MVCC, gap locks, and a default isolation level of REPEATABLE READ. Atomicity, consistency, and durability are enforced via transaction logs (redo and undo).

Redo and Undo Logs

When a transaction starts, changes are first written to the InnoDB log buffer. Before commit, the buffer is flushed to the redo log on disk (Write‑Ahead Logging). The redo log guarantees durability and consistency after a crash.

Undo logs record the previous state of each row, enabling rollback of a single transaction without affecting others.

Simple Transaction Example

The following sequence demonstrates a transaction that updates two rows, with both redo and undo entries:

start transaction;

-- record A=1 to undo log

update A = 3;

-- record A=3 to redo log

-- record B=2 to undo log

update B = 4;

-- record B=4 to redo log

flush redo log to disk;

commit;

If a crash occurs before step 8, no changes are persisted. If it occurs between steps 8 and 9, the redo log allows recovery and either rollback or completion of the commit.

Distributed Transactions

Distributed transactions can be built using InnoDB’s native support or via message queues for eventual consistency. The typical model involves three components:

Application (AP) – defines transaction boundaries.

Resource Manager (RM) – provides access to each resource, e.g., a database.

Transaction Manager (TM) – coordinates the global transaction.

Coordination follows a two‑phase commit (2PC): the first phase prepares all participants, and the second phase decides to commit or rollback. A failure in any participant forces a global rollback to preserve atomicity.

Conclusion

Any business scenario that requires ACID guarantees—such as order processing or banking—should use transactions. MySQL’s InnoDB engine provides robust support through MVCC, gap locks, and redo/undo logging, while distributed transactions rely on two‑phase commit to maintain atomicity across multiple resources.