The database maintainer (internal/routewatch/dbmaintainer.go) runs Vacuum and Analyze (internal/database/database.go) without taking the database lock that every batch write holds (Database.lock). They meet the batch writes only at SQLite's own write lock, and whichever waits longer than the busy timeout (5 seconds) fails.
Seen on the live feed (825afa2, first hour):
Every incremental vacuum failed with "database is locked" after 5 seconds, once every 10 minutes, so the file never gives freed pages back, also after a peer's routes are deleted (565,000 routes in one delete).
The hourly ANALYZE took 11 seconds on a 3.5 GB file; meanwhile an AS batch write failed with "failed to begin transaction: database is locked" and its 2,576 updates were lost. A prefix batch failing the same way loses withdrawals, which leave routes in the table for good.
Severity: medium
Definition of done
Vacuum, Analyze and Checkpoint take the database lock the batch writes take, so maintenance and batch writes wait for each other instead of failing.
ANALYZE holds that lock briefly however large the file grows (for example PRAGMA analysis_limit), so batch writes are not held up for seconds every hour; the PR says how long it takes on a database of a few million routes.
Tests, failing before the fix: with a batch write holding the lock, a vacuum waits and then succeeds; with maintenance holding it, a batch write waits and then succeeds.
make check passes.
Model: opus-5-5
The database maintainer (`internal/routewatch/dbmaintainer.go`) runs `Vacuum` and `Analyze` (`internal/database/database.go`) without taking the database lock that every batch write holds (`Database.lock`). They meet the batch writes only at SQLite's own write lock, and whichever waits longer than the busy timeout (5 seconds) fails.
Seen on the live feed (`825afa2`, first hour):
- Every incremental vacuum failed with "database is locked" after 5 seconds, once every 10 minutes, so the file never gives freed pages back, also after a peer's routes are deleted (565,000 routes in one delete).
- The hourly ANALYZE took 11 seconds on a 3.5 GB file; meanwhile an AS batch write failed with "failed to begin transaction: database is locked" and its 2,576 updates were lost. A prefix batch failing the same way loses withdrawals, which leave routes in the table for good.
Severity: medium
## Definition of done
- `Vacuum`, `Analyze` and `Checkpoint` take the database lock the batch writes take, so maintenance and batch writes wait for each other instead of failing.
- ANALYZE holds that lock briefly however large the file grows (for example `PRAGMA analysis_limit`), so batch writes are not held up for seconds every hour; the PR says how long it takes on a database of a few million routes.
- Tests, failing before the fix: with a batch write holding the lock, a vacuum waits and then succeeds; with maintenance holding it, a batch write waits and then succeeds.
- `make check` passes.
Model: opus-5-5
clawbot
self-assigned this 2026-10-19 12:50:45 +02:00
Built in #97: Vacuum, Analyze and Checkpoint take the database lock. Analyze runs ANALYZE through PRAGMA optimize, with analysis_limit, only on tables that need it, because the limit alone still counts every index entry; the PR gives the timings.
Model: opus-5-5
Built in https://git.eeqj.de/sneak/routewatch/pulls/97: `Vacuum`, `Analyze` and `Checkpoint` take the database lock. `Analyze` runs ANALYZE through `PRAGMA optimize`, with `analysis_limit`, only on tables that need it, because the limit alone still counts every index entry; the PR gives the timings.
Model: opus-5-5
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
The database maintainer (
internal/routewatch/dbmaintainer.go) runsVacuumandAnalyze(internal/database/database.go) without taking the database lock that every batch write holds (Database.lock). They meet the batch writes only at SQLite's own write lock, and whichever waits longer than the busy timeout (5 seconds) fails.Seen on the live feed (
825afa2, first hour):Severity: medium
Definition of done
Vacuum,AnalyzeandCheckpointtake the database lock the batch writes take, so maintenance and batch writes wait for each other instead of failing.PRAGMA analysis_limit), so batch writes are not held up for seconds every hour; the PR says how long it takes on a database of a few million routes.make checkpasses.Model: opus-5-5
Built in #97:
Vacuum,AnalyzeandCheckpointtake the database lock.Analyzeruns ANALYZE throughPRAGMA optimize, withanalysis_limit, only on tables that need it, because the limit alone still counts every index entry; the PR gives the timings.Model: opus-5-5
clawbot referenced this issue2026-10-19 23:38:04 +02:00