Resolving Python multiprocessing deadlock with SQLite3 database locks

by

Root Cause Diagnosis

A sqlite3.Connection opened before multiprocessing forks the workers is inherited by every child, and SQLite’s POSIX advisory locks belong to the process rather than the connection. A child that closes or garbage-collects the inherited file descriptor releases locks the parent still believes it holds, and can leave the shared lock state inconsistent, which is why SQLite documents that open connections must not cross fork(). The second cause is lock-upgrade contention. Python’s default implicit transactions run BEGIN (deferred) before the first write, so two processes that both read and then write each hold a SHARED lock and both try to upgrade to RESERVED. SQLite returns SQLITE_BUSY immediately for one of them, without waiting for the busy timeout, because waiting would deadlock. Under a retry loop, or a Pool waiting on a result that never arrives, this looks like a hang.

Quick Fix

Enable WAL once, since the setting persists in the database file. Then open one connection per worker process, with an explicit BEGIN IMMEDIATE for every write.

sqlite3 /path/to/app.db "PRAGMA journal_mode=WAL;"

import sqlite3
conn = sqlite3.connect("/path/to/app.db", timeout=30, isolation_level=None)  # inside the worker
conn.execute("BEGIN IMMEDIATE")
# ... writes ...
conn.execute("COMMIT")

Step-by-Step Resolution

1. Confirm which condition you have

Dump the stacks of the hung processes and see which processes hold the file open.

pip install py-spy
py-spy dump --pid <worker_pid>
lsof /path/to/app.db /path/to/app.db-wal /path/to/app.db-shm

A stack sitting in sqlite3.Connection.execute, or a Pool worker waiting on the task queue, points to an inherited connection or a lock-upgrade conflict. sqlite3.OperationalError: database is locked raised after roughly 5 seconds is ordinary writer contention, because the Python default is timeout=5.0.

2. Stop sharing connections across processes

Do not create the connection at module level or in the parent before the pool starts. Open it inside each worker with a Pool initializer.

import sqlite3
import multiprocessing as mp

DB = "/var/data/app.db"
_conn = None

def init_worker():
    global _conn
    _conn = sqlite3.connect(DB, timeout=30, isolation_level=None)
    _conn.execute("PRAGMA journal_mode=WAL")
    _conn.execute("PRAGMA synchronous=NORMAL")

def task(item):
    row = _conn.execute("SELECT value FROM t WHERE id=?", (item,)).fetchone()
    return row

if __name__ == "__main__":
    with mp.Pool(4, initializer=init_worker) as pool:
        print(pool.map(task, range(100)))

3. Switch to WAL and set the busy timeout

In WAL mode, readers do not block the writer and the writer does not block readers. Only one writer runs at a time.

conn.execute("PRAGMA journal_mode=WAL")      # persistent, stored in the DB file
conn.execute("PRAGMA synchronous=NORMAL")    # per connection, safe with WAL
conn.execute("PRAGMA busy_timeout=30000")    # ms, equivalent to connect(timeout=30)

4. Take the write lock up front with BEGIN IMMEDIATE

This removes the read-then-write upgrade deadlock. Manage transactions explicitly by connecting with isolation_level=None, and retry with jitter on the remaining contention.

import random, time, sqlite3

def write(conn, sql, params=(), retries=6):
    for attempt in range(retries):
        try:
            conn.execute("BEGIN IMMEDIATE")
        except sqlite3.OperationalError as e:
            if "locked" not in str(e) and "busy" not in str(e):
                raise
            time.sleep(random.uniform(0, 0.05 * 2 ** attempt))
            continue
        try:
            conn.execute(sql, params)
            conn.execute("COMMIT")
            return
        except BaseException:
            conn.execute("ROLLBACK")
            raise
    raise RuntimeError("could not acquire write lock")

On Python 3.12 and later you can instead pass autocommit=True to sqlite3.connect() and issue BEGIN IMMEDIATE yourself.

See also  Playwright tracing zip file corrupted when terminating worker process prematurely

5. Use a single writer for write-heavy workloads

