How MySQL InnoDB Implements ACID: Deep Dive into Transactions, Locks, and Isolation

Overview of ACID in MySQL

MySQL follows the classic ACID model—Atomicity, Consistency, Isolation, Durability—to guarantee reliable transaction processing. The article reviews each property and then explains how the InnoDB storage engine implements them using internal logs, buffers, and concurrency control mechanisms.

ACID Properties Defined

Atomicity : A transaction is an indivisible unit; all its statements either commit together or roll back entirely.

Consistency : The database moves from one consistent state to another, ensuring no partial updates corrupt data.

Isolation : Changes made by a transaction are invisible to others until the transaction commits, preventing interference.

Durability : Once committed, changes survive crashes and power failures.

Implementation Details in InnoDB

1. Atomicity – Undo Log (undolog)

Each data‑modifying statement (INSERT, UPDATE, DELETE, REPLACE) generates an undo‑log record before the change is written to the data page. If a ROLLBACK occurs, InnoDB reads the undo log and performs the inverse operation (e.g., delete → insert, update → previous value).

INSERT is undone by DELETE. DELETE is undone by INSERT. UPDATE is undone by applying the previous version stored in the undo log.

2. Durability – Redo Log (redolog) and Buffer Pool

Modifications are first written to the buffer pool (an in‑memory cache of data pages). The redo log records the changes in a write‑ahead log (WAL) before they are flushed to disk. The flush behavior is controlled by innodb_flush_log_at_trx_commit:

0 – Log stays in the redo‑log buffer until the OS flushes it.

1 – Log is written and fsync ed to disk on every commit (full durability).

2 – Log is written to the buffer pool and flushed to disk asynchronously.

The redo log is circular; two pointers (write position and checkpoint) manage space reuse, ensuring crash‑safe recovery.

3. Isolation – Locks + MVCC

InnoDB combines traditional locking (table and row locks) with Multi‑Version Concurrency Control (MVCC) to provide four isolation levels that prevent dirty reads, non‑repeatable reads, and phantom reads.

Table Locks : Read lock (shared) allows concurrent reads but blocks writes; write lock (exclusive) blocks both reads and writes.

Row Locks : Acquired on demand, held until transaction commit, following a two‑phase locking protocol.

Gap Locks : Lock a range between index records to prevent phantom inserts (effective in REPEATABLE READ).

Next‑Key Locks : Combination of record lock and gap lock, covering the index record and the gap after it.

Lock modes used in SQL statements: SELECT ... FOR UPDATE – acquires exclusive (write) locks. SELECT ... LOCK IN SHARE MODE – acquires shared (read) locks.

MVCC implements versioning via a hidden rollback pointer in each row and a Read View that determines which row versions are visible to a transaction. In REPEATABLE READ, a Read View is created at transaction start; in READ COMMITTED, a new Read View is generated for each statement.

4. Consistency – Result of the Other Three

Consistency is the ultimate goal achieved by combining atomicity, durability, and isolation. By ensuring that each transaction is atomic, its effects are durable, and its intermediate states are isolated, the database maintains a consistent state across all operations.

Transaction Lifecycle Commands

A typical transaction follows these steps:

Start with BEGIN TRANSACTION (or START TRANSACTION).

Execute DML statements (INSERT, UPDATE, DELETE, etc.).

Either COMMIT to make changes permanent or ROLLBACK to revert using the undo log.

Conclusion

MySQL’s InnoDB engine implements ACID through a combination of undo logs for atomicity, redo logs and buffer pool for durability, lock mechanisms plus MVCC for isolation, and the overall coordination of these components to ensure consistency. Understanding these internals helps developers tune performance, choose appropriate isolation levels, and design robust applications that rely on reliable transaction processing.