SQLite accessed via a fresh connection per call, no WAL mode — risk of 'database is locked' under concurrent access #4

New issue

Closed

opened 2026-07-28 21:01:11 +00:00 by paulloth1 · 1 comment

paulloth1 commented

2026-07-28 21:01:11 +00:00

(Migrated from github.com)

Problem

backend/db.py opens a brand-new aiosqlite.connect(DB_PATH) for every single function call (list_slides, get_slide, add_slide, ...), with SQLite's default rollback-journal mode (not WAL). No connection is shared/pooled.

Given the multi-process architecture (4 independent rotation_loop()s, see separate issue, each polling the DB every rotation cycle) plus concurrent admin writes, SQLite's default journal mode serializes writers and can raise database is locked under contention — especially noticeable if the interval is short or many admin edits/view-now clicks happen close together.

Suggested fix

Enable WAL mode (PRAGMA journal_mode=WAL;) once at startup, which allows concurrent readers alongside a writer and greatly reduces lock contention.
Consider a single shared aiosqlite connection (or a small connection pool) per process instead of opening/closing one per call.

## Problem `backend/db.py` opens a brand-new `aiosqlite.connect(DB_PATH)` for every single function call (`list_slides`, `get_slide`, `add_slide`, ...), with SQLite's default rollback-journal mode (not WAL). No connection is shared/pooled. Given the multi-process architecture (4 independent `rotation_loop()`s, see separate issue, each polling the DB every rotation cycle) plus concurrent admin writes, SQLite's default journal mode serializes writers and can raise `database is locked` under contention — especially noticeable if the interval is short or many admin edits/view-now clicks happen close together. ## Suggested fix - Enable WAL mode (`PRAGMA journal_mode=WAL;`) once at startup, which allows concurrent readers alongside a writer and greatly reduces lock contention. - Consider a single shared `aiosqlite` connection (or a small connection pool) per process instead of opening/closing one per call.

paulloth1 commented

2026-07-28 21:19:29 +00:00

(Migrated from github.com)

Fixed in 8835778: enabled PRAGMA journal_mode=WAL in init_db(), which allows concurrent readers alongside a writer and avoids 'database is locked' errors under the multi-process access pattern.

Fixed in 8835778: enabled PRAGMA journal_mode=WAL in init_db(), which allows concurrent readers alongside a writer and avoids 'database is locked' errors under the multi-process access pattern.