SQLite Multi-Process Writes: Queue, One Writer, or Postgres

SQLite allows many readers and one writer. That is not a slogan; it is the lock. Two processes that both need to COMMIT at the same moment will take turns. With WAL and a busy timeout they will usually take turns politely. With four gunicorn workers, a sidecar, and a cron, they will eventually collide, retry, and lose a request. The fix is architectural, not a larger timeout.

You have three honest options: a queue so only one process writes, a dedicated writer process the others talk to, or Postgres. Mixing them half-way is how SQLITE_BUSY becomes a weekly page.

Why many writers fail even in WAL

WAL removed the reader-versus-writer stall. It did not create concurrent write transactions. The write lock is still exclusive. BEGIN IMMEDIATE from two processes: one proceeds, the other waits up to busy_timeout. If the winner holds the transaction while it does HTTP, the loser expires. If both use DEFERRED transactions, they can deadlock into BUSY at COMMIT after doing real work.

Threads in one process share a connection cache if you let them. The standard pattern is one connection per thread, short transactions, IMMEDIATE on writes, and no connection sharing across threads. That works until you add a second process. Then you have two caches, two writers, and no coordinator.

systemd started two copies of the unit because a deploy script raced. A second Python interpreter from a debug shell. Celery and the web app both writing jobs. These are the usual offenders on a VPS.

Option 1: queue the writes

Keep SQLite. Let every process read. Send writes through a queue the single writer drains. The queue can be Redis, a Unix socket, or even a directory of JSON files if the rate is low. The invariant is that INSERT, UPDATE, and DELETE happen in one process.

import json
import sqlite3
import time

def writer_loop(db_path, queue):
    conn = sqlite3.connect(db_path, timeout=5.0, isolation_level=None)
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA busy_timeout = 5000')
    while True:
        job = queue.get()
        if job is None:
            break
        conn.execute('BEGIN IMMEDIATE')
        try:
            conn.execute(
                'INSERT INTO events(account_id, kind, payload) VALUES (?, ?, ?)',
                (job['account_id'], job['kind'], json.dumps(job['payload'])),
            )
            conn.execute('COMMIT')
        except Exception:
            conn.execute('ROLLBACK')
            raise

Readers elsewhere open their own connections read-only or with a busy timeout and never write. This is the pattern used by many small web apps that outgrew in-request writes without outgrowing the file.

A directory queue is valid at low volume: writers drop a file into /var/lib/myapp/queue, the writer process fsyncs inserts, then deletes the file. You now have two directories to back up. Prefer an in-memory queue only when the writer dies with the web process, so you cannot lose jobs across a restart.

Option 2: one writer process, RPC for mutations

If the workers are already separate, run a tiny write service on 127.0.0.1 that owns the database. Workers send HTTP or a Unix socket request. The service serializes with a mutex around transactions. Reads can still go direct to the file in WAL mode, or through the service if you want a single code path.

Bind to localhost only. L3/L4 DDoS filtering on the public interface is irrelevant if you accidentally expose a write socket on 0.0.0.0. Unix sockets with mode 770 and a shared group are cleaner than TCP for same-host workers.

[Service]
User=myapp
Group=myapp
ExecStart=/usr/local/bin/myapp-writer --db /var/lib/myapp/data/app.db --sock /run/myapp/write.sock
RuntimeDirectory=myapp
UMask=0007

Workers connect to /run/myapp/write.sock. The writer is the only process that opens the database for write. Everything else uses mode=ro URIs for SELECT. If the writer dies, mutations fail closed instead of two workers silently becoming writers.

Option 3: graduate to Postgres

Graduate when any of these are true:

• You need two machines writing.
• The queue is now a product you did not want to build.
• Workers spend measurable time waiting on BUSY after you shortened transactions.
• You want isolation levels, row-level locks, or LISTEN to wake workers.

Do not graduate because a blog post said SQLite is not for production. Do graduate because you measured the lock. Export, load, keep the app's SQL as boring as you can, and turn the SQLite file into an archive.

A hybrid that fails: three processes writing "because WAL is fine" plus a 30 second busy timeout plus retries that multiply load. That system looks healthy until a slow VACUUM or a backup lock meets a traffic spike.

Serialize writes or change engines; do not hope WAL will make many writers safe. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.