Transactions and ACID
What is a Transaction?
A transaction is a logical unit of work that contains one or more SQL statements. Its core promise is "All-or-Nothing": either everything succeeds, or the entire set of operations is undone (rolled back).
The Classic Example: A bank transfer. Withdrawing $100 from Person A and depositing $100 into Person B must happen as a single atomic unit. If the computer crashes halfway through, Person A should not lose their money without Person B receiving it.
The ACID Pillars
| Property | Meaning | How InnoDB Implements It |
|---|---|---|
| Atomicity | All operations succeed, or none do. | Undo Log (Reverse operations) |
| Consistency | Database moves from one valid state to another. | Combined effect of A, I, and D |
| Isolation | Concurrent transactions don't interfere. | MVCC + Locking |
| Durability | Once committed, changes are permanent. | Redo Log (Physically durable) |
Implementation Mechanics
Redo Log: Ensuring Durability
InnoDB uses a Write-Ahead Logging (WAL) strategy. When a transaction is committed, MySQL doesn't immediately update the giant data files on disk (which is a slow random I/O process). Instead, it writes the changes to the Redo Log (a fast, sequential I/O).
- Commit Success: As soon as the Redo Log is flushed to disk (
fsync), the transaction is considered "Durable." - Crash Recovery: If the power goes out, MySQL re-reads the Redo Log upon restart and "re-does" any changes that hadn't yet been synced to the primary data files.
Undo Log: Ensuring Atomicity
Every time you modify a row, InnoDB records the original state in the Undo Log.
- Rollback: If you hit an error or call
ROLLBACK, InnoDB uses the Undo Log to play the operations in reverse, restoring the original data. - Snapshot Isolation: Undo logs are also used by MVCC to allow other users to see the "old" version of a row while you are currently modifying it.
Architectural Insights
The "Doublewrite" Buffer
Redo logs protect against database crashes, but what if the OS crashes while writing a 16KB interior page to disk, resulting in a "partial page write"? Redo logs can only fix pages that are internally consistent. To solve this, InnoDB uses a Doublewrite Buffer: it writes a copy of the page to a safe temporary area first. If a partial write happens, InnoDB recovers the healthy page from this buffer before applying redo logs.
Logic vs. Physics
- Undo Log is a Logical Log: "I inserted a row, so to undo it, I must delete it."
- Redo Log is a Physical Log: "In Data Page 56, change 4 bytes at offset 120 to
0xFF." physical logs are much faster to process during a massive recovery event.