skip to content

A geocoding batch writing to one SQLite file intermittently raises sqlite3.OperationalError 'database is locked' - how do you diagnose and fix it?

level: seniorimportance: should knowfreq 40%

answer

  1. One writer per database file
  2. The message is a timeout, not corruption
  3. Ask how long the lock is held
  4. Slow work does not belong inside a write
  5. A journal mode set once per file

basics

~20 s

SQLite allows one writer at a time, and the error means another connection held the write lock for longer than sqlite3.connect's timeout, which defaults to five seconds. Shorten transactions, switch the file to WAL, raise the timeout, and give each worker its own connection.

solid answer

~50 s

The error is SQLite's busy condition surfacing as `sqlite3.OperationalError` after the busy timeout expires - `sqlite3.connect(path, timeout=...)` defaults to 5 seconds. Diagnose it as a lock-hold-time problem: find who holds a write transaction and for how long. The usual cause is a transaction kept open across slow work, such as a batch that begins a write, performs a network lookup per record, and only then commits; the failure is intermittent because it depends on how the workers interleave. Fixes in order of value: keep transactions to the write itself and do slow work outside them; commit in bounded chunks; enable WAL with `PRAGMA journal_mode=WAL`, which persists for the file and lets readers proceed while one writer works; raise the timeout; and use `BEGIN IMMEDIATE` for read-then-write units. Give every thread its own connection rather than sharing one with `check_same_thread=False`.

code

python · 15 lines
python
import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), 'geo.db')
writer = sqlite3.connect(path)
writer.execute('CREATE TABLE hit(id INTEGER PRIMARY KEY)')
writer.commit()
other = sqlite3.connect(path, timeout=0.1)
writer.execute('INSERT INTO hit VALUES (1)')   # holds the write lock
try:
    other.execute('INSERT INTO hit VALUES (2)')
except sqlite3.OperationalError as exc:
    print('second connection:', exc)
writer.commit()
other.close()
writer.close()

go deeper

for a junior

Know that a SQLite database file allows only one writer at a time and that the locked error is a timeout waiting for that writer, not a corrupt file. Remember to commit promptly.

for a middle

Explain the connect timeout and its five-second default, what WAL changes about readers and writers, and why check_same_thread exists. Be able to shrink a transaction so it covers only the write.

for a senior

Diagnose by lock hold time: find the transaction that spans slow work, chunk the commits, choose an immediate begin for read-then-write units, and give each worker its own connection. Explain what tuning cannot fix.

for a principal

Own the boundary decision: state the concurrent-write throughput at which an embedded file database stops being the right choice, what the migration to a server engine costs, and how the system stays operable while write contention grows.

