How MySQL InnoDB Guarantees ACID: Locks, MVCC, and Log Mechanics

Preface

This article explores how the MySQL InnoDB engine implements ACID, without delving deeply into basic concepts such as what a transaction is or the meaning of isolation levels.

ACID

MySQL, as a relational database, ensures ACID properties primarily through the InnoDB engine.

Atomicity : A transaction is the smallest unit of execution and cannot be divided; it either completes fully or has no effect.

Consistency : Data remains consistent before and after a transaction.

Isolation : Concurrent transactions do not interfere with each other.

Durability : Once a transaction is committed, its changes persist even after a crash.

Isolation

There are four isolation levels:

Isolation Level | Description
----------------|------------
Read Uncommitted | Uncommitted changes are visible to other transactions.
Read Committed   | Changes become visible only after the transaction commits.
Repeatable Read   | The same query returns identical results within a transaction, regardless of other concurrent modifications. (InnoDB default)
Serializable      | Transactions are executed serially; each read acquires a table‑level shared lock, blocking reads and writes, providing the highest isolation at the cost of concurrency.

Different isolation levels address issues such as dirty reads, non‑repeatable reads, and phantom reads.

Isolation Level | Dirty Read | Non‑repeatable Read | Phantom Read
----------------|-----------|---------------------|-------------
Read Uncommitted | Possible  | Possible            | Possible
Read Committed   | Not allowed| Possible            | Possible
Repeatable Read  | Not allowed| Not allowed         | Possible
Serializable     | Not allowed| Not allowed         | Not allowed

Isolation is achieved through locks and MVCC .

Locks

MySQL provides various lock types.

Granularity

Locks can be at the table, page, or row level. Table locks include intention shared, intention exclusive, and auto‑increment locks. Row locks are implemented by the storage engine; not all engines (e.g., MyISAM) support them.

Row Lock Types

In InnoDB, row locks are applied to index entries. Row‑level locking includes:

Shared lock (read lock): other transactions can acquire shared locks but not exclusive locks. Example: SELECT ... LOCK IN SHARE MODE Exclusive lock (write lock): other transactions cannot acquire shared or exclusive locks. Example: INSERT, UPDATE, DELETE, FOR UPDATE Row locks are acquired only when needed and are released at transaction end (two‑phase locking).

Row‑Lock Algorithms

Record Lock

Locks a single row record, always locking the corresponding index entry.

Gap Lock

Locks the gap between rows to prevent phantom reads during inserts.

Next‑Key Lock

Combines gap lock and record lock (left‑open, right‑closed).

Locks and Isolation

Locks prevent concurrent writes, ensuring isolation. However, reads can still occur under a write lock because shared locks are allowed.

MVCC

Multi‑Version Concurrency Control (MVCC) allows a transaction to read a consistent snapshot even if other transactions have modified the rows. InnoDB stores extra fields DATA_TRX_ID and DATA_ROLL_PTR in each row.

DATA_TRX_ID : The ID of the transaction that last modified the row.

DATA_ROLL_PTR : Pointer to the undo log chain for previous versions of the row.

Undo log: records the state of data before modification; used for rollback.

A read view is created at the start of each SELECT and contains:

trx_ids : Set of active (uncommitted) transaction IDs.

low_limit_id : Current maximum transaction ID + 1 at view creation.

up_limit_id : Minimum active transaction ID at view creation.

creator_trx_id : Transaction ID that created the read view.

When a row is read, InnoDB evaluates:

- If DATA_TRX_ID < up_limit_id → the row existed before the transaction and is visible.
- If DATA_TRX_ID >= low_limit_id → the row was created after the read view and is invisible.
- If invisible, InnoDB follows DATA_ROLL_PTR to the undo log to find an older version.
- Whether the row is visible also depends on the isolation level.

RR Level Phantom Reads

Even with Repeatable Read, phantom reads can occur for INSERT operations, while non‑repeatable reads affect UPDATE operations.

Transaction 1                | Transaction 2
----------------------------|----------------------------
begin                       | begin
select * from dept;         |
-                           | insert into dept(name) values('A');
-                           | commit
update dept set name='B';    |
commit                      |

Expected result:

<table><tr><td>id</td><td>name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr></table>

Actual result:

<table><tr><td>id</td><td>name</td></tr><tr><td>1</td><td>B</td></tr><tr><td>2</td><td>B</td></tr></table>

Thus, Repeatable Read does not fully eliminate phantom reads for modifications; MVCC’s handling of phantom reads is not complete.

Atomicity

Atomicity relies on undo logs. When a transaction rolls back, InnoDB uses the undo log to reverse each operation:

INSERT → DELETE

DELETE → INSERT

UPDATE → opposite UPDATE restoring previous values

For an UPDATE, the undo log records the primary key, changed columns, and before/after values, enabling precise rollback.

Durability

Durability is achieved through the redo log (Write‑Ahead Logging). Changes are first written to the redo log before being applied to the data pages.

How a SQL UPDATE Executes

When a transaction commits, the redo log is flushed to disk (fsync), ensuring that even if MySQL crashes, the redo log can be replayed to recover the database.

Redo Log

The redo log is a circular, fixed‑size log that is crash‑safe. It has two phases: prepare and commit.

Fixed size, written sequentially.

Crash‑safe.

Buffer Pool

The Buffer Pool caches data pages in memory:

Read requests first check the Buffer Pool; if missing, the page is loaded from disk.

Write requests modify the Buffer Pool, and dirty pages are periodically flushed to disk.

If MySQL crashes before dirty pages are flushed, data loss can occur, which is why redo logs are essential.

Binlog

The binary log (binlog) is a logical, statement‑level log at the server layer, distinct from the physical redo log.

Redo log: engine‑specific, records physical changes to pages.

Binlog: server‑level, records logical statements.

Redo log is written continuously; binlog is appended at transaction commit.

Example statement: update T set c=c+1 where ID=2; The executor fetches the row with ID=2 from the engine.

The executor computes the new value and asks the engine to write it.

The engine updates the row in memory and records the change in the redo log (prepare state).

The executor writes the corresponding binlog entry to disk.

The executor commits the transaction; the engine marks the redo log entry as committed.

Redo log is written first because if the redo log were lost, the database could not be recovered correctly; writing binlog first could cause duplicate effects during recovery.

Consistency

Consistency is the ultimate goal of a transaction, ensured by atomicity, isolation, and durability at the database level and by correct business logic at the application level.

Summary

Understanding the purpose of undo logs, redo logs, and MVCC clarifies how MySQL implements ACID.

References

MVCC implementation principles

MySQL locking mechanisms

MySQL transaction ACID implementation

Deep dive into MySQL transactions