Lesson 0007 · Internals & Performance
Why a COMMIT survives a power cut even though your changed rows are still sitting in memory — the write-ahead log, and the undo log that also powers rollback and MVCC.
You've covered how MySQL reads fast (0001–0004) and stays correct under concurrency
(0005–0006). The last core pillar is the D in ACID — durability: once
COMMIT returns, that change must survive a crash. The puzzle: InnoDB doesn't write
your changed data pages to disk at commit time. So how does a committed change come back after a
power cut? The answer is two logs.
Before a change is applied to the data files, InnoDB first records it in the
redo log — a small, sequential append on disk — and only that
log write must be durable at COMMIT. The actual data pages are updated in the
in-memory buffer pool and flushed to disk later. This is
write-ahead logging: log first, pages later. A sequential log append is far
cheaper than scattering random page writes across the disk, which is why COMMIT is
fast and durable at the same time.
| Log | Answers the question | Used for |
|---|---|---|
| redo log | "How do I re-apply a committed change I hadn't flushed yet?" | Crash recovery (roll forward) |
| undo log | "How do I reverse a change?" | ROLLBACK & MVCC (roll back) |
The redo log is a disk structure used during crash recovery to fix data from incomplete work: modifications that didn't finish reaching the data files before an unexpected shutdown are replayed automatically on startup, before connections are accepted. — MySQL Manual: "The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions… replayed automatically during initialization."
An undo log record holds how to undo the latest change a transaction made to a clustered-index
record — that's what ROLLBACK uses. But it does double duty: it's
also where MVCC gets earlier row versions for the consistent snapshot reads you
met in Lesson 0005.
— MySQL Manual: "An undo log record contains information about how to undo the latest change by a transaction… If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records."
redo = repeat committed work you might lose on a crash; undo = take back uncommitted work (and remember what rows used to look like for MVCC). Redo protects the committed; undo protects the not-yet-committed and the readers.
innodb_flush_log_at_trx_commitHow hard the redo log is pushed to disk at each commit is tunable — the classic durability-vs-throughput trade-off:
| Value | At each COMMIT | Crash risk |
|---|---|---|
| 1 (default) | Redo written and flushed (fsync) to disk. | None — full ACID. |
| 2 | Redo written to OS cache; fsync'd ~once/second. | Lose ≤ ~1s only on OS/power crash (a mysqld-only crash is safe). |
| 0 | Redo written + fsync'd ~once/second, not at commit. | Lose ≤ ~1s even on a mysqld crash. |
The difference between 1 and 2 is who has to survive.
Value 2 hands the redo to the OS at commit, so if only MySQL
crashes (process dies) nothing is lost — the OS still flushes it. You only lose the last ≤1s if
the whole machine loses power. That makes 2 a popular "fast but
mostly safe" choice; 1 is the only fully-ACID setting.
Answer from memory — effortful recall is what builds retention. Feedback is immediate.
The redo log is used primarily for:
Besides ROLLBACK, the undo log also powers:
Write-ahead logging means a change is durable in the:
With innodb_flush_log_at_trx_commit = 1, the redo log is flushed:
Setting the dial to 2 risks losing a committed transaction on:
COMMIT is fast despite unflushed data pages because it does a:
A modified page still in the buffer pool, not yet flushed, is a:
You can't easily crash MySQL safely, but you can watch the logs work.
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- 1 = full ACIDSHOW ENGINE INNODB STATUS\G
-- In the LOG section: "Log sequence number" (LSN) climbs as you write;
-- "Log flushed up to" and "Last checkpoint at" trail behind it.
-- Do some writes, then re-run and watch the LSN move:
UPDATE people SET city = CONCAT(city, '!') WHERE id = 1;
SHOW ENGINE INNODB STATUS\G -- LSN is now higherSTART TRANSACTION;
UPDATE people SET city = 'GONE' WHERE id = 1;
SELECT city FROM people WHERE id = 1; -- 'GONE' (your own change)
ROLLBACK;
SELECT city FROM people WHERE id = 1; -- restored — undo log reversed itThe LSN (log sequence number) is the redo log's ever-growing byte counter —
every change advances it, and "flushed up to" shows how much is safely on disk. The
ROLLBACK silently restores the old value straight from the undo log — the same
machinery that would have served that old value to a concurrent snapshot read (0005). Bring me a
SHOW ENGINE INNODB STATUS LOG section and we'll read the LSN/checkpoint story.
MySQL 8.0 Reference Manual — Redo Log
and Undo Logs, then
Optimizing InnoDB Transaction Management
for the innodb_flush_log_at_trx_commit trade-off in context.