SQLite busy_timeout is not what you think

I had a small service that writes to SQLite from three worker processes. Every few hours one of them would die with SQLITE_BUSY, despite a five-second busy_timeout. Here is the gotcha.

busy_timeout only helps while a lock is held. If another connection is stuck inside a long transaction — say, a giant delete triggering a WAL checkpoint — your writer can still get refused, because the checkpoint itself holds things up in a way the busy handler does not retry the way you expect.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 256; -- keep checkpoints small and boring

After capping wal_autocheckpoint and moving the nightly vacuum into a quiet window, the errors went away entirely. Boring is good.

Tags: sqlite, ops