Relational engines write a description of every change into a sequential log before the modified data page itself reaches disk. What is that write-ahead logging protocol, and what problem does it solve?
answer
- log record durable before its data page
- commit = flush log, not flush pages
- sequential append beats random page writes
- data files may be stale; log is authoritative
- torn pages and lazy writeback are why
basics
~20 sThe log record describing a change must reach stable storage before the changed data page does, and before a commit is acknowledged. One cheap sequential flush makes a crash recoverable: replay the log to rebuild lost page writes.
solid answer
~50 sWrite-ahead logging (WAL) is the rule that **the log record describing a modification becomes durable before the data page it describes**, and that a transaction is acknowledged as committed only once its commit record is durable. It solves two problems at once. Correctness: a page write is not atomic (a crash mid-write can tear it), and the engine deliberately writes dirty pages lazily, long after commit. The log is the authoritative record, so recovery can replay it and reconstruct any page write lost in the crash. Performance: updating ten scattered rows means ten random page writes, but only one small sequential append to the log. Commit costs a sequential flush, not random I/O. The commit path is therefore: modify the page in the buffer pool, append log records, flush the log up to and including the commit record, then acknowledge. Dirty pages are written out later, at a checkpoint or under memory pressure.
code
text · 8 lines1. read page 42 into buffer pool
2. modify tuple on page 42 in memory (page now dirty)
3. append log records: <T1, page42, offset, before, after>
4. append <T1 COMMIT>
5. fsync log up to the commit record <-- durability point
6. acknowledge COMMIT to the client
...
7. (minutes later, at checkpoint) write page 42 to the data filego deeper
Be able to state the rule in one sentence — log first, then data — and that it is what makes crash recovery possible.
Explain both motivations (recoverability of lazily written pages, and sequential vs random I/O) and describe the ordering on the commit path.
Tie it to operational reality: commit latency equals log flush latency, checkpoints control how much log must be replayed, and the log doubles as the replication and point-in-time-recovery stream.
Frame WAL as the durability contract of the storage engine and reason about where that contract can be relaxed, what the loss window becomes, and how log volume drives storage, replication bandwidth and recovery-time objectives.
## The setup A relational engine stores rows in fixed-size pages (commonly 8 KB or 16 KB) and caches those pages in memory in the buffer pool. When a transaction updates a row, the engine modifies the cached page and marks it *dirty*. Two awkward facts follow. First, writing a page to disk is not atomic. Storage guarantees atomicity only at sector granularity, so a crash in the middle of an 8 KB page write can leave half old bytes and half new bytes — a *torn page* that is not a valid page at all. Second, the engine does not want to write dirty pages at commit time. Rows touched by one transaction are scattered across the file, so forcing them out at commit turns every commit into several random writes, and a hot page updated a thousand times would be written a thousand times. ## The rule Write-ahead logging resolves both: before a dirty page may be written to disk, the log records describing the changes on that page must already be on disk; and before a commit is reported to the client, the transaction's log records including its commit record must be on disk. The log is an append-only sequential file. A log record is small — typically the affected page id, the offset, and enough information to redo the change (and, in undo-logging designs, to reverse it), plus the transaction id and a sequence number. So the durable-write cost of a transaction becomes one sequential flush of a few hundred bytes rather than several random 8 KB writes. After a crash, the engine reads the log from the last checkpoint forward and re-applies changes that never made it into the data files, then reverses the effects of transactions that had no commit record. The data files may be arbitrarily stale or damaged in the last-written pages; the log is what makes them correctable. ## Why the ordering is the whole point If a dirty page reached disk *before* its log record, and the machine died at that instant, the data file would contain a change that recovery has no record of. If the change belonged to a transaction that never committed, nothing could undo it. That is the exact hole the protocol closes: the log is always at least as new as the data files, never behind them. Symmetrically, if commit were acknowledged before the commit record was durable, the client would be told the money moved and a crash a millisecond later would lose it — breaking durability, not just performance. ## What it buys and what it costs Buys: crash recovery without forcing pages at commit; sequential instead of random durable I/O; group commit, where many concurrent commits share one flush; a byte stream that doubles as the source for physical replication and point-in-time recovery, since the log describes every change in order. Costs: every change is written twice (once to the log, once eventually to the data file). Commit latency is bounded below by the storage device's flush latency. The log must be retained until the changes it describes are safely in the data files, which is why log volume and checkpoint frequency have to be managed. ## What WAL does not do by itself It does not make the *data files* consistent at all times — they are routinely stale, and are only meaningful together with the log. It does not provide isolation; concurrent transactions still need locking or MVCC. And it is not a substitute for backups: a lost log plus stale data files is a lost database. ## How to say it in an interview "Log the intent before you change the thing, and don't tell the client 'committed' until that log entry is durable. Then a crash is always fixable by replaying or reversing the log, and commit costs one sequential flush instead of scattered random page writes."
- If a data page were written to disk before its log record and the server crashed at that moment, what exactly goes wrong?The data file now contains a change that recovery cannot account for. If the change belonged to an uncommitted transaction, there is no durable undo information to reverse it, so the database is left with a modification no transaction ever committed. If the page was also torn, recovery cannot even tell what the page should look like. The WAL ordering rule exists precisely so the log is never behind the data files.
- Why is writing every change twice — once to the log and once to the data file — still faster than just writing the data pages at commit?The log write is a small sequential append that can be batched with other transactions' records into a single flush, whereas the data pages are scattered and each is a full page. Deferring the page writes also collapses many updates to the same hot page into one eventual write, and lets the engine schedule them in bulk at checkpoints. The extra bytes are cheap; the avoided random I/O and per-commit page forcing are expensive.
A restaurant writes every order on a numbered ticket roll before the kitchen starts cooking. The kitchen state can be lost at any moment, but the ticket roll can always be replayed to work out which meals were promised and which were never started.
saying these in an interview costs you the question
- Saying the data files on disk are always consistent and up to date
- Claiming commit flushes the modified data pages to disk
- Describing the log as an audit or history feature rather than a recovery mechanism
- Saying WAL provides isolation between concurrent transactions
- Thinking the log is optional because the filesystem journals writes anyway