Resolving Python multiprocessing deadlock with SQLite3 database locks

A Python multiprocessing deadlock with SQLite3 occurs when multiple worker processes attempt concurrent writes to a single SQLite database file using standard connection objects. SQLite uses file-level or page-level locks (depending on the journaling mode) that block concurrent write access, while Python’s multiprocessing spawn/fork semantics can clone locked database connections or lock-waiting states across process boundaries. When worker processes block indefinitely waiting for a database lock held by a parent or peer process, thread/process synchronization primitives stall, producing a fatal deadlock.

Root Cause Diagnosis

SQLite3 is designed for high read concurrency but strictly serializes write operations. When using multiprocessing, workers sharing a database path or connection instance trigger file-locking conflicts (database is locked errors or infinite blocks) because OS-level POSIX advisory locks do not survive fork() safely and WAL (Write-Ahead Logging) locks stall when process handles overlap. Furthermore, using default connection timeouts or improperly managed connection pools inside sub-processes causes child processes to wait indefinitely on stale lock states held by other workers.

Quick Fix

Enable Write-Ahead Logging (WAL) mode and set a explicit busy timeout on the SQLite connection within each worker process. Execute the following pragmas immediately upon instantiating every worker database connection:

import sqlite3

conn = sqlite3.connect("app.db", timeout=30.0)
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA busy_timeout=30000;")

Step-by-Step Resolution

Step 1: Isolate Connections to Worker Processes

Never pass an initialized sqlite3.Connection object across process boundaries via multiprocessing.Process(args=(conn,)) or shared memory. Initialize a dedicated connection inside each child worker’s execution body.

Step 2: Configure Per-Worker Connection Settings

Set WAL mode, synchronous checkpointing, and an extended busy timeout inside the worker function:

import multiprocessing as mp
import sqlite3
import time


def worker_task(data_chunk):
    # Establish a fresh connection per process with an extended timeout
    conn = sqlite3.connect("application.db", timeout=60.0)

    # Pragmas to allow concurrent reads and serialized background writes
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA busy_timeout=60000;")  # Wait up to 60 seconds
    conn.execute("PRAGMA synchronous=NORMAL;")

    try:
        with conn:
            conn.execute(
                "INSERT INTO metrics (timestamp, value) VALUES (?, ?)",
                (time.time(), data_chunk),
            )
    finally:
        conn.close()

Step 3: Implement Queue-Based Centralized Writer Architecture (Recommended)

For heavy parallel write workloads, eliminate multi-process file contention entirely by routing all database writes through a single dedicated worker thread/process using multiprocessing.Queue.

import multiprocessing as mp
import sqlite3

STOP_SIGNAL = "DONE"


def db_writer_process(queue: mp.Queue, db_path: str):
    """Sole process responsible for executing writes to SQLite."""
    conn = sqlite3.connect(db_path, timeout=60.0)
    conn.execute("PRAGMA journal_mode=WAL;")

    while True:
        data = queue.get()
        if data == STOP_SIGNAL:
            break

        query, params = data
        with conn:
            conn.execute(query, params)

    conn.close()


def worker_process(queue: mp.Queue, payload: tuple):
    """Worker process offloading database writes to the central queue."""
    # Perform intensive tasks...
    result_query = "INSERT INTO results (item, score) VALUES (?, ?)"
    queue.put((result_query, payload))


if __name__ == "__main__":
    write_queue = mp.Queue()
    writer = mp.Process(
        target=db_writer_process, args=(write_queue, "production.db")
    )
    writer.start()

    workers = [
        mp.Process(
            target=worker_process, args=(write_queue, (f"item_{i}", i * 10))
        )
        for i in range(10)
    ]

    for w in workers:
        w.start()
    for w in workers:
        w.join()

    write_queue.put(STOP_SIGNAL)
    writer.join()

Why This Fails for Some Users

NFS / Network File Systems: POSIX file locking (fcntl) in SQLite is notoriously unreliable on networked storage (NFS, SMB). Lock states are not propagated atomically, causing busy_timeout to fail and processes to hang indefinitely.
Linux fork Start Method Default (Pre-Python 3.14): On Unix-like systems using fork, child processes inherit open file descriptors from the parent. If a connection was opened in the parent process before calling mp.Process(), both processes share the underlying socket/file descriptor, corrupting internal lock state.
In-Memory Databases (:memory:): In-memory SQLite instances are distinct per process connection; attempting to access a shared :memory: database across multiple processes will fail or target isolated, non-shared DB instances.
WAL Mode File Permissions: Changing journal_mode to WAL creates -wal and -shm sidecar files in the same directory as the .db file. If worker processes run under different user accounts lacking write access to the directory (not just the .db file), WAL creation fails and reverts to rollback journaling locks.