SQL Server: Write-Ahead Logging & Checkpoint
How a change becomes durable
UPDATE
dbo.Users
SET
Reputation += 10
WHERE
Id = 26837;
Buffer pool (memory)
Page 1:842
clean
dirty
Id 26837 · Reputation
4712
4722
Id 26838 · Reputation
301
Data file (disk)
Page 1:842
on disk
Id 26837 · Reputation
4712
4722
Id 26838 · Reputation
301
unchanged — still the old value on disk
Transaction log (disk)
Append-only, written sequentially
LSN 0x2A:00F1 · earlier txn · committed
LSN 0x2A:00F3 · earlier txn · committed
LSN 0x2A:00F4
UPDATE · Page 1:842
Id 26837 · Reputation 4712 → 4722
written to disk
Write-ahead: the log reaches disk BEFORE the data file does.
COMMIT ✓
Server crash · memory lost
REDO from log
CHECKPOINT
flush dirty page