How to Retry Locked Database Writes in SQLite

Set PRAGMA busy_timeout so SQLite waits for a lock instead of failing instantly, wrap writes in an exponential-backoff retry that catches sqlite3.OperationalError: database is locked, and start transactions with BEGIN IMMEDIATE so a write that cannot proceed fails fast rather than mid-statement.

This page is part of the Transaction Scoping & Rollback Strategies guide. It is scoped tightly to the SQLITE_BUSY lock error — distinct from implementing connection retries for offline apps, which is about reconnecting after a dropped connection rather than contending for a write lock on a live one.

Why This Matters #

SQLite allows many concurrent readers but only one writer at a time. When a second connection tries to write while another holds the write lock, SQLite returns SQLITE_BUSY, which the Python driver raises as sqlite3.OperationalError: database is locked. In a spatial pipeline this happens constantly: a sync job appends features while a background task rebuilds an R-tree spatial index, or two field-device uploads hit the same GeoPackage at once.

Which readers are affected depends entirely on the journal mode, and this is the one structural change that removes whole classes of SQLITE_BUSY rather than merely retrying through them:

The naive reaction — catch the error and give up — drops data. The correct response has layers: let SQLite wait a bounded time for the lock (busy_timeout), retry with backoff when it still cannot get in, acquire the write lock up front (BEGIN IMMEDIATE) so you never fail halfway through a multi-statement transaction, and reduce contention structurally with Write-Ahead Logging so readers stop blocking the writer. Applied together, these turn a fragile write into one that survives realistic contention.

Prerequisites #

• Python 3.9+ (sqlite3 is in the standard library)
• A SQLite, SpatiaLite, or GeoPackage file written by more than one connection or process
• Understanding of SQLite’s single-writer locking model
• Familiarity with transaction basics from the Transaction Scoping & Rollback Strategies guide

Primary Method #

# sqlite3 — resilient write wrapper for SQLITE_BUSY (database is locked)
import sqlite3
import time
import random


def connect(db_path: str, busy_ms: int = 5000) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=busy_ms / 1000, isolation_level=None)
    # busy_timeout: let SQLite spin waiting for the lock before returning SQLITE_BUSY
    conn.execute(f"PRAGMA busy_timeout = {busy_ms}")
    # WAL lets readers proceed without blocking the single writer
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


def write_with_retry(conn, statements, max_retries: int = 5) -> None:
    """
    Run a list of (sql, params) writes inside one transaction, retrying the
    whole transaction on 'database is locked' with exponential backoff + jitter.
    """
    for attempt in range(max_retries):
        try:
            # BEGIN IMMEDIATE grabs the write lock now, so we fail fast (and
            # retry cleanly) instead of blocking partway through the batch.
            conn.execute("BEGIN IMMEDIATE")
            for sql, params in statements:
                conn.execute(sql, params)
            conn.execute("COMMIT")
            return
        except sqlite3.OperationalError as exc:
            if "locked" not in str(exc) and "busy" not in str(exc):
                raise
            conn.execute("ROLLBACK")
            if attempt == max_retries - 1:
                raise
            # Exponential backoff with jitter: 0.1, 0.2, 0.4 ... plus randomness
            delay = (2 ** attempt) * 0.1 + random.uniform(0, 0.05)
            time.sleep(delay)

Two mechanisms stack here. busy_timeout handles brief contention inside a single statement without any Python-level retry. The backoff loop handles the case where the lock is held longer than the timeout, or where BEGIN IMMEDIATE itself cannot acquire the write lock — retrying the entire transaction from a clean rollback each time.

Step-by-Step Walkthrough #

1. Set a busy timeout on every connection #

By default busy_timeout is 0, so any contention fails instantly. Setting it tells SQLite to poll for the lock for up to N milliseconds before giving up:

import sqlite3

conn = sqlite3.connect("field.gpkg", isolation_level=None)
conn.execute("PRAGMA busy_timeout = 5000")   # wait up to 5 seconds

2. Enable WAL to cut reader/writer contention #

In the default rollback-journal mode, a reader blocks the writer and vice versa. WAL mode lets readers continue against the last committed snapshot while a write is in progress, so most SQLITE_BUSY from read/write overlap disappears:

-- SQLite: switch the database to Write-Ahead Logging (persists in the file)
PRAGMA journal_mode = WAL;

3. Acquire the write lock up front with BEGIN IMMEDIATE #

A default BEGIN (deferred) takes the write lock only at the first write statement — so a transaction can run three statements, then hit SQLITE_BUSY on the fourth and have to unwind. BEGIN IMMEDIATE takes the write lock immediately, so either the whole transaction proceeds or it fails before doing any work:

