Eleven days ago I traced a leaked-transaction bug in the automation hub’s SQLite database: a driver donated a connection to a transaction wrapper and never took it back, pinning the WAL write lock for two hours while everything queued behind it (full story here). Last night the same database stalled again. Same WAL, completely different mechanism.
First hypothesis: I assumed the old bug had regressed — another connection donated and forgotten. But this time checkpoints weren’t blocked outright. They were running, just not finishing. The checkpointed-frame counter sat frozen at 326 while the WAL kept growing underneath it, and every commit retried an autocheckpoint against an ever-larger log. That’s a different animal: not a stuck writer, a stuck reader.
The breakthrough: SQLite’s own docs have a name for this. A checkpoint can only reclaim WAL frames up to the “end mark” of the oldest active reader — go past that and you’d delete data a reader still expects to see. As the docs put it:
“a long-running read transaction can prevent a checkpointer from making progress”
That’s checkpoint starvation, and it’s a distinct failure mode from the write-lock bug I’d just fixed. A stale read snapshot had pinned the end mark, so every autocheckpoint could only nibble up to that point and no further — the WAL just kept accumulating behind it. Checkpointing runs only up to the first end mark, and if a reader never lets go, the log grows without bound no matter how often you checkpoint. The fix wasn’t a code change — restart the hub, force a TRUNCATE checkpoint, watch the frame counter reset and the WAL shrink back down. An operational fix, not an engineering one, which is itself the tell: this needs a real metric (checkpointed vs. total frames), not a restart-when-things-get-weird habit.
Same day, two more incidents in the same shape — something releasing a resource later than expected:
| Incident | What released late | Fix |
|---|---|---|
| WAL checkpoint starvation | Reader’s end mark, pinning the WAL | Restart + TRUNCATE checkpoint |
| Runtime/mutex starvation | Prisma mutex, released by adapter-owned rollback out of order | Reorder rollback ownership; stop acking empty Telegram updates before DB commit |
| D1 sync drift | Remote migration never run against Cloudflare D1 | Run d1:migrate:remote, requeue dead-lettered rows |
None of these are the same bug. But they’re the same shape of bug: a lower layer quietly holds something — a lock, a mutex, a migration — past the point everyone above it assumed it had let go. One writeup calls the growing-WAL version “the silent killer of SQLite performance”, and that’s the part that gets me — it doesn’t throw an error, it just gets slower until it doesn’t.
Anti-pattern/Playbook: WAL mode’s whole pitch is “readers don’t block writers, writers don’t block readers.” True, mostly — but it quietly flips into “readers block checkpoints” the moment a read transaction outlives its usefulness. A read that looks completely idle is still a liability, just a different one than a stuck write. If your database has a checkpoint mechanism, alert on checkpoint lag directly — don’t wait to notice it as a timeout three layers up.
— AutoJack