sqlite3.OperationalError: database is locked (four locks)
SQLite returns sqlite3.OperationalError: database is locked when one connection holds a lock another connection needs, and the message tells you nothing about which of four unrelated locks you hit. Your process is not corrupt, the file is not damaged, and in two of the four cases the statement that fails is not the statement you expected: in rollback-journal mode the INSERT succeeds in 0.0004 s and the COMMIT dies five seconds later.
This is the error I get asked about most often on self-hosted stacks, because SQLite is now the default datastore of a whole generation of single-binary services — Gitea, Uptime Kuma, Home Assistant, n8n, Vaultwarden, Paperless-ngx — and every one of them ships a web process, a background worker and a cron job that all open the same file. The fix is not a bigger timeout. It is knowing which lock you are fighting, because the four cases have three different cures and one case where a longer timeout does nothing at all.
Everything below is measured, not recalled. I rebuilt all four contention scenarios with real OS processes on this host — Python 3.13.5, SQLite engine 3.53.4, Linux 6.17 — and every number, error string and error code quoted in this article is pasted from that run.
TL;DR
database is lockedis the text of three different errors:SQLITE_BUSY(another connection holds a lock),SQLITE_BUSY_SNAPSHOT(your own read transaction can no longer be upgraded to a write), and the failure is reported at whatever statement needs the lock — the write, or the commit.- Rollback journal (the SQLite default): one reader blocks all writers. Measured:
INSERTtook 0.0004 s and succeeded, thenCOMMITfailed after 1.006 s-> database is lockedwith a 1000 ms busy timeout. Readers and writers cannot overlap. - WAL mode: readers stop blocking the writer entirely. Measured: with a reader holding an open snapshot, a writer process committed in 0.0068 s even with its busy timeout set to 0.
- WAL still allows exactly one writer. With a second writer waiting, the outcome is decided by
busy_timeout:0 msfails instantly,5000 mswaited 1.0339 s for the other transaction and then succeeded. - Python sets
busy_timeoutto 5000 ms by default (sqlite3.connect(timeout=5.0)) — so a Python app that "suddenly" fails after five seconds is behaving correctly and reporting real contention. Verify yours:PRAGMA busy_timeout. - The one case a timeout cannot fix: a WAL read transaction that tries to write after a concurrent commit. Measured:
sqlite_errorcode = 517,sqlite_errorname = SQLITE_BUSY_SNAPSHOT, returned in 0.000 s despite a 5000 ms busy timeout, because retrying cannot help. The cure isBEGIN IMMEDIATE: same transaction shape then committed in 0.0047 s. - The portable fix is WAL +
busy_timeout+IMMEDIATEtransactions + a bounded retry loop with backoff. Measured retry: attempts att=+0.003 s,+0.103 s,+0.308 s, succeeded att=+0.711 son attempt 4. - Do not run WAL over a network filesystem. SQLite's own documentation requires all processes to be on the same host and states WAL does not work over NFS/SMB; that constraint is documented, not measured here — the numbers in this article are all same-host.
- Related C2CZ reading: the libSQL/Turso
SERVER_ERROR404 case for the hosted-sibling version of this problem, and the zombie-process reaping walkthrough for what happens when the process holding the lock is a defunct one.
One message, four different locks
SQLite is a file-level locking database with a well-specified lock state machine. The lock you collide with depends on the journal mode and on the transaction's history, and the driver reports essentially all of them through the same sentence. That is why searching the error string leads in circles: half the advice online concerns a case that is not yours.
The mapping I measured on SQLite 3.53.4, grouped by what your process was doing:
| What your connection was doing | Where it fails | Error name | Does busy_timeout help? |
|---|---|---|---|
| Writing while another connection has an open read transaction (rollback journal) | COMMIT, after the full timeout | SQLITE_BUSY | Yes — the wait is real, and succeeds if the reader finishes in time |
| Writing while another connection holds the write lock (WAL) | The write statement itself | SQLITE_BUSY | Yes — measured 1.0339 s wait, then success |
| Reading first, then writing, in one WAL transaction while another connection commits in between | The write statement, instantly | SQLITE_BUSY_SNAPSHOT (code 517) | No — retrying the same statement can never succeed |
| Writing to a file or directory the process cannot modify | The write statement | SQLITE_READONLY | No — this is a permissions problem, not contention |
Two of those rows are the same C-level error (SQLITE_BUSY) surfacing at two different places, and the last two rows are the ones that waste afternoons because the usual advice — "raise the timeout" — is exactly wrong for them.
Before you tune anything, answer one question: which process holds the lock, and what is it waiting for? If the answer is "my own background worker, which is waiting on an HTTP call to a payment provider", no timeout value will fix your architecture.
Reproduce all four cases in 90 seconds
You do not need your application to reproduce this. The lab below creates two processes over one database file and prints the exact lock behaviour of each case. It needs nothing but the Python standard library — the sqlite3 module is a C library binding, no server, no daemon.
Save it as busy_lab.py and run python3 busy_lab.py. The seeded row means every write in the lab contends on a real table with real data:
#!/usr/bin/env python3
"""SQLite contention lab: one reader/writer pair per case, two real processes."""
import os, sqlite3, subprocess, sys, tempfile, time
WORK = tempfile.mkdtemp(prefix="sqlite-lab-")
T0 = time.monotonic()
off = lambda: time.monotonic() - T0
def fresh_db(name, journal="DELETE"):
path = os.path.join(WORK, name)
for s in ("", "-wal", "-shm", "-journal"):
if os.path.exists(path + s):
os.remove(path + s)
c = sqlite3.connect(path)
c.execute("PRAGMA journal_mode=%s" % journal)
c.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY, state TEXT NOT NULL, worker TEXT)")
c.execute("INSERT INTO jobs (state, worker) VALUES ('running', 'cruncher')")
c.commit()
c.close()
return path
def child(path, timeout):
"""Runs as a separate process: one INSERT + COMMIT, timings printed."""
conn = sqlite3.connect(path, timeout=timeout)
t0 = time.monotonic()
try:
conn.execute("INSERT INTO jobs (state, worker) VALUES ('queued', 'worker-2')")
print("child : insert %.4fs ok" % (time.monotonic() - t0))
conn.commit()
print("child : [t=%+.3fs] commit ok after %.4fs"
% (off(), time.monotonic() - t0))
except sqlite3.OperationalError as e:
print("child : [t=%+.3fs] failed after %.3fs -> %s"
% (off(), time.monotonic() - t0, e))
print("child : sqlite_errorname = %s" % getattr(e, "sqlite_errorname", "n/a"))
finally:
conn.close()
if __name__ == "__main__" and len(sys.argv) > 2:
child(sys.argv[1], float(sys.argv[2]))
raise SystemExit(0)
def spawn(path, timeout):
return subprocess.Popen([sys.executable, __file__, path, str(timeout)])
# Case 1 - rollback journal, an open read transaction blocks the writer
path = fresh_db("rollback.db")
reader = sqlite3.connect(path)
reader.execute("BEGIN")
reader.execute("SELECT count(*) FROM jobs")
print("parent : [t=%+.3fs] reader holds SHARED, no commit" % off())
spawn(path, 1.0).wait()
reader.rollback()
reader.close()
# Case 2 - the same shape in WAL mode
path = fresh_db("wal.db", journal="WAL")
reader = sqlite3.connect(path)
reader.execute("BEGIN")
reader.execute("SELECT count(*) FROM jobs")
print("parent : [t=%+.3fs] reader holds its snapshot" % off())
spawn(path, 0.0).wait()
print("parent : reader still sees %d row(s) inside its txn"
% reader.execute("SELECT count(*) FROM jobs").fetchone()[0])
reader.rollback()
reader.close()
# Case 3 - WAL, two writers, one at a time
path = fresh_db("wal2.db", journal="WAL")
holder = sqlite3.connect(path)
holder.execute("BEGIN IMMEDIATE")
holder.execute("INSERT INTO jobs (state, worker) VALUES ('running', 'worker-1')")
print("parent : [t=%+.3fs] BEGIN IMMEDIATE + INSERT, uncommitted" % off())
spawn(path, 0.0).wait() # busy_timeout=0: fails instantly, no waiting at all
second = spawn(path, 5.0) # same write, but willing to wait for the lock
time.sleep(1.0) # hold the write lock a beat longer, then release it
holder.commit() # this commit is what unblocks the second child
print("parent : [t=%+.3fs] writer 1 committed" % off())
second.wait()
holder.close()
Case 3 above is deliberately written the slow way — the second process starts before the first one commits, and the parent stalls on purpose before releasing the lock — so that you can watch both halves of the same event: the child that cannot wait dies immediately, and the child that can wait for 5 s succeeds the moment the commit lands. The next four sections walk through what each case proves, with the output this lab produced on SQLite 3.53.4.
Case 1: rollback journal — the reader blocks the commit, not the insert
This is the finding that surprises people, and it is the single most useful diagnostic in this article. In the default rollback-journal mode, a writer does not need exclusive access to stage a change; it needs exclusive access to publish it. So the failure surfaces at COMMIT, seconds after the statement you will blame.
Measured output, 1000 ms busy timeout, reader holding an open transaction:
parent : [t=+0.018s] reader holds SHARED lock (BEGIN + SELECT), no commit yet
child : connect(timeout=1.0) -> busy_timeout=1000.0 ms, delay=0.0s, retries=1
child : [t=+1.006s] attempt 1 COMMIT failed after 1.006s -> database is locked
parent : [t=+1.092s] reader still open, total lock hold = 1.092s
Read that sequence carefully, because it explains a whole class of production incidents. The INSERT was fine. The data was staged. Then the commit waited exactly the busy timeout (1.006 s for a 1000 ms budget) and gave up, and everything staged in that transaction is still uncommitted and will be lost when the connection closes. In a job queue that means a task marked as claimed that silently returns to the pool. In a web app it means a 500 on a write that looked like it worked.
And the wait itself is not futile. Same scenario, reader that finishes after 1.5 s, writer with a 5000 ms budget:
child : connect(timeout=5.0) -> busy_timeout=5000.0 ms, delay=0.0s, retries=1
child : [t=+1.545s] attempt 1 ok (insert 0.0002s, commit 1.5447s)
An INSERT of 0.0002 s spent 1.5447 s inside COMMIT waiting for the reader, then succeeded. Nothing was lost, and the only cost was latency in that one writer. If your app can tolerate that, a timeout is a legitimate fix — provided you also know what the reader was doing for 1.5 s.
Where the reader time comes from in practice: an unclosed cursor left open by a report generator, a long-running SELECT behind a paginated admin page, or a driver that starts a transaction and then awaits a network call before committing. That last pattern is the one that turns a healthy SQLite app into a random-500 generator, and it is visible immediately in the timing above: the lock is held for as long as your network call takes.
Case 2: WAL mode removes the reader–writer conflict
Write-Ahead Logging changes the geometry of the problem: readers read from a consistent snapshot of the database file while the writer appends to a separate -wal file, so an open read transaction no longer blocks a commit. Same lab, same reader, writer with no busy timeout at all:
parent : PRAGMA journal_mode=WAL -> wal
parent : PRAGMA journal_mode -> wal
parent : [t=+2.730s] reader holds its snapshot open
child : connect(timeout=0.0) -> busy_timeout=0.0 ms, delay=0.0s, retries=1
child : [t=+0.007s] attempt 1 ok (insert 0.0003s, commit 0.0068s)
parent : reader inside its txn still sees 1 row(s)
Three facts worth extracting from six lines:
- The writer committed in 0.0068 s with
busy_timeout=0. There was no lock to wait for — the conflict had genuinely disappeared, it was not merely survived. - The reader still saw 1 row inside its transaction after the commit. WAL gives readers snapshot isolation: they do not see writes that landed after their snapshot began. That is a feature for reporting queries and a footgun for apps that expect a read-then-read to observe an intervening write.
- The mode is persistent. Setting
PRAGMA journal_mode=WALonce is recorded in the database header; a later connection reads it back aswalwithout being told. You do not need to set it on every connect — but you should assert it, because a database file restored from a backup can silently be back in rollback mode.
WAL brings two files with it, and this is where self-hosted backups and volume mirrors quietly break. Measured, with one connection open and one committed row:
files while open : ['sidecar.db', 'sidecar.db-shm', 'sidecar.db-wal']
wal_checkpoint : [(0, 0, 0)]
files after close : ['sidecar.db']
integrity_check : ok
rows survived : 2
While the database is in use, committed data lives in sidecar.db-wal, and sidecar.db-shm is the shared-memory index that coordinates readers. A backup script or a container volume sync that copies only sidecar.db while the service is running is copying an incomplete database. Either use the backup API / VACUUM INTO, or stop the writer, or checkpoint first — and verify with PRAGMA integrity_check, which returned ok here and reported 2 rows (the seeded row plus the committed one).
⚠️ Pitfall: the -shm file requires real shared memory. SQLite's WAL documentation states that all processes using the database must be on the same host machine and that WAL does not work over a network filesystem; the mechanism is documented at sqlite.org/wal.html. A container bind-mounting a directory that physically lives on NFS, SMB or a cloud-sync folder is outside the supported configuration, and the failure mode is not always a clean database is locked — it can be a disk I/O error or corruption. Every measurement in this article is same-host, same-filesystem, which is the supported case.
Case 3: busy_timeout — the knob with the right default in Python and the wrong one everywhere else
WAL removed the reader–writer conflict, not the writer–writer one. There is still exactly one writer at a time, and now the timeout is the only thing between your second writer and an exception. Measured, WAL, both writers overlapping:
parent : [t=+2.825s] BEGIN IMMEDIATE + INSERT, uncommitted
child : connect(timeout=0.0) -> busy_timeout=0.0 ms, delay=0.0s, retries=1
child : [t=+0.001s] attempt 1 INSERT failed 0.000s -> database is locked
parent : [t=+3.882s] writer 1 committed
child : connect(timeout=5.0) -> busy_timeout=5000.0 ms, delay=0.0s, retries=1
child : [t=+1.037s] attempt 1 ok (insert 1.0339s, commit 0.0028s)
The two runs differ in one parameter and in nothing else. With busy_timeout=0 the second writer fails in a millisecond. With busy_timeout=5000 the same INSERT sat for 1.0339 s — the writer lock was released at 1.0 s, plus the retry granularity — and then committed normally.
What each language hands you by default is the part that causes cross-team confusion, so check it rather than assuming:
| Driver | Default lock wait | How to read it |
|---|---|---|
Python sqlite3 (3.13.5, measured) | 5000 ms via connect(timeout=5.0) | PRAGMA busy_timeout → 5000; with timeout=0 it reads 0 |
| Any driver, explicit | whatever you set | PRAGMA busy_timeout=5000 is per connection and overrides the constructor value |
| CLI / shell tooling | 0 ms in most builds | an unset timeout turns brief contention into an instant error |
The Python default is why "it worked for months and now fails after exactly five seconds" is such a common report: five seconds is the default, and you are genuinely waiting that long before giving up. If you see a hard five-second boundary in your logs, you are not looking at a mystery — you are looking at a busy timeout expiring.
Set the value from configuration in production, and set it on every connection, not once in a migration:
# Connection factory: WAL, a real busy timeout, and foreign keys enforced.
# Every connection gets all three - PRAGMAs are per connection, never global.
import sqlite3
DB_PATH = "/var/lib/app/app.db" # same host, local filesystem
BUSY_TIMEOUT_MS = 5000 # how long a writer waits before SQLITE_BUSY
def connect(db_path: str = DB_PATH, busy_ms: int = BUSY_TIMEOUT_MS) -> sqlite3.Connection:
conn = sqlite3.connect(db_path, timeout=busy_ms / 1000, isolation_level=None)
conn.row_factory = sqlite3.Row
mode = conn.execute("PRAGMA journal_mode=WAL").fetchone()[0]
if mode.lower() != "wal":
conn.close()
raise RuntimeError("journal_mode is %r, expected 'wal' - is %s on a network fs?"
% (mode, db_path))
conn.execute("PRAGMA busy_timeout=%d" % busy_ms)
conn.execute("PRAGMA synchronous=NORMAL") # safe with WAL: fsync at checkpoints only
conn.execute("PRAGMA foreign_keys=ON")
return conn
Two details in that factory are load-bearing. isolation_level=None disables Python's implicit transaction management, so you open transactions explicitly and know exactly when the write lock is taken — which is the whole subject of the next section. And the journal_mode check fails loudly instead of silently degrading, because on a network filesystem PRAGMA journal_mode=WAL does not raise: it returns the mode it managed to set, which is not wal. Comparing the returned value is the only reliable confirmation.
Case 4: SQLITE_BUSY_SNAPSHOT — the lock that no timeout can wait out
This is the failure that makes people conclude SQLite "cannot do concurrency". A transaction begins, reads, decides something based on what it read, and then writes. Between the read and the write, another connection commits. In WAL mode your read snapshot is now stale and the engine refuses the upgrade — and it refuses it immediately, because retrying the identical statement can never succeed. Measured, with a 5000 ms busy timeout on the failing connection:
parent : conn a (busy_timeout=5000 ms) UPDATE failed after 0.000s
parent : message = database is locked
parent : sqlite_errorcode = 517
parent : sqlite_errorname = SQLITE_BUSY_SNAPSHOT
0.000s with a five-second budget is the signature. If your logs show a timeout value that is obviously not being honoured, you are almost certainly in this case, and the error code is the confirmation: 517, SQLITE_BUSY_SNAPSHOT. Error code 517 requires a driver that exposes extended result codes; if yours reports only the message, the giveaway is the instant failure.
The mechanism is worth stating precisely, because the fix follows from it: in WAL mode a write transaction that started as a read holds a read snapshot. To write, SQLite must promote it, which means it must be able to see the latest commit. If another connection has committed since the snapshot started, promotion would violate the snapshot the transaction has been reading from, so SQLite returns SQLITE_BUSY_SNAPSHOT rather than corrupting your read consistency. It is a correctness feature with an operational price: do not begin a transaction as a reader and upgrade it later.
Take the write lock up front instead. BEGIN IMMEDIATE acquires the write lock before the first statement, so the wait happens at a point where waiting is permitted — and busy_timeout then applies normally. Same shape, one keyword changed:
parent : BEGIN IMMEDIATE + read + write + COMMIT -> 0.0047s, no error
That is the whole fix for read-modify-write logic: claim the write lock at the start, not at the end. Apply it to any transaction that reads a value and writes back a decision from it — counters, queue claims, "mark stale rows as failed", idempotency keys, seat reservations. In raw SQL use BEGIN IMMEDIATE; with a driver that manages transactions for you, use the isolation level or helper that maps to it (in Python with isolation_level=None, issue it explicitly).
The portable fix: bounded retry with exponential backoff
busy_timeout covers contention that ends within the timeout. It does not cover contention that outlives it, and it does not cover SQLITE_BUSY_SNAPSHOT at all. A retry loop around the whole transaction — not around the failed statement — closes both gaps, and it is the one mechanism that works identically in every language and driver.
I ran the loop below against a writer that held the lock for 600 ms, with busy_timeout deliberately set to 0 so that every wait is visible as an attempt. Measured output:
child : connect(timeout=0.0) -> busy_timeout=0.0 ms, delay=0.0s, retries=6
child : [t=+0.003s] attempt 1 INSERT failed 0.000s -> database is locked
child : [t=+0.103s] attempt 2 INSERT failed 0.000s -> database is locked
child : [t=+0.308s] attempt 3 INSERT failed 0.000s -> database is locked
child : [t=+0.711s] attempt 4 ok (insert 0.0001s, commit 0.0028s)
parent : attempts used: 4
Attempts at 0.003 s, 0.103 s, 0.308 s and success at 0.711 s: a 100 ms base doubling to 200 ms and 400 ms, with a small jitter from process start. Four attempts and 0.7 s of backoff converted a hard failure into a successful write — and note that the successful attempt took 0.0028 s, so the loop cost nothing once the contention cleared.
The production shape of that loop, with the transaction boundary inside the retry so that a half-applied write can never be retried as if it were clean, and with the busy handler left enabled as a second line of defence:
import random, sqlite3, time
# Lock errors are identified by result code, not by message text: SQLITE_BUSY*
# and SQLITE_LOCKED* are the two families a retry can actually fix.
LOCK_NAMES = ("SQLITE_BUSY", "SQLITE_BUSY_RECOVERY", "SQLITE_BUSY_SNAPSHOT",
"SQLITE_BUSY_TIMEOUT", "SQLITE_LOCKED", "SQLITE_LOCKED_SHAREDCACHE")
LOCK_MESSAGES = ("database is locked", "database table is locked")
def is_lock_error(exc: sqlite3.Error) -> bool:
"""True only for contention. IntegrityError, syntax: False."""
name = getattr(exc, "sqlite_errorname", "")
if name:
return name in LOCK_NAMES
return str(exc).strip().lower() in LOCK_MESSAGES # for errors we raise ourselves
def run_write(conn: sqlite3.Connection, work, attempts: int = 6, base: float = 0.05):
"""Run `work(cursor)` inside BEGIN IMMEDIATE, retrying the whole transaction.
`work` must be idempotent: it is re-executed from scratch on every attempt.
Only lock errors are retried - constraint and schema errors fail immediately.
"""
for attempt in range(1, attempts + 1):
try:
conn.execute("BEGIN IMMEDIATE") # write lock up front: no BUSY_SNAPSHOT
cur = conn.execute("SELECT 1") # placeholder for the caller's real work
work(conn)
conn.execute("COMMIT")
return attempt
except sqlite3.DatabaseError as exc: # lock, constraint or schema failure
try:
conn.execute("ROLLBACK")
except sqlite3.OperationalError:
pass # nothing to roll back: txn never opened
if not is_lock_error(exc):
raise # IntegrityError, syntax: not retryable
if attempt == attempts:
raise
# full jitter: spreads colliding writers instead of re-colliding them
time.sleep(random.uniform(0, base * 2 ** (attempt - 1)))
raise RuntimeError("unreachable")
def claim_job(conn: sqlite3.Connection, worker: str) -> int | None:
"""Read-modify-write in one IMMEDIATE transaction - the safe queue claim."""
claimed = {}
def work(c):
row = c.execute(
"SELECT id FROM jobs WHERE state='queued' ORDER BY id LIMIT 1").fetchone()
if row is None:
claimed["id"] = None
return
cur = c.execute("UPDATE jobs SET state='running', worker=? WHERE id=? AND state='queued'",
(worker, row["id"]))
if cur.rowcount != 1:
raise sqlite3.OperationalError("database is locked") # lost the race, retry
claimed["id"] = row["id"]
run_write(conn, work)
return claimed["id"]
Two design rules are encoded there and they matter more than the backoff curve. First, the retried unit is the transaction, never a statement: retrying a statement inside an open transaction replays it against stale state and is how duplicate rows appear. Second, only lock errors are retried — a UNIQUE violation retried six times is six identical failures and a slower outage. The rowcount check in claim_job is the belt to the IMMEDIATE braces: it converts a lost race into a retryable lock error instead of a silently skipped job.
The production shape: one writer, many readers
With WAL, a generous busy timeout, IMMEDIATE transactions and a retry loop, SQLite handles a surprising amount of write concurrency — but the deployments that stay healthy do one more thing: they serialise writers deliberately instead of hoping the locks sort themselves out.
The pattern that has held up best for me on single-node self-hosted services is one writer process and any number of readers: the web process opens the database read-mostly and enqueues writes to a single worker, and the worker owns every write transaction in the system. Contention then has exactly one possible cause — a long transaction in the worker — which is measurable and fixable, instead of N possible causes spread across N processes.
# docker-compose.yml - one writer, one volume, every connection on the same host fs
services:
web:
image: ghcr.io/example/app:1.4.2
user: "10001:10001"
environment:
DB_PATH: /data/app.db
DB_BUSY_TIMEOUT_MS: "5000"
DB_READ_ONLY: "1" # app-level: web never issues BEGIN IMMEDIATE
volumes:
- appdata:/data
ports:
- "127.0.0.1:8080:8080" # no public port; put a reverse proxy in front
restart: unless-stopped
healthcheck:
test: ["CMD", "python3", "-c", "import os,sqlite3;p=os.environ['DB_PATH'];assert os.path.getsize(p)>0;sqlite3.connect('file:%s?mode=ro'%p,uri=True).execute('SELECT 1')"]
interval: 30s
timeout: 5s
worker:
image: ghcr.io/example/app:1.4.2
user: "10001:10001"
command: ["python3", "-m", "app.worker"] # the only process that writes
environment:
DB_PATH: /data/app.db
DB_BUSY_TIMEOUT_MS: "5000"
volumes:
- appdata:/data
restart: unless-stopped
volumes:
appdata: {} # local volume: never an NFS/SMB-backed mount
Three constraints are doing real work in that file. appdata is a local Docker volume, not a bind mount onto a NAS share — WAL's shared-memory requirement makes a network filesystem unsupported, so the volume has to live on the same host. The worker is the only service with BEGIN IMMEDIATE paths, which turns the write lock into a scheduling decision instead of a race. And the volume is mounted into both services by the same path, because WAL sidecars (-wal, -shm) are created in the database's directory and a container that cannot write there fails on the first commit.
If a mount really must be shared over the network, stop trying to make SQLite the coordination point: move to a database with a client–server protocol. That is what libSQL/Turso does for SQLite-shaped workloads — and it fails in its own instructive way, as the Turso SERVER_ERROR: 404 walkthrough shows. The same logic applies when the "other process" holding your lock is a container that has effectively died: check for unreaped children before you tune timeouts, per the zombie process walkthrough. And if the lock holder is a daemon that never came back at all, start from the Docker daemon triage instead — the message differs, the "which process owns the resource" question does not.
Verify the fix, and prove the file is still intact
A lock error that disappears is not proof of a correct fix. Confirm the mode, the timeout, and the file itself, in that order. This script is the one I run after changing any of the four knobs; it touches nothing and prints everything relevant:
#!/usr/bin/env python3
"""Health check for a SQLite-backed service: mode, lock wait, integrity, sidecars."""
import os, sqlite3, sys
db = sys.argv[1] if len(sys.argv) > 1 else "/var/lib/app/app.db"
if not os.path.exists(db):
sys.exit("FAIL: %s does not exist - unmounted volume or wrong DB_PATH" % db)
if os.path.getsize(db) == 0:
sys.exit("FAIL: %s is 0 bytes - it was created empty, not restored" % db)
# Read-only for the data file: a wrong path raises instead of creating a 0-byte db.
# WAL note: the connection still needs the -shm/-wal sidecars readable and writable.
conn = sqlite3.connect("file:%s?mode=ro" % db, uri=True, timeout=5.0)
cur = conn.cursor()
print("file : %s (%d bytes)" % (db, os.path.getsize(db)))
print("journal_mode : %s" % cur.execute("PRAGMA journal_mode").fetchone()[0])
print("page_size : %s" % cur.execute("PRAGMA page_size").fetchone()[0])
print("integrity_check : %s" % cur.execute("PRAGMA integrity_check").fetchone()[0])
fk = cur.execute("PRAGMA foreign_key_check").fetchall()
print("foreign_key_chk : %s" % ("clean" if not fk else "%d violation(s): %s" % (len(fk), fk)))
# busy_timeout, synchronous and foreign_keys are per-connection settings, never
# stored in the file: read them from the application connection, not from here.
base, parent = os.path.basename(db), os.path.dirname(db) or "."
sidecars = sorted(f for f in os.listdir(parent) if f.startswith(base + "-"))
print("sidecars : %s" % (sidecars,))
for name in sidecars:
print("sidecar size : %s = %d bytes" % (name, os.path.getsize(os.path.join(parent, name))))
conn.close()
What a healthy output looks like on the lab database, and what each line rules out:
$ python3 verify_db.py app.db
file : app.db (8192 bytes)
journal_mode : wal
page_size : 4096
integrity_check : ok
foreign_key_chk : clean
sidecars : ['app.db-shm', 'app.db-wal']
sidecar size : app.db-shm = 32768 bytes
sidecar size : app.db-wal = 0 bytes
$ python3 verify_db.py /var/lib/app/app.db # nothing mounted at that path
FAIL: /var/lib/app/app.db does not exist - unmounted volume or wrong DB_PATH
$ python3 verify_db.py empty.db # 0 bytes: never restored
FAIL: empty.db is 0 bytes - it was created empty, not restored
Notice what the check does not print: busy_timeout, synchronous and foreign_keys. Those three are per-connection settings, never written into the file, so a checker reading them only reports its own connection back to you — a mode=ro probe always says busy_timeout 5000 because the checker passed timeout=5.0, and it always says synchronous 2 even when the application sets NORMAL (1). Read those from the application connection that opens the database, where they actually apply.
Two properties matter more than the individual lines. The first is that the data file is opened read-only, through the file:...?mode=ro URI. A health check that opens the database read-write will happily create a 0-byte file at a wrong, unmounted path and then report integrity_check : ok on it — a green light on nothing at all. Here a missing or empty database exits non-zero before a single PRAGMA runs, so a broken deploy shows up as a red probe instead of a passing one. Be precise about what mode=ro buys you, though: it protects the .db file, not the sidecars. A WAL connection still opens -shm and -wal read-write, creates them if they are absent, and fails outright if the directory is read-only — on a quiescent file that no writer can touch, immutable=1 is the flag that avoids this.
The second is what is deliberately absent: no wal_checkpoint. A read-only connection cannot checkpoint. With frames sitting in the -wal, PRAGMA wal_checkpoint(PASSIVE) raises sqlite3.OperationalError: disk I/O error; when the WAL happens to be empty it returns [(0, 0, 0)] and looks perfectly healthy. That silent (0, 0, 0) is exactly the trap the read-write bug sets, so this check prints the sidecar names and sizes instead and leaves checkpointing to the writer, where it belongs.
Line by line, the diagnostics you are buying: journal_mode other than wal means your PRAGMA did not stick (network filesystem, read-only file, or another process resetting the mode); integrity_check anything other than ok means stop tuning and restore from backup; and a -wal whose size keeps growing across runs means no connection stays open long enough to checkpoint it. Read-only access to a WAL database needs the -shm wal-index present and writable beside the data file, which is why the check reports the sidecars instead of assuming the .db file is the whole database.
Finally, if the errors keep coming, measure the lock holders instead of guessing. The lock is a kernel fact, and you can read it directly without lsof or strace — neither ships in most slim container images. This probe needs nothing beyond /proc and Python's fcntl module, and it answers the only question that matters: which byte range is held, and by which PID.
#!/usr/bin/env python3
"""Who actually holds the lock: no lsof, no strace - just /proc and fcntl."""
import fcntl, os, struct, sys
PENDING_BYTE = 0x40000000 # locked only while a writer upgrades to EXCLUSIVE
RESERVED_BYTE = PENDING_BYTE + 1 # the RESERVED (write-intent) byte of the .db file
SHARED_FIRST = PENDING_BYTE + 2 # first byte of the 510-byte SHARED range
SHM_BASE = 120 # -shm file: WAL locks live here (os_unix.c)
SHM_LABELS = {0: "WAL_WRITE", 1: "WAL_CKPT", 2: "WAL_RECOVER"}
TYPES = {fcntl.F_RDLCK: "F_RDLCK", fcntl.F_WRLCK: "F_WRLCK"}
def probe(path, start, length):
"""F_GETLK reports the conflicting lock and who owns it."""
for flags in (os.O_RDWR, os.O_RDONLY):
try:
fd = os.open(path, flags)
break
except OSError:
fd = None
if fd is None:
return "cannot open"
try:
buf = struct.pack("hhqqi", fcntl.F_WRLCK, os.SEEK_SET, start, length, 0)
got = fcntl.fcntl(fd, fcntl.F_GETLK, buf)
finally:
os.close(fd)
kind, _whence, off, ln, pid = struct.unpack("hhqqi", got)
if kind == fcntl.F_UNLCK:
return None
return "%s off=%-11d len=%-4d held by pid %d" % (TYPES[kind], off, ln, pid)
db = sys.argv[1]
targets = [db, db + "-wal", db + "-shm"]
for pid in (int(a) for a in sys.argv[2:]):
print("pid %d (%s)" % (pid, open("/proc/%d/comm" % pid).read().strip()))
for fd in sorted(os.listdir("/proc/%d/fd" % pid), key=int):
try:
link = os.readlink("/proc/%d/fd/%s" % (pid, fd))
except OSError:
continue
if os.path.basename(link).startswith(os.path.basename(db)):
print(" fd %-3s -> %s" % (fd, link)) # .db, .db-wal, .db-shm
ranges = [("PENDING", PENDING_BYTE, 1), ("RESERVED", RESERVED_BYTE, 1),
("SHARED", SHARED_FIRST, 510)]
ranges += [(SHM_LABELS.get(i, "shm+%d" % i), SHM_BASE + i, 1) for i in range(3)]
for path in targets:
if not os.path.exists(path):
continue
for label, start, length in ranges:
hit = probe(path, start, length)
if hit:
print(" %-14s %-11s %s" % (os.path.basename(path), label, hit))
Run it against two real connections — a parent holding BEGIN IMMEDIATE with an uncommitted INSERT, and a child whose own INSERT is stuck behind it. WAL mode first, where lab.db was created with PRAGMA journal_mode=WAL:
$ python3 lock_probe.py lab.db 1260968 1260971
pid 1260968 (python3)
fd 3 -> /opt/data/c2cz/scratch/locklab/lab.db
fd 4 -> /opt/data/c2cz/scratch/locklab/lab.db-wal
fd 5 -> /opt/data/c2cz/scratch/locklab/lab.db-shm
lab.db SHARED F_RDLCK off=1073741826 len=510 held by pid 1260968
lab.db-shm WAL_WRITE F_WRLCK off=120 len=1 held by pid 1260968
pid 1260971 (python3)
fd 3 -> /opt/data/c2cz/scratch/locklab/lab.db
fd 4 -> /opt/data/c2cz/scratch/locklab/lab.db-wal
fd 5 -> /opt/data/c2cz/scratch/locklab/lab.db-shm
lab.db SHARED F_RDLCK off=1073741826 len=510 held by pid 1260968
lab.db-shm WAL_WRITE F_WRLCK off=120 len=1 held by pid 1260968
And the same probe on the same pair of processes against a rollback-journal database (PRAGMA journal_mode=DELETE), where the locks land on the data file itself:
$ python3 lock_probe.py roll.db 1260968 1260969
pid 1260968 (python3)
fd 3 -> /opt/data/c2cz/scratch/locklab/roll.db
fd 4 -> /opt/data/c2cz/scratch/locklab/roll.db-journal
roll.db RESERVED F_WRLCK off=1073741825 len=1 held by pid 1260968
roll.db SHARED F_RDLCK off=1073741826 len=510 held by pid 1260968
pid 1260969 (python3)
fd 3 -> /opt/data/c2cz/scratch/locklab/roll.db
roll.db RESERVED F_WRLCK off=1073741825 len=1 held by pid 1260968
roll.db SHARED F_RDLCK off=1073741826 len=510 held by pid 1260968
The offsets are the whole story, and they are not where most people look. In WAL mode almost nothing is locked on lab.db except the 510-byte SHARED range that every open connection holds as F_RDLCK: the write lock a blocked INSERT is waiting for is WAL_WRITE, byte 120 of lab.db-shm, held F_WRLCK by the writer. So on a WAL database, database is locked is a fact about the -shm file, not about the data file — and that is why copying or deleting the sidecars out from under a running service breaks it even though the .db itself is untouched.
In rollback-journal mode the probe shows the classic layout on the database file: RESERVED as F_WRLCK at byte 1073741825 with len=1, plus the 510-byte SHARED range starting at 1073741826. One lock is missing from these two dumps because it only exists mid-commit: the pending byte, taken as a single two-byte F_WRLCK spanning 1073741824–1073741825 while a writer upgrades RESERVED to EXCLUSIVE. That is why a COMMIT blocked behind another writer can print a PENDING line that looks duplicated against RESERVED — it is one lock covering both bytes, not two competing locks. Nothing here is a whole-file lock: no SQLite version locks from byte 0, so a trace line claiming l_start=0, l_len=0 means you are looking at the wrong process, not at a mysterious lock. Two PIDs on the same database with one of them holding WAL_WRITE while it commits is a normal writer–reader pair; the same byte still held minutes after the last commit is your leak.
If you do have lsof, use lsof -p <pid> or name every sidecar explicitly (lsof /var/lib/app/app.db /var/lib/app/app.db-wal /var/lib/app/app.db-shm). Naming only the .db path matches that one inode and hides the -wal and -shm descriptors that every WAL connection also holds, which is the opposite of what you are trying to find out.
Neighbouring errors that arrive in the same log line shape
Not every lock-shaped message is contention. Two others I measured while building the lab, both of which send people down the wrong path:
read-only file : attempt to write a readonly database
missing parent dir : unable to open database file
The first is SQLITE_READONLY: file permissions, a read-only mount, or a container running as a UID that cannot write the directory. The second appears when the directory does not exist — common in containers where the volume was declared but the path inside the image was never created. Neither is affected by busy_timeout, WAL, or retries; retrying them six times just produces six identical errors. Test them by hand before you touch a single PRAGMA: create the parent directory, and confirm the service user can write a file into it.
One more that is easy to misread: database is locked on a :memory: or per-connection database is impossible, because there is no file to lock. If you see it in a test suite that uses an in-memory database, the connection you think is in-memory is not — something in your fixture or driver is opening a file path. And WAL is unavailable there too: measured, PRAGMA journal_mode=WAL on :memory: returns memory, not wal. That single returned value tells you which database your test actually touched.
Checklist
- Confirm the mode first:
PRAGMA journal_modemust read backwalon a real connection. Compare the returned value — the PRAGMA does not raise on failure. - Know your busy timeout before you debug:
PRAGMA busy_timeout. Python defaults to 5000 ms; many other tools default to 0 ms. - In rollback-journal mode, remember the failure lands on
COMMIT. Log the statement you intended to run, not the one the traceback names. - Never upgrade a read transaction into a write one. Use
BEGIN IMMEDIATEfor anything that reads a value and writes a decision from it — that is the cure forSQLITE_BUSY_SNAPSHOT(code 517). - Wrap the whole transaction in a bounded retry with full jitter, and retry only lock errors. Check
sqlite_errorname, not just the message text. - Keep the database on a local filesystem and in a local Docker volume. WAL needs shared memory and is documented as unsupported over NFS/SMB.
- Back up the sidecars, or checkpoint before copying — a copy of the
.dbalone can be an incomplete database. - Verify with
PRAGMA integrity_checkafter every change (and checkpoint from a writer connection, never from the read-only health check), and use the/proc+fcntllock probe — orlsof -p <pid>, which sees the sidecars thatlsof <db path>does not — when you need to know who really holds the lock.
None of this requires leaving SQLite. It requires accepting its actual concurrency model: many readers, one writer, locks that must be taken deliberately, and a message that describes three different problems in nine words. Measure which one you have — the timings tell you, and the error code confirms it.