SQLite “Database Is Locked”: WAL Mode, busy_timeout, and a Reliable Fix

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

SQLite returns SQLITE_BUSY—often shown as “database is locked”—when a connection cannot obtain the lock it needs. The fix is rarely “increase the timeout and forget it.” Long transactions, a read-to-write upgrade, forgotten cursors, multiple writers, checkpoint pressure, and unsuitable filesystems can all create the same symptom.

What You’ll Learn: understand SQLite locking, enable WAL mode correctly, set a bounded busy timeout, find long writers, avoid transaction-upgrade deadlocks, and decide when a client/server database is the better architecture.

1. What WAL Changes—and What It Does Not

2. Inspect the Current Database

PRAGMA journal_mode;
PRAGMA busy_timeout;
PRAGMA synchronous;
PRAGMA wal_autocheckpoint;
PRAGMA database_list;

Record the database path and confirm every process is opening the same file. Relative paths can create multiple databases and lead to misleading diagnostics.

3. Enable WAL Mode Once

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;

journal_mode=WAL is persistent for the database. busy_timeout is connection-specific, so set it on every newly opened connection or through the client library configuration. Choose a bounded value that matches request latency objectives.

Do not switch a live production database to WAL without testing backup, checkpoint, filesystem, and multi-process behavior. Preserve the database, -wal, and -shm relationship during supported backup operations.

4. Keep Write Transactions Short

-- Acquire the write reservation before doing work.
BEGIN IMMEDIATE;
UPDATE jobs
SET status = 'running', started_at = CURRENT_TIMESTAMP
WHERE job_id = 42 AND status = 'queued';
COMMIT;

• Do not perform network calls, user interaction, file processing, or model inference inside a write transaction.
• Prepare values before BEGIN.
• Write the minimum rows and commit immediately.
• Always roll back in error paths.
• Close cursors/statements so read transactions can finish.

5. Why BEGIN IMMEDIATE Can Help

A deferred transaction may start by reading and later try to upgrade to a writer. If another connection already prevents that upgrade, SQLite can return SQLITE_BUSY immediately because waiting cannot resolve the lock cycle. BEGIN IMMEDIATE asks for the write reservation at the start, making contention visible before application work is performed.

6. Configure the Application Connection

import sqlite3

con = sqlite3.connect("app.db", timeout=5.0, isolation_level=None)
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA synchronous=NORMAL")
con.execute("PRAGMA busy_timeout=5000")

try:
    con.execute("BEGIN IMMEDIATE")
    con.execute(
        "UPDATE jobs SET status=? WHERE job_id=?",
        ("running", 42),
    )
    con.execute("COMMIT")
except Exception:
    con.execute("ROLLBACK")
    raise
finally:
    con.close()

The Python timeout and SQLite busy_timeout both express waiting behavior; keep the application policy explicit and test the actual driver. Add bounded retry with jitter only around idempotent or transactionally safe operations.

7. Inspect and Control Checkpoints

PRAGMA wal_checkpoint(PASSIVE);
PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_autocheckpoint;

A long-lived reader can prevent old WAL frames from being checkpointed, allowing the WAL file to grow. Use PASSIVE for observation with minimal interference. Schedule more aggressive checkpoint modes only with evidence and an understanding of concurrent readers.

8. A Reproducible Contention Test

1. Connection A runs BEGIN IMMEDIATE and updates a row without committing.
2. Connection B sets busy_timeout=2000 and attempts BEGIN IMMEDIATE.
3. Verify that B waits about two seconds and returns busy.
4. Commit A and repeat; B should now succeed.
5. Move artificial work outside A’s transaction and compare lock duration.

9. Root-Cause Checklist

• Which process owns the current writer?
• How long is its transaction open?
• Is a cursor keeping a read transaction alive?
• Does every connection set busy_timeout?
• Is the application upgrading a deferred read transaction to a write?
• Are multiple worker processes writing concurrently without serialization?
• Is WAL growing because checkpoints cannot advance?
• Is the database on a network share, sync folder, or unsupported VFS?
• Could a crashed process, antivirus, or backup tool be interfering with files?

10. When SQLite Is No Longer the Right Fit

SQLite is excellent for embedded applications, desktop tools, mobile devices, local services, tests, and modest single-writer workloads. Move to PostgreSQL, SQL Server, MySQL, or another server database when sustained concurrent writes, remote access, centralized administration, high availability, or independent scaling are core requirements.

Summary

WAL mode improves reader/writer overlap but preserves SQLite’s single-writer rule. The durable fix is short explicit write transactions, a bounded per-connection timeout, correct checkpoint and filesystem behavior, and writer serialization where necessary. A larger timeout alone only hides the queue.

Continue learning: To strengthen your SQLite query-writing foundation after resolving contention, browse Udemy and search for SQLite SQL courses with exercises on queries, updates, and application database access. To strengthen the SQL querying and relational database fundamentals behind your work, explore the interactive courses on DataCamp.

Related DBA park Guides

• SQLite vs DuckDB

Official References

• SQLite documentation: Write-Ahead Logging
• SQLite documentation: PRAGMA statements
• SQLite documentation: Result and error codes