SpatiaLite & GeoPackage Automation with Python

How to Retry Locked Database Writes in SQLite

Set PRAGMA busytimeout so SQLite waits for a lock instead of failing instantly, wrap writes in an exponential-backoff retry that catches…

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.

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

python
# 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:

python
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:

sql
-- 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:

python
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")

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:

python
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))

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:

python
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:

sql
-- 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.