Fix sqlite3 "database is locked" with concurrent writers

The problem

Two background workers writing to the same SQLite file produced:

sqlite3.OperationalError: database is locked

intermittently, and some counter updates were lost. Most drivers default busy_timeout to 0, so the first bit of contention becomes an immediate error.

What didn't work

A retry loop with a random sleep — under load it still fails, and if you swallow the exception the write is silently dropped.
Opening a new connection per statement — multiplies lock acquisition attempts and makes contention worse.
PRAGMA journal_mode=DELETE (the default rollback journal) — readers still block the writer and vice versa.

The fix

import sqlite3

conn = sqlite3.connect(
    "app.db",
    timeout=15,            # wait up to 15s for the write lock before erroring
    isolation_level=None,  # autocommit mode: you issue BEGIN/COMMIT explicitly
)
conn.execute("PRAGMA journal_mode=WAL")    # readers never block the writer
conn.execute("PRAGMA busy_timeout=15000")  # applies to the driver's lock waits

conn.execute("BEGIN IMMEDIATE")  # take the RESERVED lock now, not at COMMIT
try:
    conn.execute("UPDATE counters SET n = n + 1 WHERE id = ?", (counter_id,))
    conn.execute("COMMIT")
except sqlite3.OperationalError:
    conn.execute("ROLLBACK")
    raise

Why it works

WAL mode lets readers and the writer proceed concurrently, busy_timeout makes the driver wait for the lock instead of throwing, and BEGIN IMMEDIATE acquires the write lock at transaction start so contention is resolved deterministically at BEGIN rather than surfacing as a surprise failure at COMMIT.

Continue reading — $1/month

The full fix, verbatim, plus every other solution in the feed.