How MySQL InnoDB Implements ACID: Locks, MVCC, and Logging

The article examines the implementation of ACID in MySQL's InnoDB engine, focusing on the underlying mechanisms rather than basic transaction theory.

ACID is introduced with four components, each highlighted using inline code tags:

（Atomicity）原子性 : a transaction is an indivisible unit that either fully succeeds or has no effect.

（Consistency）一致性 : data remains consistent before and after a transaction.

（Isolation）隔离性 : concurrent transactions do not interfere with each other.

（Durability）持久性 : once committed, changes survive crashes.

Isolation is achieved via four isolation levels, illustrated with diagrams (omitted here). The article explains that locks and MVCC (Multi‑Version Concurrency Control) are the core techniques.

Locks

InnoDB provides several lock types and granularities:

Granularity: table lock, page lock, row lock (InnoDB supports row‑level locks; MyISAM does not).

Row‑lock types: shared (read) lock and exclusive (write) lock, each preceded by intention locks.

Lock algorithms: Record Lock, Gap Lock, and Next‑Key Lock (record + gap).

Locks are held until the transaction ends, following a two‑phase locking protocol.

MVCC

InnoDB stores additional fields DATA_TRX_ID and DATA_ROLL_PTR in each row to support versioning. An undo log records the previous state of modified rows.

A ReadView is created at the start of each SELECT, containing:

trx_ids: IDs of active (uncommitted) transactions.

low_limit_id: current maximum transaction ID + 1.

up_limit_id: minimum active transaction ID.

creator_trx_id: ID of the transaction that created the view.

When a row is read, InnoDB compares its DATA_TRX_ID with the limits in the ReadView to decide visibility, possibly retrieving older versions from the undo log.

Phantom Reads and RR Isolation

Even with Repeatable Read (RR), phantom reads can occur for INSERT operations, while non‑repeatable reads affect UPDATE statements. The article shows an example where expected results differ due to phantom behavior.

Atomicity

Undo logs enable rollback: INSERTs are undone by DELETEs, DELETEs by INSERTs, and UPDATEs by inverse UPDATEs, ensuring the transaction can be fully reverted.

Durability

Durability relies on the redo log, which implements Write‑Ahead Logging (WAL). Changes are first written to the redo log, then flushed to disk on commit, guaranteeing recovery after a crash.

The redo log is a circular, crash‑safe log; the buffer pool caches data pages, improving I/O performance but requiring the redo log to persist changes before the buffer pool is flushed.

Binlog vs. Redo Log

Binlog (binary log) operates at the server level and records logical statements, while redo log is engine‑level and records physical page modifications. Redo log is written before binlog to avoid inconsistencies during recovery.

Consistency

Consistency is the ultimate goal of the other three properties; application logic must also enforce it (e.g., updating both balance and inventory in a purchase).

Summary : MySQL InnoDB achieves ACID through a combination of lock protocols, MVCC with undo/redo logs, buffer pool management, and careful ordering of redo and binlog writes, providing both data integrity and performance.