LESSON 26.3
Write-ahead logs and MVCC
How databases survive crashes with a write-ahead log and checkpoints, how MVCC lets readers and writers avoid blocking each other, isolation levels, and the cleanup MVCC leaves behind.
7 min read
Two pieces of machinery sit under every transactional database: a log that makes commits durable without writing pages immediately, and a versioning scheme that lets many transactions read and write at once. Both explain behavior you'll see in production.
The write-ahead log
Updating a B-tree page means a random write somewhere on disk, and a single transaction can touch many pages. Flushing them all on every commit would be slow. Instead:
- Log first. The change is appended to the write-ahead log (WAL), a sequential file.
- Flush the log on commit. One sequential
fsyncmakes the commit durable. - Update pages in memory. Changed pages are marked dirty in the buffer pool and written to disk later, in batches.
- Checkpoint. Periodically, all dirty pages are flushed and the log position is recorded, so older log can be recycled.
- Recover. After a crash, replay the log from the last checkpoint to redo changes whose pages never reached disk.
- Random page write on SSD
- tens to hundreds of microseconds each
- Sequential log append
- one flush covers many commits (group commit)
- Checkpoint interval
- minutes - trades recovery time for write smoothness
The WAL does more than crash recovery. Replicas apply the same log to stay in sync, point-in-time recovery replays archived log onto a base backup, and change data capture reads it to stream changes out. It's the database's own event log.
MVCC
If writers overwrote rows in place, readers would need locks to avoid seeing half-finished changes. Multi-version concurrency control avoids that: an update writes a new version of the row, tagged with the transaction that created it, and leaves the old version for anyone who still needs it.
Each transaction reads from a snapshot: it sees versions committed before the snapshot and ignores newer ones. A long report sees a consistent table without blocking anyone, and writers proceed without waiting for it.
Isolation levels
MVCC makes snapshots cheap, and isolation levels decide how they're used:
Read committed
- Few conflicts
- Default in Postgres
- Each statement gets a new snapshot; lost updates possible
Pick it when: Most OLTP work with care around read-modify-write.
Repeatable read / snapshot
- One snapshot for the whole transaction
- Write skew still possible
Pick it when: Reports, multi-step reads that must agree.
Serializable
- As if transactions ran one at a time
- Aborts under conflict; must retry
Pick it when: Invariants across rows, like on-call schedules or balances.
Write skew is the classic snapshot anomaly: two doctors each check that someone else is on call, both see one other doctor, and both go off call. Each transaction's snapshot was valid; together they broke the rule. Serializable isolation, or an explicit lock on the rows being checked, prevents it.
The cleanup bill
Old versions pile up. Postgres removes them with vacuum; MySQL's InnoDB keeps old versions in undo logs and purges them. Either way, cleanup can only remove versions that no running transaction might still read.
Try it
Change Data Capture Pipeline HLD
Stream changes out of a database by reading its log.