How MySQL Enforces ACID Properties

This article explains how MySQL, primarily through its default InnoDB storage engine, ensures database transaction reliability by enforcing the ACID (Atomicity, Consistency, Isolation, Durability) properties. We will break down the specific internal mechanisms—such as the undo log, redo log, lock manager, and Multi-Version Concurrency Control (MVCC)—that MySQL uses to protect your data during concurrent operations and system failures.

Atomicity: The Undo Log

Atomicity guarantees that a transaction is treated as a single, indivisible unit of work. Either all operations within the transaction succeed, or none of them do.

MySQL enforces Atomicity using the Undo Log. Before any data modification occurs, InnoDB writes the reverse of the operation to the undo log. If a transaction is rolled back (either manually by the user or automatically due to an error), MySQL reads the undo log to revert the database to its original state. For example, if a transaction inserts a row, the undo log records a delete command to undo it if necessary.

Consistency: Rules, Constraints, and the Doublewrite Buffer

Consistency ensures that a transaction can only transition the database from one valid state to another, maintaining all schema rules, constraints, and internal integrity.

MySQL achieves Consistency through two levels of enforcement: * Declarative Constraints: MySQL strictly validates data types, triggers, and constraints such as UNIQUE, NOT NULL, and FOREIGN KEY during transaction execution. If any constraint is violated, the transaction is aborted. * The Doublewrite Buffer: To prevent data corruption caused by partial page writes (e.g., during a power outage), InnoDB writes pages to a contiguous layout called the doublewrite buffer before writing them to the actual data files. If a crash occurs mid-write, MySQL restores the original page from the doublewrite buffer.

Isolation: MVCC and Locking

Isolation ensures that concurrent transactions execute independently of one another without causing data inconsistencies. MySQL supports four isolation levels: Read Uncommitted, Read Committed, Repeatable Read (default), and Serializable.

MySQL enforces Isolation using two main technologies: * Multi-Version Concurrency Control (MVCC): For Read Committed and Repeatable Read levels, MVCC allows high concurrency by letting read operations access older versions of modified rows. Instead of locking a row during a read, MySQL reads a point-in-time snapshot of the data from the undo log. * Locking Mechanisms: To prevent conflicting updates, InnoDB employs row-level locks (Shared and Exclusive locks) and Gap locks. In the strict Serializable isolation level, MySQL uses these locks to prevent phantom reads, ensuring transactions execute as if they were running sequentially.

Durability: The Redo Log and Write-Ahead Logging

Durability guarantees that once a transaction commits, its changes survive even in the event of a system crash or power loss.

MySQL enforces Durability through Write-Ahead Logging (WAL) using the Redo Log. Writing data directly to the database files on disk is a slow, random I/O process. Instead, MySQL writes the transaction’s changes sequentially to the in-memory redo log buffer, which is flushed to the physical redo log files on disk when the transaction commits. If the database crashes, MySQL performs a recovery process upon reboot, reading the redo log to replay (roll forward) any committed transactions that had not yet been written to the data files.