Fixing SQLite WAL Mode Reader Locks in Your Homelab

You’ve seen the error. Buried in the logs of Uptime Kuma, Home Assistant, or some other self-hosted gem is the dreaded database is locked. Your first instinct is to blame the app, or maybe Docker. But the problem is often more subtle. Many of these applications use SQLite, and its high-performance Write-Ahead Logging (WAL) mode has a sharp edge you can cut yourself on.

If other tools read these SQLite databases directly, like a Grafana dashboard or a monitoring script, they can contribute to the problem. WAL mode is fantastic for concurrency, but its checkpoint mechanism can lead to a bizarre form of reader starvation, where your read-only tools cause the entire database to lock up.

This isn’t a theoretical issue. It can take down monitoring dashboards and cause intermittent failures across a homelab. Here’s how to identify the problem, understand why it happens, and actually fix it.

How WAL mode works

In WAL mode, SQLite appends changes to a separate -wal file instead of rewriting the database in place. Readers keep reading a consistent snapshot while the one writer appends. Periodically a checkpoint copies the changes from the WAL back into the main .db file so the WAL can start over. SQLite runs one automatically when the WAL reaches about 1000 pages by default.

Two rules from the SQLite documentation explain most homelab problems:

1. Only one writer at a time. A second writer waits, and if it waits longer than its busy timeout, it gets SQLITE_BUSY / database is locked.
2. A checkpoint can only finish when no one is still reading old data from the WAL. If some connection always has a read transaction open, checkpoints never complete and the WAL grows without bound. SQLite calls this checkpoint starvation; the cure is “reader gaps”, moments when nobody is reading.

Note what isn’t true: checkpoints do not block readers. The default (passive) checkpoint just copies as much as it can and stops.

Diagnosing

sqlite3 /app/data/kuma.db "PRAGMA journal_mode;"   # 'wal' means WAL mode
ls -lh /app/data/kuma.db*                         # watch the size of kuma.db-wal over time

• A -wal file that only ever grows points to checkpoint starvation: something keeps a read transaction open.
• database is locked errors point to write contention: several writers, long write transactions, or a blocking checkpoint.

Fixes, in order of preference

1. Don’t hold read transactions open. Tools that query the app’s database (dashboards, scripts) should run short queries and close them, not keep a transaction or connection idle in the middle of a read. Better still, use the app’s own API or metrics instead of reading its database.

2. Only one writer. Don’t point two containers or app instances at the same SQLite file, and don’t write to it from scripts while the app is running.

3. Give writers a busy timeout. Apps should set PRAGMA busy_timeout (or the driver’s equivalent) so a writer waits briefly instead of failing at once. That’s an app setting; check the app’s documentation for a way to configure it.

4. Checkpoint manually only with care. You can run a checkpoint during quiet hours, from the same host:

sqlite3 -cmd ".timeout 5000" /app/data/kuma.db "PRAGMA wal_checkpoint(PASSIVE);"

PASSIVE never blocks anyone. FULL, RESTART and TRUNCATE are more thorough, but block new writers while they wait for readers to finish, so running them frequently against a busy app can cause database is locked errors in the app. If you use TRUNCATE to shrink an oversized WAL, do it once, in a quiet moment, or with the app stopped.

5. Never put a WAL-mode database on a network filesystem. WAL relies on shared memory between processes on one machine, and SQLite’s docs state that it does not work over a network filesystem. Keep SQLite data on local disk or a local Docker volume, not NFS or SMB.

When to move off SQLite

SQLite is excellent for a single app with modest write rates. If several services need to write the same data, or you keep fighting lock errors after the fixes above, the app’s database server option (PostgreSQL or MariaDB, if it offers one) is the better tool. Uptime Kuma 2.x, for example, supports MariaDB.

What’s Next

Once your databases are stable, it’s time to level up other parts of your homelab.

• Refine your Docker setup: Dive deeper into crafting efficient and maintainable services with our Ultimate Docker Compose Guide.
• Improve your monitoring: Now that your dashboards aren’t crashing the database, learn about Self-Hosting Monitoring with Prometheus.