SQLite writes are dropped with "database is locked": no busy timeout, and WAL is never turned on #198

New Issue

2026-10-04T20:24:55+02:00

clawbot commented

2026-10-04 20:24:55 +02:00

Found by the review of #195 (#195 (comment)). Checked against next at 66e71b4.

internal/database/database.go opens SQLite with sql.Open("sqlite", dbURL) and the default db_url is file:<state_dir>/state.sqlite3?_journal_mode=WAL. modernc.org/sqlite (v1.42.2) does not read _journal_mode, so the database is not in WAL mode, and nothing sets a busy timeout. A request makes several writes on separate pool connections (the request counters, storing the source and its metadata, the variant's accounting row), and the eviction pass writes in the background; when two overlap, one fails at once with "database is locked (SQLITE_BUSY)". These writes are best effort and only logged, so the request is still served, but the write is lost: for example a source file stays on disk without its source_content and source_metadata rows until a reconciliation pass repairs it. Under real traffic this happens routinely.

Plan:

Open the database the way modernc.org/sqlite documents: WAL journal mode and a busy timeout (a few seconds) set through the driver's own _pragma= parameters, in the default db_url and applied in internal/database so that a db_url given by the operator gets the busy timeout too. If one connection for writes is the plainer fix, say why in the PR.
Test first: concurrent writes like the ones a request and the eviction pass make all succeed (no SQLITE_BUSY), and the database reports WAL mode; the test fails on next.
README.md where it documents db_url says what pixa adds to it.

This lands before #195, whose integration test exposes it.

Model: opus-5-5

Found by the review of https://git.eeqj.de/sneak/pixa/pulls/195 (https://git.eeqj.de/sneak/pixa/pulls/195#issuecomment-125138). Checked against `next` at `66e71b4`. `internal/database/database.go` opens SQLite with `sql.Open("sqlite", dbURL)` and the default `db_url` is `file:<state_dir>/state.sqlite3?_journal_mode=WAL`. `modernc.org/sqlite` (v1.42.2) does not read `_journal_mode`, so the database is not in WAL mode, and nothing sets a busy timeout. A request makes several writes on separate pool connections (the request counters, storing the source and its metadata, the variant's accounting row), and the eviction pass writes in the background; when two overlap, one fails at once with "database is locked (SQLITE_BUSY)". These writes are best effort and only logged, so the request is still served, but the write is lost: for example a source file stays on disk without its `source_content` and `source_metadata` rows until a reconciliation pass repairs it. Under real traffic this happens routinely. Plan: - Open the database the way `modernc.org/sqlite` documents: WAL journal mode and a busy timeout (a few seconds) set through the driver's own `_pragma=` parameters, in the default `db_url` and applied in `internal/database` so that a `db_url` given by the operator gets the busy timeout too. If one connection for writes is the plainer fix, say why in the PR. - Test first: concurrent writes like the ones a request and the eviction pass make all succeed (no `SQLITE_BUSY`), and the database reports WAL mode; the test fails on `next`. - `README.md` where it documents `db_url` says what pixa adds to it. This lands before https://git.eeqj.de/sneak/pixa/pulls/195, whose integration test exposes it. Model: opus-5-5

clawbot referenced this issue from a commit

2026-10-04 20:32:45 +02:00

Wait on a busy SQLite database and turn on WAL mode (closes #198)

clawbot referenced this issue from a commit

2026-10-04 20:34:15 +02:00

Wait on a busy SQLite database and turn on WAL mode (closes #198)

clawbot referenced a pull request that will close this issue

2026-10-04 20:38:18 +02:00

Wait on a busy SQLite database and turn on WAL mode (closes #198) #200

clawbot commented

2026-10-04 20:38:26 +02:00

Fixed in #200: pixa adds a five-second busy timeout to every db_url, and the default db_url turns on WAL mode through the driver's _pragma parameter.

Model: opus-5-5

Fixed in https://git.eeqj.de/sneak/pixa/pulls/200: pixa adds a five-second busy timeout to every `db_url`, and the default `db_url` turns on WAL mode through the driver's `_pragma` parameter. Model: opus-5-5

clawbot closed this issue

2026-10-04 20:58:37 +02:00

clawbot referenced this issue from a commit

2026-10-04 20:58:38 +02:00

Wait on a busy SQLite database and turn on WAL mode (closes #198)

clawbot referenced this issue

2026-10-04 21:44:04 +02:00

Add an integration test of the image proxy flow (closes #80) #195

Sign in to join this conversation.