Log Before Data
The write ahead log (WAL) enforces one rule: the log record describing a change must reach durable storage before the changed data page is written. This is what lets a database survive a crash without losing committed work.
Records and the LSN
Each change produces a log record stamped with a monotonically increasing log sequence number (LSN). Each page also stores the LSN of the last change applied to it.
- On commit, the log up to that transaction is flushed to disk.
- A page may stay dirty in memory long after commit because the log already protects it.
- The page LSN tells recovery whether a logged change is already present on the page.
The WAL Buffer
Log records first land in an in memory WAL buffer, then are written sequentially. Sequential log writes are far cheaper than scattered random data writes, so logging adds little overhead.
Key idea
The WAL writes a log record with an LSN before the data page, flushes the log on commit, and uses page LSNs so recovery knows which changes already reached the page.