Why do read-only connections get locked in WAL mode?

In WAL mode, connections coordinate through the `-shm` file. When opening or closing an empty WAL database, an exclusive lock is briefly required. If a new read-only connection arrives at this moment, an `SQLITE_BUSY` error may occur, even without any application data writes.

How can this issue be mitigated?

The main workarounds include setting a non-zero busy timeout and switching to DELETE mode for workloads with infrequent writes. Using connection pooling or reusing connections to reduce the total number of read-only connections can also help.

Are there any drawbacks to switching to DELETE mode?

In DELETE mode, table-wide locks occur during write operations, which can reduce concurrency for write-heavy workloads. The choice of mode should depend on the specific read/write balance and workload frequency. For scenarios with extremely rare writes, such as the one described by hynek.me, DELETE mode can be a safer option.

Dev

A Hidden Pitfall of SQLite WAL Mode: Lock Conflicts Even in Read-Only Connections

In SQLite's WAL mode, lock conflicts can occur even with short-lived read-only connections. The connection lifecycle in WAL mode can cause "database is locked" errors, even in the absence of any writes.

July 27, 2026 4 min read Reviewed & edited by the SINGULISM Editorial Team

A Hidden Pitfall of SQLite WAL Mode: Lock Conflicts Even in Read-Only Connections — Photo from Unsplash

The “database is locked” error in SQLite is generally thought to be related only to write operations. However, it has been revealed that even short-lived read-only connections can trigger this error.

According to a report on Lobsters via hynek.me and ubernostrum, the connection lifecycle in Write-Ahead Logging (WAL) mode can lead to lock conflicts even when no application data writes are taking place. This phenomenon is especially pronounced in workloads where writes to the database are rare but reads occur frequently.

Common Misconceptions About WAL Mode

Standard advice for handling SQLite locking issues typically boils down to three points:

Use WAL mode.
Set a non-zero busy timeout.
Use BEGIN IMMEDIATE for write transactions.

While this advice holds true for typical web application workloads, which assume long-lived database connections, hynek.me encountered a specific scenario that breaks these assumptions. In this case, writes to the database occurred less than once a month, while reads were performed tens to hundreds of times per second by independent processes. Each process opened the database in read-only mode (SQLITE_OPEN_READONLY), executed a single SELECT query, and then immediately closed the connection.

To avoid writes blocking reads, the database was configured to use WAL mode. However, even with this setup, “database is locked” errors occurred sporadically. The issue was exacerbated by the default connection timeout value of 0 in the SQLite C library.

Write Conflicts Occur Even During Reads

In WAL mode, connections coordinate with each other through the -shm file. When an empty WAL database is opened or closed, an exclusive lock is temporarily required. If a new read-only connection arrives at this precise moment, SQLITE_BUSY errors may occur, even if no application data writes are taking place.

The situation was clarified through a comparison of file timestamps provided by hynek.me:

$ ls -la /vmws/config/config.db*
-rw-r----- 1 root root 294912 Jul 21 09:49 /vmws/config/config.db
-rw-r----- 1 root root 32768 Jul 24 18:26 /vmws/config/config.db-shm
-rw-r----- 1 root root 0 Jul 24 18:26 /vmws/config/config.db-wal

The main database file (config.db) had not been updated since July 21. However, the -wal and -shm files were being modified by read-only processes until July 24. This unexpected writing activity occurred in what was presumed to be a read-only system.

Workarounds and Trade-Offs

Considering the extremely infrequent writes in their use case, hynek.me resolved the issue by switching the database from WAL mode to DELETE mode. Since making this change, no “database is locked” errors have occurred.

Hynek.me reflected, “If the default busy timeout were not 0, I might never have learned about this issue. In a way, it’s fortunate that the error was so apparent—it’s easier to address a problem when it fails clearly rather than silently.”

However, this solution is not universally ideal. In DELETE mode, table-wide locks occur during write operations, making it unsuitable for workloads with high write frequency. Developers must carefully evaluate the trade-offs and choose the appropriate mode based on their specific requirements.

A Reproducible Test Script

Hynek.me has also shared a Python script using the standard library to reproduce this issue. The script runs three scenarios in parallel processes:

A: No busy timeout, WAL mode
B: 1-second busy timeout, WAL mode
C: No busy timeout, no WAL mode (DELETE mode)

On a 2023 MacBook Pro running Python 3.14, 1 to 10 failures were observed in Scenario A, while no failures occurred in Scenarios B and C. These results clearly highlight the relationship between WAL mode and busy timeout settings.

Editorial Opinion

This discovery highlights a hidden pitfall in SQLite’s WAL mode. In the short term, developers building systems that rely heavily on short-lived, read-only connections should reconsider their choice of enabling WAL mode. As demonstrated by hynek.me, switching to DELETE mode or configuring an appropriate busy timeout can mitigate the issue effectively.

This problem is particularly likely to occur in architectures with independent processes that do not use connection pooling. Adopting connection pooling or reusing connections could potentially reduce the frequency of lock conflicts.

From a long-term perspective, improvements in SQLite’s WAL implementation would be ideal. Optimizing the lock coordination for short-lived, read-only connections could provide a fundamental solution to this issue. Furthermore, SQLite’s documentation should explicitly mention that lock conflicts can occur even with read-only connections in WAL mode.

From an editorial standpoint, it is crucial for developers to thoroughly analyze the expected workload patterns when selecting a database engine.

References

“SQLite WAL Mode Can Lock Short-Lived Readers”, by hynek.me via ubernostrum — Lobsters, 2026-07-26T22:32:54.000Z (ARR)
Source URL: https://hynek.me/til/sqlite-read-only-wal-locked/

Frequently Asked Questions

Why do read-only connections get locked in WAL mode?: In WAL mode, connections coordinate through the `-shm` file. When opening or closing an empty WAL database, an exclusive lock is briefly required. If a new read-only connection arrives at this moment, an `SQLITE_BUSY` error may occur, even without any application data writes.
How can this issue be mitigated?: The main workarounds include setting a non-zero busy timeout and switching to DELETE mode for workloads with infrequent writes. Using connection pooling or reusing connections to reduce the total number of read-only connections can also help.
Are there any drawbacks to switching to DELETE mode?: In DELETE mode, table-wide locks occur during write operations, which can reduce concurrency for write-heavy workloads. The choice of mode should depend on the specific read/write balance and workload frequency. For scenarios with extremely rare writes, such as the one described by hynek.me, DELETE mode can be a safer option.

Source: Lobsters

Written by SINGULISM Editorial Team

Edited & reviewed by Kenichiro Yamamoto

Last updated: July 26, 2026

If you find any factual errors or inaccuracies, we will promptly publish a correction. Please contact us via the contact form to request a correction.

Comments

← Back to Home