# SQLite WAL Does Not Mean Unlimited Writers: A Practical Python Playbook

A small service can run happily on SQLite for months, then begin throwing `database is locked` as soon as two background workers start processing jobs. Switching to Write-Ahead Logging (WAL) often improves the situation, but it does not turn SQLite into a multi-writer database. That distinction is the first thing an engineer needs to understand before adding more retries or raising every timeout.

SQLite's official [WAL documentation](https://www.sqlite.org/wal.html) is precise: readers and a writer can operate at the same time, but **there can still be only one writer at a time**. This article builds a compact Python pattern around that reality. It covers transaction boundaries, connection lifetimes, bounded retries, crash-safe work identities, and checkpoint monitoring. The examples are illustrative recipes, not benchmarks; measure them under your own workload.

## The lock is a symptom of transaction design

In rollback-journal mode, readers and writers interact through locking that can limit concurrency. WAL changes the commit protocol: writers append to a write-ahead log, while readers use a snapshot of committed data. That reduces interference between ordinary reads and writes. It does not eliminate contention among transactions that need the write lock.

Imagine a worker that starts a transaction, calls an external HTTP service, transforms a payload, and finally updates a row. Another worker arrives during that slow network call. Even if each SQL statement is tiny, the first worker is holding scarce database capacity for the entire external request. WAL cannot make that transaction cheap.

The first remedy is often architectural, not a PRAGMA: finish network calls, validation, and CPU-heavy work **before** acquiring a write transaction. Commit the smallest coherent database change, then release the connection promptly.

A write transaction should protect a database invariant, not the whole business workflow. If you need to coordinate an expensive workflow across failures, store durable task states and do the expensive work outside the transaction.

## Initialize WAL once and configure connections deliberately

