How the write-ahead log ensures atomicity and durability

WHAT THIS TESTS The interviewer checks whether you understand the write-ahead logging protocol that underlies durability and atomicity, including recovery.

A GOOD ANSWER COVERS The write-ahead rule requires that the log record describing a change is flushed to durable storage before the corresponding data page is written. At commit time the engine forces the transaction's log records to disk and only then acknowledges the commit; it does not need to flush the actual modified data pages, which may still sit in the buffer cache. This makes commits durable and fast, since sequential log writes are cheaper than scattered page writes. Durability follows because the committed effect is safely on disk in the log even if the data pages are not yet written. Atomicity follows from recovery: after a crash the engine reads the log and performs redo, reapplying changes of committed transactions whose pages may have been lost, and undo, rolling back changes of transactions that were in flight at the crash, so no partial transaction survives.

COMMON WRONG ANSWERS Thinking a commit must flush every dirty data page; only the log must be durable. Treating the WAL as a backup or replication file rather than a recovery log. Forgetting the undo half, which provides atomicity for interrupted transactions. Ignoring checkpoints that bound how much log must be replayed.

LIKELY FOLLOW-UPS What is a checkpoint and why does it matter? How does fsync relate to durability? How do redo and undo differ? How does the WAL also enable replication and point-in-time recovery?

ONE CONCRETE EXAMPLE A transfer writes log records for the debit and credit, then commits by flushing the log. The server crashes before the data pages are written. On restart, recovery replays the redo records from the log, reapplying both the debit and credit, so the durable, atomic transfer is fully restored.