Unlocking MySQL InnoDB: Deep Dive into ACID, Redo/Undo Logs, and Double Write

Modern interview questions often go beyond the simple request to "describe the ACID properties of MySQL InnoDB" and probe the underlying implementation details.

Atomicity

Atomicity means a transaction is an indivisible unit: it either fully succeeds or fully rolls back. In InnoDB this is realized through Redo Log (for committing changes) and Undo Log (for rolling back).

When a transaction commits, its modifications are first written to the Redo Log buffer and later flushed to disk. If a rollback is required, the Undo Log contains the inverse SQL statements that restore the data to its pre‑transaction state.

Redo Log and innodb_flush_log_at_trx_commit

The innodb_flush_log_at_trx_commit parameter controls how Redo Log is flushed:

0 : Redo Log stays in the log buffer; the master thread flushes it to disk roughly once per second.

1 (default): Redo Log is written to the OS buffer and flushed to disk at each transaction commit.

2 : Redo Log is written to the OS buffer at commit, and the OS flushes it to disk about once per second.

Choosing 0 or 2 can tolerate a MySQL crash (assuming the OS remains intact) without losing committed transaction data.

Undo Log

Before a transaction commits, InnoDB stores the SQL needed for rollback in the Undo Log, which is a logical log. For an INSERT, the corresponding Undo Log entry is a DELETE statement; for a DELETE, it is an INSERT; for an UPDATE, it is the inverse UPDATE. Undo Log is organized into rollback segments and undo segments, with each transaction system segment capable of holding 256 rollback segments, each of which can hold 1 024 undo records.

Durability

Durability is commonly described as the Write‑Ahead Logging (WAL) guarantee: all modifications must be recorded in the Redo Log before the data pages are written to the data files. In addition, InnoDB employs a Double Write mechanism to protect against partial page writes caused by system crashes.

InnoDB’s minimum I/O unit is a 16 KB page, while the OS page size is typically 4 KB. Writing a page therefore involves four OS pages, and the operation is not atomic. If a crash occurs during this write, the page can become corrupted. Double Write solves this by first writing the page to a 2 MB Double Write Buffer in memory, then flushing the buffer to a shared tablespace consisting of 128 pages. After the Redo Log is flushed, the master thread writes dirty pages from the buffer pool to the Double Write Buffer, and finally the Double Write Buffer writes the pages to the data files. If a crash happens after the Double Write Buffer is persisted but before the data file is fully written, recovery can copy the intact pages from the Double Write area back to the data file and then apply the Redo Log.

Isolation

InnoDB’s default isolation level is REPEATABLE READ , achieved through a combination of lock mechanisms and Multi‑Version Concurrency Control (MVCC). Locks prevent concurrent transactions from modifying the same resource (row, page, table, or even database). MVCC enables snapshot reads that see a consistent version of the data without waiting for locks.

Two read modes exist:

Snapshot read : a copy of the data is taken at the start of the transaction and used throughout; example SQL: SELECT * FROM table1; Current read : each read returns the latest committed data and acquires appropriate locks; example SQL: SELECT * FROM table1 FOR UPDATE; or SELECT * FROM table1 LOCK IN SHARE MODE; MVCC relies on hidden row fields ( DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID), the Undo Log, and a Read View that determines which version of a row is visible to the current transaction.

Consistency

Consistency guarantees that a transaction transforms the database from one valid state to another, preserving business rules (e.g., a transfer of 10 CNY must debit one account and credit another). This is enforced by the internal mechanisms described above rather than by binary logs, which are used for replication, not for transaction‑level consistency.

Conclusion

The interplay of Redo Log, Undo Log, Double Write, lock protocols, and MVCC ensures that MySQL InnoDB fulfills the ACID guarantees. Understanding these internals helps developers reason about performance trade‑offs, recovery scenarios, and the impact of configuration parameters such as innodb_flush_log_at_trx_commit.