database is locked condition in SQLite connection layer

Answer to the Core Question

In rollback‑journal mode, a writer holds an EXCLUSIVE lock only while the transaction is active. A concurrent reader that requests a SHARED lock will:

• Block if the connection’s busy_timeout is non‑zero or if a busy handler is installed and the handler chooses to retry.
• Return SQLITE_BUSY immediately if busy_timeout is zero and no busy handler is present.

Thus, the deciding factors are:

1. Whether busy_timeout is set to a non‑zero value on the reader’s connection.
2. Whether a busy handler is attached that can retry the lock acquisition.
3. The length of the writer’s transaction (longer transactions increase the chance that a reader hits the busy state).
4. The filesystem’s support for advisory locking (broken or network filesystems can cause spurious SQLITE_BUSY).

Impact of Zero Default busy_timeout

SQLite’s default busy_timeout is zero, meaning the library returns SQLITE_BUSY immediately without sleeping. Under load, the observed latency comes from:

• Application‑level retry loops that sleep between attempts.
• Thread scheduling delays when the application blocks on a busy error.
• Driver‑level timeouts (e.g., ODBC or JDBC) that may add additional wait time.

Because the library itself does not delay, the only way to reduce latency is to either:

1. Increase busy_timeout so SQLite waits internally.
2. Implement an efficient back‑off strategy in the retry loop.
3. Shorten write transactions or batch writes to reduce the duration of the exclusive lock.

WAL Mode Checkpointing and Latency Spikes

In WAL mode readers and writers can proceed concurrently. However, checkpointing can introduce stalls:

• A checkpoint copies WAL frames back to the main database. If readers hold a SHARED lock that prevents the checkpoint from completing, the writer may block until the readers finish.
• Auto‑checkpointing (triggered by a write that reaches wal_autocheckpoint frames) can cause a writer to pause while waiting for all active readers to release their marks.
• Long read transactions keep the reader’s mark alive, so a write that needs to trigger a checkpoint will experience a latency spike only when the read ends or the checkpoint threshold is reached.

To mitigate this:

1. Keep read transactions short or use PRAGMA wal_autocheckpoint(0) to disable auto‑checkpoints and schedule checkpoints manually.
2. Use PRAGMA wal_checkpoint(TRUNCATE) during low‑traffic windows to clear the WAL proactively.
3. Monitor WAL size and checkpoint activity with PRAGMA wal_checkpoint(TRUNCATE) and PRAGMA wal_checkpoint(RESTART) for diagnostics.

Recommended Diagnostic Step

To fine‑tune the behavior, check whether the application sets a busy handler or busy_timeout per connection. If you can provide the code that opens the SQLite connection, we can suggest the exact setting that will convert immediate SQLITE_BUSY into a controlled wait.

Sample Code

# Example in Python
import sqlite3
conn = sqlite3.connect("example.db", timeout=5)  # timeout is busy_timeout in seconds
# or using a busy handler
conn.set_busy_handler(lambda: True)  # retry immediately