Workers compute and send results over a queue. One process owns the connection and commits in batches.

import sqlite3, multiprocessing as mp

def writer(q, db):
    conn = sqlite3.connect(db, timeout=30, isolation_level=None)
    conn.execute("PRAGMA journal_mode=WAL")
    batch = []
    while True:
        item = q.get()
        if item is None:
            break
        batch.append(item)
        if len(batch) >= 500:
            conn.execute("BEGIN IMMEDIATE")
            conn.executemany("INSERT INTO t(id, value) VALUES (?, ?)", batch)
            conn.execute("COMMIT")
            batch.clear()
    if batch:
        conn.execute("BEGIN IMMEDIATE")
        conn.executemany("INSERT INTO t(id, value) VALUES (?, ?)", batch)
        conn.execute("COMMIT")
    conn.close()

def work(x, q):
    q.put((x, x * x))

if __name__ == "__main__":
    q = mp.Queue(maxsize=5000)
    w = mp.Process(target=writer, args=(q, "/var/data/app.db"))
    w.start()
    procs = [mp.Process(target=work, args=(i, q)) for i in range(8)]

[p.start() for p in procs]

[p.join() for p in procs] q.put(None) w.join()

6. Keep transactions and cursors short

An unfinished SELECT keeps a read lock open. In rollback-journal mode it blocks commits, and in WAL mode it blocks checkpoints, so the -wal file grows without bound.

rows = conn.execute("SELECT ...").fetchall()   # drain the cursor, releases the lock

Never hold a transaction open across network calls, time.sleep, or a wait on another process’s result.

7. Use a non-fork start method if the parent must own connections

import multiprocessing as mp
if __name__ == "__main__":
    mp.set_start_method("spawn")   # or "forkserver"

spawn starts a clean interpreter, so no connection state is inherited. sqlite3.Connection cannot be pickled, so any attempt to pass one to a worker fails with an explicit error.

Why This Fails for Some Users

  • Default start method differs by platform and version: Linux uses fork before Python 3.14, which is where inherited-connection hangs occur. macOS (3.8+) and Windows use spawn. Python 3.14 changes the POSIX default to forkserver, so code that only worked because of fork semantics can start failing there.
  • Network and virtualized filesystems: NFS, SMB/CIFS, some FUSE mounts, and Docker bind mounts on macOS and Windows have unreliable or emulated locking. WSL2 paths under /mnt/c behave the same way. WAL requires shared memory (-shm) and does not work across hosts, so keep the database on a local Linux filesystem.
  • ORM and framework connection pools: SQLAlchemy engines created before the fork share pooled connections with the children. Call engine.dispose() in the child or use NullPool. Django needs django.db.connections.close_all() before forking.
  • PRAGMA locking_mode=EXCLUSIVE: It makes the first connection hold the lock indefinitely, so any second process blocks until it exits.
  • In-memory databases: :memory: is private to each process. Workers see empty databases rather than a lock, which is sometimes misdiagnosed as a locking bug.
  • Checkpoint starvation in WAL: Long-lived readers stop the WAL from being reset and the file keeps growing. Run PRAGMA wal_checkpoint(TRUNCATE); from a process with no open read transactions, and tune PRAGMA wal_autocheckpoint.
  • Crashed workers: A process killed while holding the write lock releases it at process exit, and SQLite rolls back the hot journal on the next open. A persistent hang after a crash usually means another live process, often a zombie worker or stale inherited descriptor, still has the file open. Check with lsof.
  • Old SQLite builds: Versions before 3.7.0 have no WAL. Check with python -c "import sqlite3; print(sqlite3.sqlite_version)".
  • Permissions: WAL mode needs write access to the database directory to create -wal and -shm, so a read-only directory or a mismatched user in a container causes unable to open database file or lock errors.
  • Write throughput ceiling: SQLite allows one writer at a time. If workers spend most of their time writing, more processes will not help, so batch writes through a single writer, or move to a client-server database when sustained concurrent writes are required.
See also  Fixing urllib3 ConnectionResetError(104, 'Connection reset by peer') during bulk API requests

Leave a Comment