A minimal local database can use one-time setup followed by short-lived connections. Python's standard `sqlite3` module is sufficient for this example, and SQLite's [PRAGMA reference](https://www.sqlite.org/pragma.html) documents the relevant settings.

```python
import sqlite3
from pathlib import Path

DB = Path("jobs.sqlite3")

def connect():
    con = sqlite3.connect(DB, timeout=5, isolation_level=None)
    con.execute("PRAGMA busy_timeout=5000")
    con.execute("PRAGMA foreign_keys=ON")
    return con

def initialize():
    with connect() as con:
        mode = con.execute("PRAGMA journal_mode=WAL").fetchone()[0]
        if mode.lower() != "wal":
            raise RuntimeError(f"WAL not active: {mode}")
        con.execute("""
            CREATE TABLE IF NOT EXISTS completed_jobs (
                job_key TEXT PRIMARY KEY,
                result TEXT NOT NULL,
                completed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """)
```

Run `initialize()` in a controlled setup step before starting the workers. A mode switch is not a request to make during every hot transaction. The example uses autocommit mode so transaction boundaries are explicit later; it does not imply that autocommit alone prevents lock contention.

Set `busy_timeout` on each connection. Its purpose is to wait for a conflicting lock to clear instead of failing instantly. Waiting is not free: a worker that waits for five seconds still uses time and capacity. A timeout is a stopgap, not permission to keep transactions open indefinitely.

Do not casually put a WAL database on a network file share. SQLite's documentation says WAL depends on same-host shared-memory mechanisms, so it is not a solution for multiple hosts sharing one database file.

## Make the write atomic, short, and safe to retry

If a job completion message might be delivered twice, a unique key is part of correctness. A queue acknowledgement, HTTP response, or process exit can be lost after SQLite commits. Retrying blindly should not create duplicate completed-job rows.

This example uses a bounded attempt count, an explicit immediate transaction, and a uniqueness constraint. Keep any remote API request outside `save_completed`.

```python
import random
import sqlite3
import time

def save_completed(job_key: str, result: str) -> bool:
    for attempt in range(4):
        con = connect()
        try:
            con.execute("BEGIN IMMEDIATE")
            cursor = con.execute(
                """INSERT INTO completed_jobs (job_key, result)
                   VALUES (?, ?)
                   ON CONFLICT(job_key) DO NOTHING""",
                (job_key, result),
            )
            con.execute("COMMIT")
            return cursor.rowcount == 1
        except sqlite3.OperationalError as exc:
            if con.in_transaction:
                con.execute("ROLLBACK")
            if "locked" not in str(exc).lower() or attempt == 3:
                raise
            time.sleep(0.05 * (2 ** attempt) + random.uniform(0, 0.03))
        except Exception:
            if con.in_transaction:
                con.execute("ROLLBACK")
            raise
        finally:
            con.close()
    raise AssertionError("unreachable")
```

The return value distinguishes a newly recorded job from one already recorded. The key must describe the **logical job**, not a randomly generated identifier for each attempt. Otherwise every retry defeats the unique constraint.

`BEGIN IMMEDIATE` asks for a write transaction at a predictable point. It can fail or wait when another writer owns the lock; that is expected. The retry policy belongs at this narrow boundary. Do not wrap a non-idempotent payment, email send, or publish operation in the same retry loop just because the database write is retryable.

The sleep is small and randomized to reduce synchronized retries. Four attempts are an example, not a recommended universal setting. A busy system may need fewer retries and a real backpressure mechanism; a batch importer can tolerate longer waits.

## A retry is not a cure for slow readers and checkpoints

WAL introduces an extra responsibility: checkpointing. A checkpoint transfers committed frames from the WAL back into the main database. SQLite normally triggers automatic checkpoints after a default threshold of about 1,000 pages, but a long-lived read transaction can prevent a checkpoint from progressing past that reader's snapshot.

If your application streams a query result for minutes, keeps a cursor alive across an external request, or runs a long analytics transaction, the WAL may grow. That can affect read performance and disk usage. The documentation describes the reader end mark and the resulting checkpoint limitations in detail.

A simple operational inspection uses the built-in checkpoint PRAGMA:

```python
def wal_status():
    with connect() as con:
        busy, log_pages, checkpointed_pages = con.execute(
            "PRAGMA wal_checkpoint(PASSIVE)"
        ).fetchone()
    return {
        "busy": busy,
        "log_pages": log_pages,
        "checkpointed_pages": checkpointed_pages,
        "behind": log_pages - checkpointed_pages,
    }

print(wal_status())
```

Treat these values as observations, not a magic health score. `PASSIVE` attempts checkpoint work without waiting aggressively for competing operations; the returned counters help you see whether frames remain. A separate monitoring process, measured during normal load, can reveal whether the backlog repeatedly grows.

Do not reflexively run `TRUNCATE` on every request or delete `-wal` and `-shm` files while the database is in use. Those files are part of SQLite's live coordination and recovery behavior. Consider more aggressive checkpoints only with a measured operational reason and a controlled maintenance window.

## Use separate connections and measure the right latency

SQLite connections have thread-affinity behavior in Python by default. Sharing one connection among unrelated threads without a carefully designed ownership model adds another layer of confusion. A straightforward pattern is one connection per independent operation or one clearly owned connection per worker, closed at a known boundary.

Measure the duration of acquiring a transaction, the time between `BEGIN` and `COMMIT`, the count of `SQLITE_BUSY` outcomes, and the size of the WAL backlog. Total request latency alone cannot tell you whether the problem came from a database lock, network call, or slow business logic.

Use synthetic contention tests before deciding to increase limits in production. Launch several writers that attempt small independent inserts and a reader that scans the table. Then deliberately add a slow transaction and observe how lock waits change. Compare transaction length before and after moving the slow work outside the lock.

If writes are still serialized too often, try a single writer queue inside one process, batching independent inserts into short transactions, or moving to a server database when multi-host writes and higher concurrency are genuine requirements. The best choice depends on throughput, failure tolerance, and operational cost rather than database fashion.

## A small diagnostic checklist

Before calling WAL broken, check whether your application has a long-running write transaction, whether every connection actually uses the expected journal mode and busy timeout, and whether any reader holds a snapshot while an external operation runs.

Then look at duplicate delivery behavior. If a worker crashes after commit but before acknowledging a message, will reprocessing preserve the business invariant? A primary key plus `ON CONFLICT DO NOTHING` handles the completed-job example, but a richer workflow may require explicit states and reconciliation.

Finally, examine the deployment topology. WAL belongs to a database file that its participating processes can coordinate on the same machine. If workloads must share mutable state across machines, adding more Python retry logic will not remove that architecture mismatch.

## What to take into the next incident

The sentence "we already enabled WAL" is not a diagnosis. WAL makes a specific kind of read/write concurrency better; it does not supply multiple simultaneous writers, infinite transaction capacity, or automatic exactly-once processing.

The durable fix is usually a combination of short writes, stable job identities, bounded lock handling, intentional connection ownership, and measured checkpoint behavior. Those choices are less exciting than switching databases, but they are often enough to make a small service predictable again.

Sources: [SQLite WAL mode](https://www.sqlite.org/wal.html), [SQLite PRAGMA reference](https://www.sqlite.org/pragma.html), and [auto-checkpoint behavior](https://www.sqlite.org/c3ref/wal_autocheckpoint.html).