Write-ahead log recovery across a partial checkpoint
Replay committed assignments, reject an unfinished tail, and expose the ordering failures that lose atomicity when home data or log deletion happens too soon.

A committed transaction can survive even while the main database pages still contain old values. A write-ahead log preserves enough committed work to reconstruct the intended state later. The crucial question is not whether a log file exists, but which records were durably established before the crash and which recovery rule interprets them.
This lesson is for a developer who understands ordinary file writes and the difference between visibility and persistence. We use a small redo-only model to update two values together, interrupt it at several boundaries, and explain checkpoint ordering. Basic Python dictionaries are sufficient. Every crash boundary below is simulated state, not an actual device failure or a SQLite recovery experiment.
Give the log and the home pages different jobs
Suppose the home state contains x = 0 and y = 0. One transaction wants x = 10 and y = 20. Writing the two home values directly can leave the mixed state 10, 0 if only the first change survives. A final synchronization cannot help after a crash that occurred before it completed.
Our log records new assignments, followed by a commit marker. A recovery pass collects assignments until it encounters that marker, then applies the whole pending group to its reconstructed state. A trailing group without a commit marker is ignored. The model serializes transactions: there is one writer, no interleaving of transaction records, and no concurrent reader snapshot to preserve.
For this particular redo-only design, the complete transaction, including its commit marker, must be durable before any of its changes can enter the durable home state. This is a deliberately strict rule for this small model, not a claim that all storage engines forbid uncommitted page writes. Other recovery designs can allow them when they also maintain the information and protocol needed to undo unfinished work. This example has no undo log.
SQLite’s WAL overview describes appending changed content to a separate log and recording a commit there without first rewriting the original database file. Its actual WAL format stores revised database-page content in frames, with a commit indication in a frame header. Our readable set and commit tuples are teaching records, not SQLite’s binary format.
A commit marker that was merely appended to a process buffer is not the durable boundary. The model’s input named durable_records represents only records known to have survived, in order. It grants that knowledge as an assumption so we can study replay, rather than pretending a Python list can simulate filesystem synchronization.
Replay complete transactions and ignore the unfinished tail
The function copies the supplied home dictionary instead of mutating the caller’s object. Within a transaction, the most recent assignment to a key wins. At a commit marker, the pending changes update the reconstructed view; at the end of the log, any pending changes disappear. Keys absent from all committed assignments retain their home values.
def recover(home, durable_records):
"""Replay complete serialized transactions from trusted logical records."""
recovered = dict(home)
pending = {}
for record in durable_records:
if record[0] == "set":
_, key, value = record
pending[key] = value
elif record == ("commit",):
recovered.update(pending)
pending.clear()
else:
raise ValueError("unknown record")
# Any remaining changes belong to an uncommitted tail.
return recovered
initial = {"x": 0, "y": 0}
records = [
("set", "x", 10),
("set", "y", 20),
("commit",),
("set", "x", 99), # A later transaction has not committed.
]
cases = [
("before durable commit", initial, records[:2], {"x": 0, "y": 0}),
("commit durable; home old", initial, records[:3], {"x": 10, "y": 20}),
("one home value copied", {"x": 10, "y": 0}, records[:3], {"x": 10, "y": 20}),
("uncommitted log tail", {"x": 10, "y": 20}, records, {"x": 10, "y": 20}),
]
for label, home, durable, expected in cases:
result = recover(home, durable)
assert result == expected
assert recover(result, durable) == result
print(label, result)
Before a durable commit, the first two assignment records alone do not authorize either change. With the old home state, recovery returns 0, 0. Once the complete three-record transaction is durable, recovery returns 10, 20 even if neither home value was copied yet.
The third case has already persisted the new value of x, but not y. Replaying the durable transaction assigns x again and repairs y. The fourth case adds a later x = 99 record without another commit. Recovery ignores that unfinished transaction and preserves the earlier committed pair 10, 20.
These outcomes rely on valid input states. Every home change represented in the accepted cases was already protected by a complete durable transaction. The function cannot discover that an arbitrary home dictionary violates this rule, nor can it reconstruct an old value for which no old image was logged.
Make replay safe to repeat
Recovery can itself be interrupted. Our records contain absolute assignments such as “set x to 10,” so applying the same complete log again produces the same final values. The assertions verify this idempotence for each demonstrated state. Assigning x twice is harmless; adding ten twice would be a different operation.
A log of increments would need a different rule to avoid double application, such as durable progress information coordinated with page updates. Real systems use richer metadata and recovery protocols; the current model neither implements nor proves those mechanisms. Choosing assignment records is what makes this small redo example easy to inspect.
The idempotence also applies to a fixed home state and fixed log history, not to arbitrary concurrent editing. Replaying an old retained log over unrelated newer changes could overwrite them. The model assumes that recovery owns the state and replays the complete relevant committed suffix in order. It does not expose partially reconstructed values to concurrent application reads.
Keep the log until the checkpoint is durable
A checkpoint transfers committed log content into home storage so recovery need not retain the same history forever. In our no-reader model, the safe sequence is to preserve the durable log, copy the complete committed state into home storage, establish that home persistence, and only then discard the obsolete log. Copying bytes without completing the required persistence step is insufficient.
SQLite’s checkpoint discussion states the key ordering: synchronize the WAL before transferring content to the database, and synchronize the database before resetting the WAL. This explanation is about that ordering, not a complete recipe for safely deleting a live database’s log file.
Continue in the same Python session for two intentionally invalid states and a valid completed checkpoint:
# Deliberately invalid: home changed before the commit became durable.
mixed = {"x": 10, "y": 0}
assert recover(mixed, records[:2]) == mixed
assert mixed not in [initial, {"x": 10, "y": 20}]
# Deliberately invalid: discard the log before home is fully persisted.
assert recover(mixed, []) == mixed
# Valid completed checkpoint in this no-reader, single-writer model.
complete_home = {"x": 10, "y": 20}
assert recover(complete_home, []) == complete_home
# Redo uses assignments, including the latest assignment within a transaction.
repeated = [("set", "x", 5), ("set", "x", 7), ("commit",)]
assert recover(initial, repeated) == {"x": 7, "y": 0}
assert recover(recover(initial, repeated), repeated) == {"x": 7, "y": 0}
The first invalid case persists x too soon, before the commit is durable. Recovery ignores the incomplete transaction but cannot undo the already changed x, leaving neither the whole old state nor the whole new state. The second case discards the log while y is still old, destroying the information this model needs to finish the update.
In the valid case, both home values are already durable before the log becomes empty. Empty replay then returns the committed state. Actual log reuse also needs safeguards against confusing old bytes with current records. SQLite’s format uses salts and cumulative checksums to determine valid frames; our trusted tuples have no torn-record detection, byte framing, checksum or generation identifier.
Distinguish durable commit, response delivery and checkpoint completion
Under a promise of power-loss durability, a successful response must respect the required durable log boundary. It need not wait for every home page to be checkpointed. However, a crash after durable commit but before the client receives a response can leave the client uncertain: recovery includes the transaction even though the caller never observed success.
That uncertainty does not disappear because storage replay uses idempotent assignments. An application retry can represent a new business operation, especially for counters, payments or external messages. Request identity and retry semantics are a separate design problem; this example proves nothing about exactly-once external effects.
Durability settings also matter. The SQLite WAL discussion distinguishes synchronizing the WAL on each commit with synchronous=FULL from omitting that commit synchronization with NORMAL, under which recent commits may be lost after power failure or hard reset. “Committed” without the configuration and failure model is therefore too vague to infer a persistence promise.
Real readers complicate checkpoint progress. SQLite gives a reader an end mark in the log for its transaction, and a long-running reader can constrain how far a checkpoint proceeds. Our model deliberately has no such reader, so its ability to discard all committed history is not an instruction to truncate a live WAL manually. The WAL-file documentation treats that file as part of the database’s persistent state.
As an exercise, add two more serialized transactions, including repeated assignments to the same key. Test every prefix of the log against a reference that applies only transactions whose commit markers are present. Then interrupt home copying after each subset of a committed transaction’s keys and verify recovery still reaches the same final state. Keep the complete log until the final home-persistence boundary; removing it early should reproduce a specific failure, not silently pass the test.