Offloading SQLite Writes to a Thread Executor

SQLite allows exactly one writer against a database at a time — that is a structural invariant, not a setting you can raise. An asyncio service or a multi-threaded worker pool that lets several callers write concurrently therefore does not gain write parallelism; the callers race for one write lock and the losers get SQLITE_BUSY (error 5). The clean way to honour the invariant without scattering retry loops is to route every write through a single dedicated writer thread — a ThreadPoolExecutor(max_workers=1) (or a queue-drained worker) that owns the one write connection — and submit work to it with loop.run_in_executor. This keeps the event loop free of blocking fsync() calls and, because there is only ever one writer, makes lock contention between your own tasks structurally impossible. This page sits under the Async Execution Patterns topic, part of the broader WAL Optimization & Concurrency Tuning guide, and covers the executor approach specifically — where you want an explicit writer thread and a submission boundary you control rather than a library-managed connection.

Figure — Async tasks and pool threads submit write callables to a single-worker executor; the one worker thread owns the write connection, drains submissions in order, and is the sole holder of the write lock.
Single-worker executor serializing all SQLite writes Multiple callers — asyncio coroutines using run_in_executor and worker-pool threads submitting directly — hand write callables to one ThreadPoolExecutor configured with max_workers equal to one. That single worker thread created and owns the write connection with check_same_thread enabled, applies the PRAGMA baseline, and executes each submitted callable one at a time as the sole holder of the write lock, committing to the SQLite database in WAL mode. Because there is one worker, callers never contend for the lock. asyncio tasks run_in_executor pool threads submit() write callables Executor · max_workers=1 one worker owns the conn check_same_thread=True sole write-lock holder executes one at a time commit SQLite WAL mode

Diagnosis

The signature of this problem is SQLITE_BUSY that appears only as write concurrency rises. Under asyncio it surfaces as sqlite3.OperationalError: database is locked raised from inside a coroutine that opened its own connection; under a thread pool it surfaces on whichever worker lost the lock-upgrade race. Two checks separate it from a slow-disk problem.

First, confirm the writers are colliding and not just slow. Instrument the write path to record the acquiring thread and the wait before the lock is granted — blocked writers pile up on BEGIN IMMEDIATE, which takes the write lock at statement start:

import sqlite3, threading, time, logging
log = logging.getLogger("write_probe")

def probe_write(conn: sqlite3.Connection, sql: str, params=()):
    t0 = time.monotonic()
    conn.execute("BEGIN IMMEDIATE;")             # reserve the write lock now, not at first write
    waited_ms = (time.monotonic() - t0) * 1000   # time spent blocked on the lock
    conn.execute(sql, params); conn.execute("COMMIT;")
    if waited_ms > 50:                           # >50ms blocked == contention, not disk latency
        log.warning("tid=%s blocked %.0fms on write lock", threading.get_ident(), waited_ms)

If those warnings fire from multiple thread ids and the blocked time scales with caller count, you have write contention. When it vanishes at one writer, the fix is to make there be one writer.

Second, understand why the GIL does not save you here. Python’s sqlite3 releases the GIL around the blocking C calls, so two threads genuinely enter SQLite’s locking layer at the same time — the concurrency is real at the C level, which is exactly why they contend for the write lock. Offloading each write to its own pool thread makes contention worse, not better. The lever that removes it is worker count: one worker means one thread ever reaches the write lock.

Solution

Create a ThreadPoolExecutor(max_workers=1). The write connection is created lazily on that worker thread and stored in threading.local, so the handle is only ever touched by the thread that owns it. Every write is a callable submitted to the executor; async callers wrap the submission in loop.run_in_executor so the event loop never blocks.

import asyncio, sqlite3, threading
from concurrent.futures import ThreadPoolExecutor

_writer = ThreadPoolExecutor(max_workers=1, thread_name_prefix="sqlite-writer")
_local = threading.local()

def _conn(path: str) -> sqlite3.Connection:
    conn = getattr(_local, "conn", None)
    if conn is None:                                     # created ON the worker thread, once
        conn = sqlite3.connect(path, check_same_thread=True)  # enforce single-thread ownership
        conn.execute("PRAGMA journal_mode=WAL;")         # readers get snapshots; writer never blocks them
        conn.execute("PRAGMA synchronous=NORMAL;")       # fsync at checkpoint, not per-commit; crash-safe
        conn.execute("PRAGMA busy_timeout=5000;")        # backstop for OTHER processes on the file
        conn.execute("PRAGMA foreign_keys=ON;")          # per-connection; off by default
        _local.conn = conn
    return conn

