How Does MySQL InnoDB Ensure ACID Guarantees?

During a typical interview question about the four ACID properties, the article explains how MySQL’s InnoDB storage engine implements each guarantee.

Atomicity

Atomicity is ensured by the undo log , which records the inverse SQL statements needed to roll back a transaction. If an error occurs, MySQL uses the undo log to revert all changes, making the transaction appear never to have run.

Consistency

Consistency means the database state before and after a transaction remains valid. InnoDB relies on atomicity, isolation, and durability to maintain consistency. Unique‑key checks (e.g., primary‑key conflicts) and application‑level checks (e.g., insufficient funds in a transfer) illustrate this.

Isolation

Isolation is provided by Multi‑Version Concurrency Control (MVCC). Each row stores hidden columns trx_id and roll_pointer . When a transaction starts, a ReadView is created containing the IDs of active transactions. A transaction can read a row only if its trx_id is older than the ReadView (committed) or it follows the version chain via roll_pointer. MVCC works for READ COMMITTED and REPEATABLE READ; READ UNCOMMITTED reads the latest row, and SERIALIZABLE locks all rows, making MVCC irrelevant. MySQL defaults to REPEATABLE READ for better performance.

Durability

Durability is achieved by writing changes to both the in‑memory buffer and the redo log . On crash, the redo log is replayed to recover committed transactions. The commit sequence is: write redo log → prepare state → write binlog → write commit record to redo log → transaction becomes committed. The binlog, together with the redo log, ensures master‑slave replication and point‑in‑time recovery remain consistent.

The article ends with a reflective question: did MySQL design ACID first and then implement the mechanisms, or did the mechanisms lead to the definition of ACID?