Why SQLite WAL Still Raises "database is locked" in Python

You switch a small Python service to SQLite write-ahead logging, start two workers, and still see "database is locked." The obvious question is whether WAL is broken. Usually it is not. The mistaken assumption is that allowing readers and writers to overlap is the same as allowing multiple writers to commit simultaneously.

SQLite's WAL documentation makes the distinction explicit: readers can continue while a writer appends, but there is still only one active writer. That matters for webhook handlers, queue consumers, background jobs, and small web applications that share a single database file. A slow transaction can block a perfectly ordinary insert in another process.

This tutorial starts with a reproducible lock, then changes only the parts that actually control write contention. The examples use Python's built-in sqlite3 module, a local temporary database, and ordinary SQL. You do not need an ORM or a server installation.

WAL changes reader/writer overlap, not the single-writer rule

In the default rollback-journal mode, a reader can interfere with a writer's ability to complete a transaction. WAL stores new pages in a separate log first. A reader keeps using a consistent view of the database while the writer appends changes. That is a useful improvement for workloads that mix frequent reads with short writes.

However, the WAL still has one write stream. Two connections cannot both own the writer lock. The second writer must wait until the first transaction ends, or fail when its busy timeout expires. WAL is not a substitute for keeping transactions short.

A practical model is to separate three operations:

• Reading a stable snapshot.
• Owning the writer lock and committing a change.
• Copying committed WAL content back into the main database during a checkpoint.

They have different concurrency behavior. Treating them as a single "SQLite is concurrent" setting hides the cause of most locking complaints.

Reproduce a blocked writer with two connections

Save the following as a standalone Python file and run it. It creates a temporary database, enables WAL, and opens two independent connections. Connection A deliberately keeps a write transaction open while connection B attempts another write.

from pathlib import Path
from tempfile import TemporaryDirectory
import sqlite3

with TemporaryDirectory() as folder:
    db = Path(folder) / "events.db"

    setup = sqlite3.connect(db, isolation_level=None)
    mode = setup.execute("PRAGMA journal_mode=WAL").fetchone()[0]
    setup.execute(
        "CREATE TABLE events (id INTEGER PRIMARY KEY, message TEXT)"
    )
    setup.close()
    assert mode == "wal"

    writer_a = sqlite3.connect(db, timeout=0.2, isolation_level=None)
    writer_b = sqlite3.connect(db, timeout=0.2, isolation_level=None)

    writer_a.execute("BEGIN IMMEDIATE")
    writer_a.execute(
        "INSERT INTO events(message) VALUES (?)", ("first",)
    )
    try:
        writer_b.execute(
            "INSERT INTO events(message) VALUES (?)", ("second",)
        )
    except sqlite3.OperationalError as error:
        print(type(error).__name__, str(error))
    else:
        raise AssertionError("Second writer unexpectedly succeeded")

    writer_a.execute("COMMIT")
    writer_b.execute(
        "INSERT INTO events(message) VALUES (?)", ("second",)
    )

    print(writer_b.execute("SELECT COUNT(*) FROM events").fetchone()[0])
    writer_a.close()
    writer_b.close()

The expected observation is an OperationalError mentioning that the database is locked, followed by a row count of two. Connection B did not have corrupt data or a broken journal. It tried to write while connection A owned the writer lock. After A committed, B could proceed.

The small timeout is intentional. It makes the demonstration fast instead of leaving the program waiting. In a real service, choose a timeout from the request latency budget rather than copying this demonstration value blindly.

Notice the explicit BEGIN IMMEDIATE. It requests the write transaction at the start instead of letting the first write statement discover the contention later. That makes the critical section visible to the reader and to anyone inspecting logs.

Set a bounded busy timeout, then measure it

Python's sqlite3.connect accepts a timeout parameter. Its documented default is five seconds. This controls how long a connection waits on a lock before raising an OperationalError; it does not promise that a lock will clear during that window.

SQLite also exposes PRAGMA busy_timeout, measured in milliseconds. You can inspect the configured value directly rather than guessing which connection settings are in effect.

import sqlite3

db = sqlite3.connect("app.db", timeout=2.0, isolation_level=None)
value = db.execute("PRAGMA busy_timeout").fetchone()[0]
print("Busy timeout in milliseconds:", value)

db.execute("CREATE TABLE IF NOT EXISTS jobs (id INTEGER PRIMARY KEY)")
db.close()

The path here is illustrative; use a file inside your application's designated data directory. For tests, prefer a temporary directory. Keep a timeout small enough that one stuck worker cannot absorb an entire fleet of incoming requests. A longer timeout can smooth brief bursts, but it does not fix a writer that holds a transaction while waiting for network I/O.

A useful diagnostic log records transaction start time, time spent waiting to begin a write, commit time, and which operation ran inside the transaction. A simple error count without timing data will not tell you whether the cause is lock acquisition, a slow transaction, or a checkpoint interaction.

Move network calls out of write transactions

This pattern causes unnecessary contention: begin a transaction, read a job, call a remote API, wait for a response, then update the row and commit. The database writer lock is now tied to unpredictable network latency.

The better boundary is usually a short claim transaction followed by the slow work outside it. A final short transaction records the result. If multiple workers may claim the same job, enforce the claim atomically with a conditional update, not a separate SELECT followed by an unconditional UPDATE.

The next example shows a claim that can be attempted by multiple connections. Each connection uses its own handle to the same local database.

import sqlite3

