Decision in brief
Start with the SQLite result code, the statement that failed and the duration of outstanding transactions. SQLITE_BUSY commonly means another connection holds a conflicting lock. SQLite WAL lets readers overlap a writer, but it still permits one writer at a time per database. Shorten write transactions, bound retries, and investigate old readers before changing checkpoint settings.

What to log before changing SQLite settings
A framework can turn several SQLite errors into the same database-is-locked string. Capture the primary and extended result codes if the driver exposes them. Record the actual SQLite version in the affected process, journal mode, busy timeout, transaction lifetime, pending writers, database location and WAL size. The sqlite3 executable can use a different SQLite build from the application.
| Observation | Probable mechanism | First action |
|---|---|---|
| SQLITE_BUSY during BEGIN IMMEDIATE or a write | Another connection is writing, or a file-level conflict prevents progress. | Measure writer wait and transaction duration. Move network calls out of write transactions and queue bursts if needed. |
| SQLITE_BUSY_SNAPSHOT when a reader tries to write | A WAL read snapshot became stale after another connection committed. | Roll back the stale transaction. Retry the complete transaction if its external effects are idempotent. |
| SQLITE_LOCKED | The conflict typically involves the same connection or shared cache, such as changing an object another cursor is still using. | Close or finish the relevant cursor and review connection sharing. Increasing a busy timeout alone may not address this case. |
| WAL file grows while writes succeed | A long-lived reader may pin an older snapshot. A large file can also be reused without shrinking. | Check active readers and checkpoint frame counts. Compare the trend over repeated cycles before assuming the file cannot be recycled. |
Reproduce SQLite writer contention on a temporary database
Use two SQLite CLI sessions pointing at the same new file in a local temporary directory. These steps reproduce expected lock contention. Use a third session only for observation, and keep these experiments away from production files.
- In session A run PRAGMA journal_mode=WAL, CREATE TABLE t(n INTEGER), and INSERT INTO t VALUES (1), as three terminated SQL statements. Verify that the journal-mode result is wal.
- In A run BEGIN IMMEDIATE followed by UPDATE t SET n=n+1. Leave the transaction open.
- In B use .timeout 200, then run SELECT n FROM t and BEGIN IMMEDIATE as separate commands. The read sees the last committed value of 1. The second write transaction fails with database is locked after the short wait.
- Run COMMIT in A. Retry BEGIN IMMEDIATE in B, update t and commit. The write now succeeds without changing the journal mode.
- To observe a pinned reader, start a new BEGIN and SELECT in B. Commit a few separate writes from A, then run PRAGMA wal_checkpoint(PASSIVE) in A. Compare logged and checkpointed frames before and after ending B’s read transaction. A mismatch can reflect limited checkpoint progress, and the PASSIVE result's first column alone does not prove that all frames were copied.
How to improve SQLite performance under load
Measure transaction duration and end-to-end request latency together. Adopt a modest, per-connection busy timeout within the request budget. A bounded retry should have a deadline and must account for side effects. If retries lift the average while requests routinely exceed their deadlines, shorten the transaction or change how work is admitted.
For WAL growth, check for open cursors, streaming responses and workers that leave read transactions open. Run a passive checkpoint observation on the database owner host and track WAL size separately from checkpointed frames. Never delete a live WAL file. Use the backup API or another documented snapshot method, and practice restoring it in isolation.
An ordinary lock error differs from corruption. If the application reports malformed data, preserve a supported backup and logs, and verify the SQLite build with your vendor before attempting repair. SQLite’s WAL documentation describes a rare reset race fixed in 3.51.3 and listed backports. Treat version verification and a restoration drill as separate tasks from reducing lock waits.
HN cases and the threshold for changing databases
HN commenter rich_sasha described locking and slowdowns alongside WAL growth. They reported monitoring and managing checkpoint behavior and pointed out that storage presented as a local drive did not always act like a physical local disk. Use this account to choose metrics to collect. The comment gives no general threshold or benchmark for SQLite.
The Sairo author described using one SQLite WAL-mode file per object-storage bucket with a background crawler writing an FTS5 index. The work is naturally partitioned by bucket and its writer is distinct from search readers. That design illustrates a useful option when the application's data really can be split. A reported search latency for that workload does not estimate your write capacity.
If a single writer queue regularly exceeds the service's latency target after transaction tuning, and independent hosts must write to shared state, test a client-server database. If the load can be partitioned into independent files without breaking atomicity, a per-file queue can preserve SQLite's deployment advantage.