Build a B-tree storage engine (SQLite-style) (11 scenes)
Scene 08

WAL — durability without rewriting the file every commit

Commits append a page-image frame to the WAL and fsync it; the main DB file is left alone until checkpoint, so commits are sequential appends and readers don't block writers.

Previously

The page cache made reads RAM-fast and let writes happen in cache. But a crash erases RAM — the engine still owes you a story for how a commit becomes durable without rewriting random pages of the main file every time.

Scene 08

WAL — durability without rewriting the file every commit

  1. Watch
  2. Try it
  3. Predict
  4. Capture
users.db · (lags behind WAL)123456789101112131415161718192021222324users.db-wal — append-only, 0 framesempty WALusers.db-shm — page → latest WAL frameno shm entriessynchronous mode (when fsync runs)OFFNORMALFULLEXTRAcommit latency~1–10 msloss window on crash0(per-commit fsync)Rollback-journal default: every commit fsynced.Idle. INSERT INTO users(42,'Alice') is queued — press play to commit.
What to watch for

Watch one COMMIT walk the four steps. Mutate the page in cache, append a frame to the WAL, fsync the WAL, then return. The DB file is not touched.

Continue unlocks when the animation finishes.
Implementation

Highlighted lines are the ones running in the diagram right now.

Pager.commitUnderWAL
the four-step commit — what the diagram is animating
def commit(txn):
    for page in txn.dirty_pages:        # step 1: in cache
        wal.append(Frame(page.id, page.bytes))
    wal.append(Frame(commit=True))      # commit marker
    if synchronous in (FULL, EXTRA):
        fsync(wal)                       # step 3: durable
    shm.update_index(txn.dirty_pages)   # readers see latest
    return OK                            # step 4
    # NOTE: db file is NOT touched here
Reader.readPage
why a reader doesn't block on the writer
def read_page(page_id, snapshot_end_mark):
    # check shm for latest frame ≤ end_mark
    frame = shm.lookup(page_id, snapshot_end_mark)
    if frame is not None:
        return wal.read(frame)           # served from WAL
    return db_file.read(page_id)         # fall back to main file
Pager.commitUnderRollbackJournal
the older default — for contrast (one sentence in lessons)
def commit_rollback(txn):
    journal.write(originals(txn.dirty_pages))
    fsync(journal)                       # 1st fsync
    db_file.write(txn.dirty_pages)       # rewrite the FILE
    fsync(db_file)                       # 2nd fsync
    delete(journal)                      # commit point
    # readers were blocked under EXCLUSIVE this whole time
Sources

Where this sits in Build a B-tree storage engine (SQLite-style)

Scene 08 of 11, in the Speed & durability act — Page cache, WAL+fsync, and the checkpoint that keeps WAL bounded.. Commits append page-images to the WAL and fsync; the main DB file is left alone until checkpoint — fast, and readers don't block.

Up next. The WAL grows by one frame on every commit. Eventually that has to be cleaned up — that's checkpointing — and there is one canonical way it goes wrong: a long-lived reader pins the WAL open and it grows without bound.

All 11 scenes in Build a B-tree storage engine (SQLite-style) · Every curriculum

Built with Arqly
Every scene in Build a B-tree storage engine (SQLite-style) builds on the one before it.All 11 Build a B-tree storage engine (SQLite-style) scenes