Configuring busy_timeout for IoT Sensor Writes

A single sensor-ingestion thread on an edge gateway rarely fails. It fails the moment a second writer appears — a telemetry exporter flushing a batch, a checkpoint folding the -wal file back into the database, a dashboard query holding a snapshot open a beat too long. At that instant SQLite returns SQLITE_BUSY (error code 5) to whichever connection lost the race, and with the default busy_timeout of 0 milliseconds it does so on the first failed lock attempt: no wait, no retry, no queue. The sampling loop raises sqlite3.OperationalError: database is locked, the frame is lost, and the time-series develops a gap. This page covers the exact fix for that scenario — setting a bounded busy_timeout on the sensor-writer connection — as one hardening step within the SQLite Architecture & Production Hardening discipline. It assumes your database is already in Write-Ahead Logging mode; if it is not, the contention pattern below looks different and the timeout alone will not save you.

Diagnosis

Confirm you are hitting this problem and not a structural deadlock before you change anything. Two signals together are conclusive: the runtime error text and the current timeout value.

The error surfaces as OperationalError whose message contains database is locked (the Python driver’s rendering of SQLITE_BUSY). In C runtimes it is the literal return code 5 from sqlite3_step(). A gateway logging its ingestion path will show it clustered around export or checkpoint activity, not at random:

2026-07-04 11:42:07 WARNING iot_sensor_db  insert failed for sensor=bme280: database is locked
2026-07-04 11:42:07 WARNING iot_sensor_db  insert failed for sensor=sht31: database is locked

Now read back what the connection is actually configured with. A fresh handle inherits busy_timeout = 0 unless something set it, and the value is per-connection — it does not persist in the database header the way journaling mode does:

import sqlite3

conn = sqlite3.connect("/var/lib/telemetry/sensors.db")
current = conn.execute("PRAGMA busy_timeout;").fetchone()[0]
print(f"busy_timeout = {current} ms")   # -> 0 on an unconfigured connection == fail-fast

If that prints 0 (or a value far below your longest legitimate lock-hold) and the errors correlate with a competing writer or checkpoint, you have the timeout problem — not a genuine deadlock. A deadlock would persist no matter how large the timeout; contention will not.

busy_timeout versus a checkpoint holding the write lock A time axis from 0 to 1000 milliseconds. A shaded band from 0 to about 800 milliseconds marks the window during which a competing checkpoint holds the write lock. On the upper track, with busy_timeout set to 0, a sensor write fires at time 0, collides with the held lock, and fails immediately with SQLITE_BUSY — the frame is dropped and a gap appears in the time-series. On the lower track, with busy_timeout set to 4000 milliseconds, the same write fires at time 0 and the built-in busy handler retries on an escalating back-off schedule of 1, 2, 5, 10, 15, 20 then 25 millisecond sleeps; the retry ticks ride through the entire lock-hold window and, just after the lock is released near 800 milliseconds, the INSERT finally commits at roughly 830 milliseconds. competing checkpoint holds the write lock — ~800 ms 0 200 400 600 800 1000 ms busy_timeout = 0 — fail-fast write fires SQLITE_BUSY on first attempt frame dropped — gap in the time-series busy_timeout = 4000 — busy handler retries INSERT commits ~830 ms escalating back-off sleeps: 1, 2, 5, 10, 15, 20, 25… ms ride through the held window

Solution

Set the timeout on the sensor-writer connection immediately after opening it and before the first write, then read it back and assert. A PRAGMA that silently no-ops — because a pool handed you a recycled handle, or because the statement executed inside an implicit transaction — is invisible until a crash exposes it, so verification is not optional. This factory does exactly that and nothing tangential:

import sqlite3
import logging

logger = logging.getLogger("iot_sensor_db")

def open_sensor_writer(db_path: str, timeout_ms: int = 4000) -> sqlite3.Connection:
    """Open the single sensor-writer connection with a verified busy_timeout."""
    # isolation_level=None -> autocommit, so each PRAGMA runs immediately and is
    # never wrapped in an implicit BEGIN that would defer or discard it.
    conn = sqlite3.connect(db_path, isolation_level=None)

    # PRAGMA busy_timeout is in MILLISECONDS. 4000 ms comfortably exceeds the
    # longest legitimate lock-hold on flash: an SD-card checkpoint under load
    # (~800 ms) plus fsync jitter. Keep it well under any watchdog/sample deadline
    # so an exhausted timeout still fails fast enough to trigger fallback.
    conn.execute(f"PRAGMA busy_timeout = {timeout_ms};")

    # Read the value BACK from SQLite (not from your variable) and assert it stuck.
    applied = conn.execute("PRAGMA busy_timeout;").fetchone()[0]
    if applied != timeout_ms:
        conn.close()
        raise RuntimeError(f"busy_timeout not applied: wanted {timeout_ms}, got {applied}")

    logger.info("sensor-writer busy_timeout verified at %d ms", applied)
    return conn