conn.execute("BEGIN IMMEDIATE")
conn.execute("INSERT INTO observations (obs_id, geom) VALUES (?, ?)", (1, blob))
conn.execute("UPDATE sync_state SET last_id = ?", (1,))
conn.execute("COMMIT")

The difference is where the failure lands, and therefore how much work a retry has to redo:

4. Wrap the transaction in a backoff retry #

When BEGIN IMMEDIATE or COMMIT still returns locked, roll back and retry with growing delays and a little jitter so competing writers do not resynchronise on the same schedule:

import time
import random
import sqlite3

for attempt in range(5):
    try:
        conn.execute("BEGIN IMMEDIATE")
        conn.execute("INSERT INTO observations (obs_id, geom) VALUES (?, ?)", (2, blob))
        conn.execute("COMMIT")
        break
    except sqlite3.OperationalError as exc:
        if "locked" not in str(exc):
            raise
        conn.execute("ROLLBACK")
        time.sleep((2 ** attempt) * 0.1 + random.uniform(0, 0.05))

The doubling matters as much as the retrying. A fixed delay makes competing writers collide on the same schedule forever; doubling spreads them apart, and the jitter term breaks the remaining synchrony between two processes that started at the same instant. Five attempts on this schedule spend about three seconds in total before giving up — long enough to ride out an index rebuild, short enough that a caller still gets an answer.

5. Keep transactions short and single-writer #

The most reliable fix is architectural: funnel all writes through one connection or one worker so writers never actually compete. Short transactions that open, write, and commit quickly hold the lock for milliseconds, shrinking the window in which any other writer can collide.

Verification #

Reproduce contention deliberately to prove the wrapper holds. Open two connections, have the first hold a write lock, and confirm the second waits and then succeeds rather than raising:

import sqlite3, threading, time

a = sqlite3.connect("field.gpkg", isolation_level=None)
b = sqlite3.connect("field.gpkg", isolation_level=None)
b.execute("PRAGMA busy_timeout = 3000")

a.execute("BEGIN IMMEDIATE")
a.execute("INSERT INTO observations (obs_id) VALUES (99)")

def release():
    time.sleep(1)
    a.execute("COMMIT")

threading.Thread(target=release).start()
# b should block ~1s for the lock, then succeed — not raise immediately
b.execute("INSERT INTO observations (obs_id) VALUES (100)")
b.commit()
print("second writer succeeded after waiting")

Confirm WAL is actually active:

-- SQLite: expect 'wal'
PRAGMA journal_mode;

Alternative Approaches or Edge Cases #

BEGIN IMMEDIATE versus busy_timeout alone. A long busy_timeout with deferred transactions can still deadlock: two connections each hold a read lock and each want to upgrade to a write lock, so neither can proceed and both time out with SQLITE_BUSY (a true deadlock, not mere contention). BEGIN IMMEDIATE avoids this by never holding a read lock while waiting to write. Prefer it for any transaction that will write.

Cross-process writers. busy_timeout and WAL work across separate processes, not just threads, so the same techniques protect a GeoPackage written by several command-line jobs. What they cannot fix is a writer on a network filesystem — SQLite locking is unreliable over NFS/SMB, so keep the file on local storage.

Troubleshooting #

sqlite3.OperationalError: database is locked #

Cause: Another connection holds the write lock longer than your busy_timeout, or busy_timeout is still at its default of 0. Fix: Set PRAGMA busy_timeout on every connection and wrap writes in the backoff retry above; verify no reader is holding a long-lived transaction open.

sqlite3.OperationalError: database is locked only on COMMIT #

Cause: A deferred BEGIN let the transaction start reading, then could not upgrade to a write lock at commit because another writer held it. Fix: Switch to BEGIN IMMEDIATE so the write lock is taken at the start, turning a late failure into an early, cleanly retryable one.

Writes still block after enabling WAL #

Cause: WAL removes reader/writer contention but not writer/writer contention — two simultaneous writers still serialise. Fix: Route writes through a single connection or worker (single-writer design) and keep each transaction short; use the retry wrapper only for the residual collisions.

Frequently Asked Questions #

Related #

• Transaction Scoping & Rollback Strategies — parent guide: transaction boundaries, isolation, and rollback
• Implementing Connection Retries for Offline Apps — reconnecting after a dropped connection, a different failure than a busy lock
• Read-Only WAL Mode for Distributed Field Devices — how WAL changes the locking model on shared field files
• How to Create a Spatial Index in SQLite with Python — index rebuilds are a common source of write contention