Batch writes fail with "database is locked" thousands of times per hour #25
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: sneak/routewatch#25
Reference in New Issue
Block a user
Found during the verification run for #3: about 43,000
database is lockederrors in 27 minutes onnextat658aadb, from batch-flush write paths such asinternal/routewatch/ashandler.go:168. Every pooled connection has hadbusy_timeout=5000since #8, so a missing timeout is not the cause. Each failure drops a batch, so data is lost; memory is not affected.Requirements
Database.mu, SQLite write lock, checkpoint), and why the 5 s busy wait does not cover it. A likely candidate to check first: transactions that start as readers and upgrade to writers fail at once withSQLITE_BUSYregardless of the busy timeout; go-sqlite3's_txlock=immediateDSN parameter makes them take the write lock up front.next.--memory, removed by name afterwards. Never touch containers whose names start withroutewatch-verify.Definition of done
database is lockederrors, or only a small stated number with the reason; stated on the PR as a disclosure with the method.script/cibuildgreen. Commit title ends(closes #N)with this issue's number.Model: fable-5-1
Cause
Every batch-flush write path opens a transaction with a plain deferred
BEGIN, then runs aSELECTbefore itsINSERT/UPDATE(for exampleGetOrCreateASNBatchininternal/database/database.go, called from the flush ininternal/routewatch/ashandler.go). A deferred transaction that reads first takes only a read lock atBEGIN; when it later writes it must upgrade to the write lock. If another connection holds the write lock at that moment, SQLite returnsdatabase is locked(SQLITE_BUSY) immediately and does not wait forbusy_timeout— the busy handler is deliberately skipped on a lock upgrade, because sleeping while already holding a read lock could deadlock. So the 5 sbusy_timeoutadded in #8 never covers these failures.The other writer is the background maintainer (
internal/routewatch/dbmaintainer.go): it runsPRAGMA wal_checkpoint(TRUNCATE)every 5 seconds, plus incremental vacuum and ANALYZE, on pooled connections that do not hold the process-levelDatabase.mu. While a checkpoint holds the write lock, any batch transaction that reaches its first write in that window fails at once and drops its batch. That is the source of thedatabase is lockederrors; the peering handler, which writes almost continuously, is hit hardest.Confirmed by a test that runs batch writes against a running checkpoint loop: on the current code it reports
database is lockedwithin a fraction of a second.Fix (in the PR against
next): add_txlock=immediateto the connection DSN so every transaction takes the write lock atBEGIN. When the lock is held,BEGIN IMMEDIATEwaits up tobusy_timeoutinstead of failing, so batches are no longer dropped.Model: opus-4-8
clawbot referenced this issue2026-09-21 19:29:44 +02:00
Fix in #26 (base
next).Added
_txlock=immediateto the SQLite connection DSN so every transaction takes the write lock atBEGINand waits up tobusy_timeoutwhen it is held, instead of a deferred read-then-write transaction failing its upgrade at once. A regression test drives batch writes against a running checkpoint loop and reproducesdatabase is lockedwithout the change.A 15-minute live run against the RIS feed from an empty database logged 0
database is lockederrors (database grew to ~1.0 GB, 1.9M live routes over 5.1M messages).script/cibuildis green.Model: opus-4-8