The timeout= keyword on sqlite3.connect() sets the same underlying value in seconds, but relying on it alone is fragile: it is easy to omit when a pool or ORM constructs the connection for you, and it will not reach handles created outside your factory. Setting the PRAGMA explicitly and asserting the read-back is what makes the guarantee hold. Do not raise the timeout in an attempt to also cover write-write conflicts — that is a different failure (SQLITE_BUSY_SNAPSHOT) the busy handler cannot retry, covered under gotchas below.

Verification

Three checks, from cheapest to most realistic.

First, the read-back is already baked into the factory — a startup that logs sensor-writer busy_timeout verified at 4000 ms proves the value reached SQLite on this connection. Grep for the absence of that line as a deployment smoke test.

Second, confirm the file is genuinely in WAL mode, because the whole contention model above assumes readers and the writer are not serializing on every transaction:

mode = conn.execute("PRAGMA journal_mode;").fetchone()[0]
assert mode == "wal", f"expected WAL, got {mode!r} — busy contention will look different"

Third, exercise the contention directly. Hold a write lock in one connection and confirm a second connection waits and succeeds rather than failing instantly:

import time, threading, sqlite3

def hold_write_lock(path, seconds):
    c = sqlite3.connect(path, isolation_level=None)
    c.execute("BEGIN IMMEDIATE;")          # take the write lock now
    time.sleep(seconds)                    # hold it for `seconds`
    c.execute("COMMIT;")

threading.Thread(target=hold_write_lock, args=(db_path, 2), daemon=True).start()
time.sleep(0.1)                            # let the holder win the lock

writer = open_sensor_writer(db_path, timeout_ms=4000)
t0 = time.monotonic()
writer.execute("INSERT INTO readings(sensor, value) VALUES (?, ?);", ("bme280", 21.4))
elapsed = (time.monotonic() - t0) * 1000
print(f"insert waited {elapsed:.0f} ms and succeeded")   # ~1900 ms, NOT an exception

With busy_timeout = 0 this raises database is locked in under a millisecond. With 4000 it blocks for roughly the lock-hold duration and then commits — that measured wait, and the absence of an exception, is your proof the timeout is doing its job.

Failure Modes & Gotchas

A connection pool hands out un-hardened handles. busy_timeout is per-connection and resets to 0 on every new handle, so if a pooled or ORM-managed layer creates connections without running your factory, the PRAGMA is simply never applied — the read-back in open_sensor_writer never runs. Route every connection through one initializer (SQLAlchemy’s connect event, a pool on_connect hook) and assert the value inside it. This is the same recycled-handle trap detailed in reducing lock contention in multi-threaded apps; the timeout you carefully set on one connection tells you nothing about the next one the pool vends.

The timeout cannot retry a snapshot conflict. In WAL mode, if the sensor writer starts a transaction against an old snapshot and another writer commits first, the deadlock is unresolvable by waiting and SQLite returns SQLITE_BUSY_SNAPSHOT — the busy handler does not fire for it, so no busy_timeout, however large, helps. Guard writes with BEGIN IMMEDIATE to take the write lock up front, and wrap the transaction in an explicit ROLLBACK-and-retry loop for the snapshot case rather than assuming the timeout absorbs it. When retries are themselves exhausted, hand the payload to a durable buffer via your fallback routing strategy instead of dropping it.

Advisory locks (and therefore the timeout) are inert on the wrong filesystem. SQLite enforces the lock the busy handler waits on through POSIX fcntl() advisory locking. On network mounts (NFS, SMB) that locking is unreliable or absent, so concurrent writers can corrupt the file while busy_timeout silently protects nothing — keep active sensor databases on local flash only. The same inertness follows from broken ownership: if two services run under identities that cannot both acquire the advisory lock, or a permissive chmod lets an unexpected process bypass the intended boundary, the timeout is configured but meaningless. Verify the filesystem permissions and ownership of the database directory as part of the same hardening pass.