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

Introduction

The article discusses the implementation of ACID in MySQL's InnoDB engine, focusing on how transactions achieve atomicity, consistency, isolation, and durability without delving into basic definitions.

ACID Components

Atomicity : A transaction is an indivisible unit; it 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 Levels

Four isolation levels are defined to address different concurrency problems:

Isolation Level

Description

Read Uncommitted

Uncommitted changes are visible to other transactions.

Read Committed

Changes become visible only after the transaction commits.

Repeatable Read

Read results are consistent within a transaction; this is InnoDB's default level.

Serializable

Transactions are executed serially, using table‑level shared locks; highest isolation but lowest concurrency.

These levels prevent dirty reads, non‑repeatable reads, and phantom reads as shown in the following matrix:

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

Read Uncommitted

Allowed

Allowed

Allowed

Read Committed

Disallowed

Allowed

Allowed

Repeatable Read

Disallowed

Disallowed

Allowed

Serializable

Disallowed

Disallowed

Disallowed

Locking Mechanism

InnoDB uses locks and MVCC to achieve isolation.

Lock Granularity

Locks exist at table, page, and row levels. Row locks are implemented via index entries; engines that do not support row locks (e.g., MyISAM) fall back to table locks.

Row Lock Types

Row locks are applied to index entries and come in two main forms:

Shared lock (read lock) – other transactions may acquire shared locks but not exclusive locks. Example: select ... lock in share mode

Exclusive lock (write lock) – no other transaction may acquire shared or exclusive locks. Example: insert, update, delete, for update

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

Lock Algorithms

Record Lock : Locks a single index record.

Gap Lock : Locks the gap between index records to prevent phantom reads.

Next‑Key Lock : Combination of record lock and gap lock (the default for repeatable read).

Multi‑Version Concurrency Control (MVCC)

MVCC allows a transaction to see a consistent snapshot of data even when other transactions modify rows.

Version Chain

Each row stores DATA_TRX_ID (the transaction that last modified the row) and DATA_ROLL_PTR (pointer to the undo log chain for previous versions).

Read View

When a SELECT starts, InnoDB creates a read view containing:

trx_ids : IDs of active (uncommitted) transactions.

low_limit_id : One greater than the maximum transaction ID at view creation.

up_limit_id : Minimum active transaction ID at view creation.

creator_trx_id : ID of the transaction that created the view.

Rows are displayed based on comparisons between DATA_TRX_ID and the view limits; if a row is invisible, its previous version is fetched via the undo log.

Example of Repeatable‑Read Phantom Issue

Even with repeatable read, phantom reads can still occur for INSERT operations because MVCC does not fully prevent them.

id  name
1   A
2   B

Actual result may show both rows as B , illustrating the limitation.

Atomicity and Undo Log

Undo logs record the original state of rows before modification. During a rollback, InnoDB uses the undo log to reverse inserts, deletes, and updates, ensuring atomicity.

Durability and Redo Log

Durability relies on the redo log (Write‑Ahead Logging). Changes are first written to the redo log, then flushed to disk on commit. The redo log is fixed‑size, circular, and crash‑safe.

Buffer Pool

The buffer pool caches data pages in memory. Reads first check the pool; writes modify the pool and are later flushed to disk. If a crash occurs before flushing, redo logs guarantee recovery.

Binlog vs. Redo Log

Binlog is a logical, statement‑level log at the server layer, while redo log is a physical, page‑level log specific to InnoDB. Redo log is written in a circular fashion and flushed frequently; binlog is appended and written at transaction commit.

Example update flow:

Engine fetches the target row.

Executor modifies the row in memory.

Engine writes the change to the redo log (prepare state).

Executor writes the corresponding statement to the binlog.

Engine commits the transaction, marking the redo log entry as committed and flushing it to disk.

Writing redo log first ensures that, in case of failure, the database can be recovered to a consistent state.

Consistency

All the mechanisms above aim to keep the database state consistent; application logic must also enforce consistency (e.g., updating both balance and inventory together).

Conclusion

The article summarizes how MySQL InnoDB implements ACID through undo/redo logs, MVCC, various lock types, and the buffer pool, providing a deeper understanding beyond the mere existence of these components.