def _apply(path: str, sql: str, params):
    conn = _conn(path)                                   # runs on the single worker thread
    try:
        conn.execute("BEGIN IMMEDIATE;")                 # take the write lock up front
        conn.execute(sql, params)
        conn.execute("COMMIT;")
    except sqlite3.Error:
        conn.execute("ROLLBACK;")                        # leave the connection clean for the next job
        raise

async def write(path: str, sql: str, params=()):
    loop = asyncio.get_running_loop()
    # run_in_executor schedules _apply on the single worker; the loop thread stays free.
    return await loop.run_in_executor(_writer, _apply, path, sql, params)

Because max_workers=1, the executor has one thread, that thread constructs and forever owns the write connection, and every submitted callable runs to completion before the next begins. run_in_executor returns an awaitable, so on asyncio the loop is never held during the write; synchronous pool threads can call _writer.submit(_apply, ...).result() against the same executor. The write lock is uncontested by construction — there is only one thread that ever asks for it.

Verification

Confirm two things: that writes really all ran on one thread, and that the connection carries the intended PRAGMAs. Have the writer stamp its thread id, then assert the audit table holds exactly one:

import sqlite3, asyncio, threading

async def verify(path: str):
    await write(path, "CREATE TABLE IF NOT EXISTS audit(tid INTEGER);")
    await asyncio.gather(*(
        write(path, "INSERT INTO audit(tid) VALUES (?);", (threading.get_ident(),))
        for _ in range(200)
    ))
    conn = sqlite3.connect(path)
    try:
        tids = {r[0] for r in conn.execute(
            "SELECT DISTINCT tid FROM audit;")}          # every INSERT recorded the executor thread id
        # note: the INSERT runs on the worker, so all rows share the worker's tid, not the callers'
        assert conn.execute("PRAGMA journal_mode;").fetchone()[0].lower() == "wal"
        assert conn.execute("PRAGMA busy_timeout;").fetchone()[0] == 5000
    except sqlite3.Error as exc:
        raise AssertionError(f"verification failed: {exc}") from exc
    finally:
        conn.close()

asyncio.run(verify("app.db"))

The read-backs prove the writer connection is in WAL with the 5-second timeout. Then drive real load — fan 32 async tasks at write() submitting thousands of inserts — and assert no OperationalError escapes. Zero lock errors under load means the writes were serialized cleanly instead of rejected; a database is locked means a write is escaping the executor onto a second connection.

Failure Modes & Gotchas

Raising max_workers above 1 reintroduces exactly the contention you removed. It is tempting to “speed up” the writer by giving the executor more threads, but each extra worker opens its own write connection, and now two workers reach BEGIN IMMEDIATE at once and contend for the single write lock — SQLITE_BUSY returns, only intermittently and harder to reproduce. The single-writer guarantee is the whole point; keep the write executor at one worker. Parallelism belongs on the read side, where WAL lets independent reader connections run concurrent snapshot reads.

A transaction cannot span two separately submitted callables. Each callable that opens BEGIN IMMEDIATE and COMMIT is one atomic unit on the worker; if you submit “insert A” and “insert B” as two calls expecting them to share a transaction, they do not — the worker may run an unrelated callable between them, and a crash after A commits leaves B unwritten. To make several statements atomic, submit them as one callable that does BEGIN IMMEDIATE, all statements, then COMMIT. Ordering is guaranteed only in submission order on the single worker; if two coroutines submit interdependent writes without awaiting the first, their relative order is whatever the executor’s queue received first, so await the dependency before submitting the dependent write.

The connection must be created and used on the worker thread. sqlite3 connections are bound to the thread that created them; opening the write connection on the main thread and then using it inside _apply raises ProgrammingError: SQLite objects created in a thread can only be used in that same thread — which is why the connection is built lazily inside _conn on first use and cached in threading.local. Leave check_same_thread=True (the default) so a stray cross-thread use fails loudly instead of corrupting state; do not set it False to “share” the handle, which defeats the ownership model. For a library-managed alternative that hides the executor behind an awaitable connection, avoiding event-loop blocking with aiosqlite covers the same offload with less wiring.