WAL: the log goes first, so a commit can be cheap and still survive

What it is for. Changes are made in memory, where they are fast; flushing data pages to disk is slow, and random (a page here, a page there). But the moment a transaction commits, it must be un-losable — power cut, process crash, anything. That promise is Durability, the D in ACID (Atomicity, Consistency, Isolation, Durability; adjectives atomic, consistent, isolated, durable). WAL — Write-Ahead Log — is how the promise is kept without paying a page-flush per commit.

The one rule. A data page may reach the disk late, but the log record describing a change must be durable before the transaction that made it is allowed to commit. "Ahead" is always relative to the data page, never to the change in memory.

Act one — the normal path. Walk one commit (the running example: accounts id=7, balance 100 to 80, transaction 742). (1) Every change first appends a log record to the WAL buffer — "transaction 742 changed this page thus" — each record carrying an increasing sequence number, the LSN; then the in-memory page is modified (mvcc versioning proceeds as usual). (2) At COMMIT, the WAL buffer is flushed and fsync'd; success is returned only after the fsync lands. At that instant the modified data page may still exist only in memory — and the commit is already fully valid, because the log can rebuild the page. (3) Afterwards, a checkpoint flushes dirty pages in the background and advances the line up to which log segments can be recycled. What does not happen on the normal path: no data-page flush at commit time, no waiting for checkpoints. Commit latency is roughly one sequential append plus one fsync. (Rollback is also normal traffic: an aborting transaction undoes its changes, and the undo itself is written to the log as compensation records — so a crash in the middle of an undo cannot corrupt the story.)

Act two — the crash. At the power cut, everything in memory dies: unflushed data pages and the WAL buffer's unsynced tail. What survives on disk: the WAL up to the last fsync, and data pages as of earlier checkpoints — possibly quite stale. Recovery runs three steps, in this order. (1) Locate: read the last checkpoint's position from the control file; only log after it can contain changes not yet in the data pages. (2) Redo: replay records in LSN order, regardless of whether their transactions committed, rebuilding the physical world exactly as it was at the crash instant. Replay is idempotent because every data page header stores a page LSN — the newest log record that page has already absorbed; a record is applied only if the page's LSN is behind it, otherwise skipped. Re-running recovery, even after a second crash mid-recovery, converges to the same state. (3) Undo: transactions that were uncommitted at the crash instant are rolled back from the log, their undo again logged as compensation records. Final state: every committed transaction present, no uncommitted one visible — as if the crash had been a pause.

The three-copies picture. One logical state in three tenses: the in-memory pages are the present, the data pages on disk are the past, and the WAL is the verb stream that pushes the past back to the present. As an equation: data pages + WAL recorded after the checkpoint ≡ the in-memory state. The three are not copies in content — they are one state kept equivalent by the rule.

Why the log is cheaper to write — it is on disk too. Three reasons. (1) Sequential vs random: the log is one file appended at the tail, the pen never lifts; data pages are scattered across the whole file and flushing them is random I/O — the most expensive kind, on spinning disks by seek time, on SSDs by write amplification. (2) Less data: a log record holds only the change — tens to hundreds of bytes; a page flush costs the full 8KB or 16KB even when one byte changed. Ten scattered pages touched by one transaction: ten random full-page writes, versus one small contiguous append. (3) One stream, shareable: a commit needs exactly one fsync of one stream, and queued transactions can share a single fsync (group commit). WAL does not avoid the disk; it reshapes the unavoidable write into the disk's favorite form — sequential, incremental, once. (The checkpoint then does the merging work in the background: a page changed ten times is flushed once, in its final form.)

Cousins, not twins — WAL vs MVCC versions. Both are append-only records of change, and the resemblance is real (InnoDB's undo log even doubles as the old-version store). They differ in job (versions separate live readers from writers; the WAL resurrects state after death), in shape (versions are scattered per row with no global order; the WAL is one globally ordered stream), and decisively in disk discipline: a version may exist only in memory and die in the crash — the WAL only counts once it has been forced to disk ahead of the commit. Durability comes from that forcing, not from the stacking.