### What the error actually reports SQLite's concurrency model is file-level: many readers may read at once, but there is exactly one writer at a time for a database file. When a connection cannot get the lock it needs, SQLite returns a busy condition. sqlite3 does not raise immediately - it retries until the busy timeout expires, and only then converts it into `sqlite3.OperationalError` with the message about the database being locked. That timeout is the `timeout` argument to `sqlite3.connect`, five seconds by default, and it can also be set with `PRAGMA busy_timeout`. So the message never means 'SQLite is broken' or 'the file is corrupt'. It means: somebody else held the lock for longer than my patience. ### Diagnosing: measure lock hold time, not the error The error is intermittent because it depends on overlap, so chasing the failing call is the wrong move; the question is which connection holds a write transaction and for how long. Three things to establish: 1. **Where do transactions begin and end?** In sqlite3's default legacy mode an implicit `BEGIN` is issued before the first INSERT, UPDATE, DELETE or REPLACE and stays open until you commit. `Connection.in_transaction` tells you whether one is open right now. 2. **What happens between them?** This is where the answer usually is. A batch that opens a write, performs a per-record lookup over the network, and commits at the end holds the write lock for the entire duration of the slow work. Any timeout or retry on that network call extends the lock hold directly. 3. **How many writers are there?** Threads, worker processes, a scheduled job, and an operator with a shell open on the same file all count. A pattern that survives three weeks of a release train and then starts failing usually gained a writer rather than losing capacity. Useful instrumentation: log the wall time from first write to commit, and `Connection.set_trace_callback` while reproducing to see exactly which statements run inside the transaction. ### The fixes, in the order they pay **Shrink the transaction.** Do the slow work first, buffer the results, then open a transaction, write, and commit. This is almost always the real fix, and no amount of tuning substitutes for it. Never hold a SQLite write transaction across a network call. **Commit in bounded chunks.** For a long batch, a commit every few thousand records bounds both lock hold time and how much work a failure discards. **Enable WAL.** `PRAGMA journal_mode=WAL` switches the file from a rollback journal to a write-ahead log. It is a property of the *database file* and persists across connections, so it is set once. Under WAL, readers no longer block on the writer and the writer no longer blocks readers - a large win when the batch competes with a reporting query. It does not create a second writer slot: writers still serialise. WAL needs the file to be on a filesystem with working shared-memory locking, which rules out most network filesystems. **Take the write lock up front.** A deferred transaction that reads and later writes may have to upgrade its lock, and if another writer got there first the upgrade cannot always be retried safely. Beginning the unit of work as `IMMEDIATE` - via `isolation_level='IMMEDIATE'` or an explicit `BEGIN IMMEDIATE` - makes contention appear at the start, where retrying is trivial. **Raise the timeout, and retry deliberately.** A larger `timeout` converts a hard failure into a wait, which is right when contention is short and bursty and wrong when it is masking a long-held lock. Pair it with a bounded retry and a log line, so the contention stays visible instead of turning into latency nobody measures. ### Threads and connections `sqlite3.connect(check_same_thread=True)` is the default, and it raises `sqlite3.ProgrammingError` if a connection is used from a thread other than the one that created it. That check is a guard rail, not the underlying constraint: `sqlite3.threadsafety` reports the threading mode the underlying library was built with. Passing `check_same_thread=False` removes the guard rail and makes serialising access your job, which people then forget to do. The robust pattern is one connection per worker thread - held in a thread-local, or taken from a small pool - so no connection is shared and each has its own transaction state. Note that connections must not be shared across a fork either; child processes need to open their own. ### When SQLite is the wrong answer If the workload genuinely has several concurrent writers sustaining throughput, no amount of WAL and timeout tuning changes the one-writer rule. That is the point to say so plainly: SQLite is superb for embedded, read-heavy, single-writer workloads, and a server engine is the right tool once concurrent write throughput is the requirement. Recognising that boundary is more valuable in an interview than another tuning knob.

  • Does WAL let two connections write at the same time?
    No. WAL removes the reader-writer conflict, so readers keep working while one writer appends to the log, but writers still serialise on the file. It also depends on shared-memory locking, so it is unsuitable on most network filesystems, and it adds a checkpoint step that folds the log back into the database. It raises concurrency for mixed read and write workloads without changing the single-writer rule.
  • Is passing check_same_thread=False a reasonable way to use one connection from a thread pool?
    Only if you serialise the access yourself, which almost nobody does consistently. The flag removes sqlite3's guard rail without changing the fact that a connection carries a single transaction state, so two threads inside it can interleave statements into each other's transaction. Prefer one connection per thread, held in a thread-local or a small pool, and keep the default check on.
  • Would raising the connect timeout to 60 seconds be a sufficient fix?
    It changes a failure into a wait, which helps only when contention is short and bursty. If a writer holds the lock for a long time, a bigger timeout converts an obvious error into latency that no dashboard attributes to locking. Use it to absorb jitter, but fix the hold time first, and log every retry so the contention stays visible.

saying these in an interview costs you the question

  • Reads the message as file corruption
  • Adds a bare retry loop with no timeout change
  • Claims WAL allows concurrent writers
  • Shares one connection across threads with check_same_thread=False
  • Holds a write transaction across a network call
  • Says SQLite simply cannot be used concurrently

context

resolving links…