Purpose of the Write-Ahead Log

WHAT THIS TESTS The interviewer verifies you understand the write-ahead rule and how it delivers the D in ACID cheaply.

A GOOD ANSWER COVERS The Write-Ahead Log is an append-only, sequential file recording every change before it is applied to the data files. The cardinal rule: the log record describing a change must be flushed durably to disk before the corresponding data page can be considered durable, and before a transaction is reported committed. At commit time the database fsyncs the relevant WAL records, which is fast because it is one sequential write, then it can acknowledge the commit. The actual data pages may remain dirty in the buffer pool and get written to their scattered locations later, lazily, by the background writer and checkpoints. This decouples commit latency, a sequential log append, from the slow random I/O of updating data pages. Durability survives a crash because, on restart, the engine reads the WAL and replays every committed change that had not yet reached the data files, reconstructing them; changes from uncommitted transactions are undone or never replayed.

COMMON WRONG ANSWERS Saying commit must write the data pages first; that is exactly what WAL avoids. Confusing the WAL with the data files. Thinking the log is random-access; it is sequential, which is why it is fast. Forgetting the fsync that makes it durable.

LIKELY FOLLOW-UPS Why is sequential log writing fast; what is a checkpoint; what is the difference between commit durability and page persistence; how does replay stay correct.

ONE CONCRETE EXAMPLE A transfer debits one account and credits another, then commits. The database appends two change records to the WAL and fsyncs them, then returns success, while the modified pages sit dirty in memory. The server loses power a second later. On restart, the engine finds those committed WAL records, replays them onto the data files, and the transfer is intact even though the data pages were never flushed before the crash.