def claim_job(db_path: str, job_id: int, worker: str) -> bool:
    conn = sqlite3.connect(
        db_path, timeout=2.0, isolation_level=None
    )
    try:
        conn.execute("BEGIN IMMEDIATE")
        changed = conn.execute(
            """
            UPDATE jobs
               SET status = 'claimed', worker = ?
             WHERE id = ? AND status = 'pending'
            """,
            (worker, job_id),
        ).rowcount
        conn.execute("COMMIT")
        return changed == 1
    except Exception:
        if conn.in_transaction:
            conn.execute("ROLLBACK")
        raise
    finally:
        conn.close()

This function intentionally does not perform the job itself. Its only responsibility is to claim it. The worker can then release the connection, execute the slow external task, and store completion in another short transaction.

A conditional claim is not a complete job queue. You still need an explicit policy for abandoned claims, visibility timeouts, retryability, and side effects. In particular, a failed network response must not automatically be interpreted as a failed remote operation. The database can prevent duplicate local claims without proving whether an external action happened.

Make transaction ownership explicit

Connection behavior changed in modern Python: the sqlite3 module has an autocommit interface, while isolation_level controls the older implicit transaction behavior. Code copied from older tutorials may therefore use different transaction assumptions than your service.

The examples here deliberately set isolation_level=None and issue SQL BEGIN, COMMIT, and ROLLBACK statements themselves. This is a simple way to make each critical section visible for teaching and testing. It is not a recommendation to mix implicit and explicit transaction models in an existing application.

If you use Python's newer autocommit parameter instead, choose a model once and test it against the exact Python version deployed. Do not assume that wrapping every operation in a with block fixes a transaction that begins too early. Confirm when the lock is actually acquired and when the database is free to accept the next writer.

A review of transaction boundaries should answer four concrete questions. Where does the transaction start? Which statement first needs the writer lock? Does any file, HTTP, model, or queue operation happen before commit? Does every exception path roll back or otherwise release the connection? If the answers are unclear, adding a timeout is only masking uncertainty.

See what a long-lived reader does to checkpoints

A checkpoint copies content from the WAL back into the main database file. SQLite normally handles checkpointing automatically, so most small applications can begin with its defaults. That does not mean checkpoint progress is guaranteed under an endless stream of overlapping readers.

A reader holds a snapshot end mark. A checkpoint cannot advance past pages needed by that reader. If readers never finish, the WAL can grow even while new writes continue to succeed. This often appears as a growing database-wal file rather than as an immediate write failure.

You can inspect checkpoint status through PRAGMA wal_checkpoint(PASSIVE). It reports three values: whether the operation encountered a busy condition, the number of pages in the WAL, and the number of pages checkpointed.

import sqlite3

conn = sqlite3.connect("app.db", timeout=2.0)
mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
if mode != "wal":
    raise RuntimeError("Expected WAL mode")

busy, log_pages, checkpointed = conn.execute(
    "PRAGMA wal_checkpoint(PASSIVE)"
).fetchone()

print({
    "busy": busy,
    "wal_pages": log_pages,
    "checkpointed_pages": checkpointed,
})
conn.close()

This is a diagnostic probe, not an instruction to force TRUNCATE after every request. A passive checkpoint may stop before completing without disrupting active readers. More aggressive checkpoint modes have different blocking behavior, so schedule them with knowledge of your workload rather than using them as a reflexive lock repair.

Also keep the database on a local filesystem suitable for SQLite WAL. Its shared-memory design does not support the ordinary cross-machine network-filesystem scenario described in SQLite's official documentation.

Use a small contention test before tuning production

A reliable test needs at least two independent connections to the same file. A single connection running a loop cannot reproduce writer competition by itself. The most revealing test holds a transaction open on one connection, then measures how the other behaves with a known timeout.

Run the test in both directions: first with a deliberate hold that exceeds the timeout, then with a short hold that ends before the timeout. Confirm the expected failure and expected successful wait. Log elapsed time, not just the exception text.

After that, verify the application's own transaction length. A write query may take milliseconds while the surrounding transaction stays open for seconds. Look for synchronous HTTP calls, expensive serialization, long loops, or human input inside write transactions. Those are better targets for improvement than arbitrary PRAGMA values.

Keep the test separate from production records. A temporary directory and deterministic fixtures make the result repeatable and prevent a performance experiment from altering real work.

A practical decision rule for small Python services

Start with WAL when the application has mixed reads and writes on one local machine and the mode is appropriate for its storage. Use a finite lock timeout and short write transactions. Have each process or worker create and manage its own connection according to the application's threading model. Avoid passing one Python connection around unrelated threads.

If a lock persists, measure the actual transaction boundary and write occupancy before increasing the timeout. If the database-wal file grows, inspect long-lived readers and checkpoint progress separately. These are two different symptoms with different interventions.

If the required write throughput depends on many simultaneous long write transactions, SQLite's single-writer design may be the wrong fit. That is an architectural decision, not evidence that WAL is malfunctioning.

The key result is reproducible: WAL lets readers overlap a writer; it does not let two writers own the write lock. Fix the time spent holding that lock, and the "database is locked" exception becomes a measurable contention signal rather than a mysterious configuration failure.

Primary references

• SQLite: Write-Ahead Logging — writer concurrency, checkpoints, and filesystem constraints.
• Python: sqlite3 module — connection timeout and transaction-control behavior.
• SQLite: PRAGMA statements — busy_timeout and wal_checkpoint semantics.

The reproducer and claim function intentionally use built-in Python and SQLite features only; adapt transaction and recovery policy to the failure modes of your application.