SpatiaLite & GeoPackage Automation with Python

Implementing Connection Retries for Offline Apps

Use Write-Ahead Logging, strict error filtering, and jittered exponential backoff to survive file-lock contention and storage I/O interruptions when…

Use Write-Ahead Logging, strict error filtering, and jittered exponential backoff to survive file-lock contention and storage I/O interruptions when Python automation writes to SpatiaLite or GeoPackage databases in disconnected field environments.

Why This Matters

Field GIS workflows differ from server-backed stacks in one fundamental way: there is no TCP socket to reconnect. A GeoPackage file is a single SQLite database on a flash card, an external drive, or a network share that may temporarily disappear when a device suspends or a mount point drops. When multiple sync threads or background workers compete for the same file, the OS enforces mandatory file locks and SQLite raises OperationalError: database is locked instead of a network timeout. Blind retries exhaust file descriptors, trigger WAL checkpoint starvation, and leave partial geometry writes that corrupt R-tree spatial indexes. A disciplined retry layer — one that understands which errors are transient and which are not — is the difference between a reliable offline cache and a field data loss incident.

This page is a focused implementation guide under Transaction Scoping & Rollback Strategies, which covers the broader rules for explicit BEGIN/COMMIT scoping and savepoint rollbacks.

A dropped connection and a busy lock look similar in a traceback and need opposite responses. Retrying a busy lock on the same connection is correct; retrying a dropped connection on the same connection can never succeed.

Two failures that look alike and need opposite handlingA busy lock means the connection is healthy and another writer holds the lock, so the correct response is to roll back and retry on the same connection after a delay. A dropped or invalidated connection — a closed handle, a file that moved, storage unmounted — means the connection object itself is unusable, so the correct response is to discard it and build a new one. Retrying the wrong way round either loops forever or throws away a working connection.busy lockconnection is healthyanother writer holds itroll back and retryon the same connectionconnection invalidatedhandle closed, file movedstorage unmounteddiscard and rebuildretrying it can never workbranch on the message text before deciding which retry applies
Both arrive as OperationalError, which is why the handler has to inspect the message rather than the type.

Prerequisites

  • Python 3.9+ with the standard library sqlite3 module (no third-party dependencies required for this pattern).
  • SQLite 3.35+ linked into your Python build. Check with import sqlite3; print(sqlite3.sqlite_version).
  • A SpatiaLite 5.0+ or GeoPackage 1.3-compliant database file. If you are loading spatial functions at runtime, ensure mod_spatialite is installed — see Native sqlite3 Spatial Extensions.
  • Familiarity with Python’s sqlite3 isolation-level semantics: the module defaults to auto-commit for DDL and implicit transactions for DML. The retry wrapper below uses isolation_level=None (autocommit mode) so that BEGIN/COMMIT/ROLLBACK are driven explicitly.

Primary Method

The class below is a zero-dependency retry wrapper. It enforces WAL mode on connection, distinguishes retryable OS-level errors from hard schema errors, and applies jittered exponential backoff to avoid thundering-herd collisions when multiple field devices reconnect after a sync gap.

python
# SpatiaLite / GeoPackage connection retry — Python 3.9+, sqlite3 stdlib only
import sqlite3
import time
import random
import logging
from typing import Tuple

logger = logging.getLogger(__name__)

# Substrings present in SQLite error messages that indicate a transient OS-level
# condition: SQLITE_BUSY (5), SQLITE_CANTOPEN (14), SQLITE_IOERR (10).
_RETRYABLE_TOKENS = frozenset(
    ["locked", "busy", "unable to open", "disk i/o error", "io error"]
)


class SpatialDBRetry:
    """Retry wrapper for SpatiaLite/GeoPackage with exponential backoff and WAL enforcement."""

    def __init__(
        self,
        db_path: str,
        max_retries: int = 5,
        base_delay: float = 0.5,
        max_delay: float = 12.0,
    ) -> None:
        self.db_path = db_path
        self.max_retries = max_retries
        self.base_delay = base_delay
        self.max_delay = max_delay

    def _is_retryable(self, exc: Exception) -> bool:
        if not isinstance(exc, sqlite3.OperationalError):
            return False
        msg = str(exc).lower()
        return any(token in msg for token in _RETRYABLE_TOKENS)

    def _backoff(self, attempt: int) -> float:
        """Full-jitter exponential backoff, capped at max_delay."""
        cap = min(self.base_delay * (2 ** attempt), self.max_delay)
        return random.uniform(0, cap)

    def connect(self) -> sqlite3.Connection:
        """Open the database and enforce WAL mode, retrying on transient I/O failures."""
        for attempt in range(self.max_retries + 1):
            try:
                # isolation_level=None → manual BEGIN/COMMIT/ROLLBACK in callers
                conn = sqlite3.connect(
                    self.db_path,
                    timeout=10.0,
                    check_same_thread=False,
                    isolation_level=None,
                )
                conn.execute("PRAGMA journal_mode=WAL;")
                conn.execute("PRAGMA synchronous=NORMAL;")
                conn.execute("PRAGMA foreign_keys=ON;")
                return conn
            except sqlite3.OperationalError as exc:
                if self._is_retryable(exc) and attempt < self.max_retries:
                    delay = self._backoff(attempt)
                    logger.warning(
                        "connect attempt %d/%d failed (%s); retrying in %.2fs",
                        attempt + 1,
                        self.max_retries,
                        exc,
                        delay,
                    )
                    time.sleep(delay)
                else:
                    raise

    def execute(
        self, conn: sqlite3.Connection, sql: str, params: Tuple = ()
    ) -> sqlite3.Cursor:
        """Execute one statement, retrying only when no transaction is open.

        If the caller already issued BEGIN, a mid-transaction lock error is
        re-raised immediately so the *entire transaction* can roll back and
        retry as a unit — never just the failing statement in isolation.
        """
        in_tx = conn.in_transaction
        for attempt in range(self.max_retries + 1):
            try:
                return conn.execute(sql, params)
            except sqlite3.OperationalError as exc:
                if self._is_retryable(exc) and not in_tx and attempt < self.max_retries:
                    delay = self._backoff(attempt)
                    logger.warning(
                        "execute attempt %d/%d failed (%s); retrying in %.2fs",
                        attempt + 1,
                        self.max_retries,
                        exc,
                        delay,
                    )
                    time.sleep(delay)
                else:
                    raise

Step-by-step Walkthrough

1. Open the connection with WAL mode

python
client = SpatialDBRetry("/data/field_survey.gpkg", max_retries=5, base_delay=0.5)
conn = client.connect()

PRAGMA journal_mode=WAL decouples readers from writers and eliminates exclusive read locks, so background sync threads can query the file while a write transaction is in progress. This is the single most important setting for field devices that run concurrent data-collection and sync processes. The Connection Pooling & Lifecycle Management guide explains how to share one WAL-mode connection safely across threads.

2. Scope every write inside an explicit transaction

python
try:
    conn.execute("BEGIN IMMEDIATE;")
    client.execute(
        conn,
        "INSERT INTO survey_points (id, geom, status) VALUES (?, ?, ?);",
        (101, b"\x00\x01...", "pending"),
    )
    client.execute(
        conn,
        "UPDATE sync_queue SET status = 'synced' WHERE id = ?;",
        (42,),
    )
    conn.execute("COMMIT;")
except Exception as exc:
    conn.execute("ROLLBACK;")
    logger.error("transaction aborted: %s", exc)
    raise
finally:
    conn.close()

BEGIN IMMEDIATE acquires a reserved lock at the start, preventing a second writer from grabbing the lock between your first and second statement — a race condition that causes spurious SQLITE_BUSY mid-transaction. The matching ROLLBACK on any error ensures orphaned geometry records never persist.

3. Retry the entire transaction unit on lock errors

When execute re-raises inside an open transaction (because conn.in_transaction is True), the caller’s except block catches it, rolls back, and can retry the entire unit:

python
def sync_with_retry(client: SpatialDBRetry, record: dict, max_tx_retries: int = 3) -> None:
    for tx_attempt in range(max_tx_retries):
        conn = client.connect()
        try:
            conn.execute("BEGIN IMMEDIATE;")
            client.execute(
                conn,
                "INSERT OR REPLACE INTO features (fid, geom, label) VALUES (?, ?, ?);",
                (record["fid"], record["geom"], record["label"]),
            )
            conn.execute("COMMIT;")
            return
        except sqlite3.OperationalError as exc:
            conn.execute("ROLLBACK;")
            if tx_attempt < max_tx_retries - 1:
                delay = client._backoff(tx_attempt)
                logger.warning("tx attempt %d failed; retrying in %.2fs: %s", tx_attempt + 1, delay, exc)
                time.sleep(delay)
            else:
                raise
        finally:
            conn.close()

4. Verify WAL mode is active

After the first successful connect, confirm WAL mode is applied before trusting any write:

python
mode = conn.execute("PRAGMA journal_mode;").fetchone()[0]
assert mode == "wal", f"Expected WAL, got {mode!r}"

On read-only filesystems or certain Android storage mounts, WAL mode silently falls back to DELETE journal mode — this assertion surfaces the problem immediately.

Retry loop with jittered exponential backoffFlowchart: execute statement, check success; on success return result; on failure check if error is retryable and attempts remain; if yes sleep with jittered backoff then retry; if no raise error immediately.yesnonoyesretryExecute statementconn.execute(sql, params)Success?Return resultsuccessRetryable &attempts left?Raise errorfail fastSleep jittered backoffmin(base·2ⁿ, max_delay)
Only contention errors (locked/busy) enter the backoff loop; schema errors and constraint failures propagate immediately. Jitter spreads reconnecting field devices across time to avoid I/O spikes.
Why mobile background throttling breaks a naive retry loopA retry loop sleeps between attempts. In the foreground those sleeps elapse as written, and five attempts finish in about three seconds. Once the app is backgrounded, the operating system may suspend the process between attempts, so the same loop can span minutes of wall-clock time and finish long after the work stopped being useful. A deadline measured against a monotonic clock bounds it correctly where an attempt count does not.foregroundsleeps elapse as writtenfive attempts, about 3 sthe loop behaves as designedbackgroundedprocess suspended between attemptsthe same five attempts span minutesfinishes long after it matteredbound the loop with a monotonic deadline, not an attempt count
An attempt count assumes attempts are evenly spaced in time. On a throttled device they are not.

Verification

After a successful write cycle, run these checks to confirm the database is healthy:

python
# Confirm WAL is active
mode = conn.execute("PRAGMA journal_mode;").fetchone()[0]
assert mode == "wal", f"Expected WAL, got {mode!r}"

# Confirm row count increased
count = conn.execute("SELECT COUNT(*) FROM survey_points;").fetchone()[0]
assert count > 0, "No rows written — transaction may have silently rolled back"

# Check for outstanding WAL frames that need checkpointing
wal_info = conn.execute("PRAGMA wal_checkpoint(PASSIVE);").fetchone()
# Returns (busy, log, checkpointed); log==checkpointed means WAL is fully flushed
print(f"WAL checkpoint: busy={wal_info[0]}, log={wal_info[1]}, checkpointed={wal_info[2]}")

For GeoPackage files, also verify spatial index integrity after any bulk-write session:

python
# GeoPackage R-tree check (requires gpkg_geometry_columns populated)
rows = conn.execute(
    "SELECT table_name, column_name FROM gpkg_geometry_columns;"
).fetchall()
for table, col in rows:
    result = conn.execute(
        f"SELECT CheckSpatialIndex('{table}', '{col}');"
    ).fetchone()
    print(f"R-tree {table}.{col}: {result[0]}")  # 1 = valid, 0 = needs rebuild

Alternative Approaches and Edge Cases

SpatiaLite vs GeoPackage path differences

When using SpatiaLite (a .sqlite or .db file with mod_spatialite loaded), the connection setup adds one step:

python
conn.enable_load_extension(True)
conn.load_extension("mod_spatialite")
conn.execute("SELECT InitSpatialMetaDataFull(1);")  # only on first creation

The retry logic in SpatialDBRetry is identical for both formats because both are SQLite files and surface the same OperationalError subtypes. The R-tree check command differs: SpatiaLite uses RecoverSpatialIndex('table_name') rather than the GeoPackage gpkgAddSpatialIndex approach.

Android and iOS background throttling

Mobile operating systems suspend background processes aggressively. On Android (API 23+), Doze mode can pause your sync thread for several minutes. On iOS, background fetch windows are limited to 30 seconds. Configure max_delay to stay below your platform’s background task time limit:

python
import sys

# Shorter ceiling on mobile runtimes where background execution is time-capped
max_delay = 6.0 if sys.platform in ("android", "ios") else 12.0
client = SpatialDBRetry("/data/field.gpkg", max_retries=4, base_delay=0.4, max_delay=max_delay)

If the device suspends mid-transaction, SQLite rolls back automatically on the next open — WAL mode ensures the previously committed frames are safe. The pending incomplete transaction is discarded cleanly.

Async context (asyncio + run_in_executor)

The standard library sqlite3 module is synchronous. In an asyncio application, wrap blocking calls in loop.run_in_executor:

python
import asyncio

async def async_sync(client: SpatialDBRetry, record: dict) -> None:
    loop = asyncio.get_running_loop()
    await loop.run_in_executor(None, lambda: sync_with_retry(client, record))

For async-native spatial queries, see Async Database Queries in Python GIS.

Troubleshooting

OperationalError: database is locked does not clear after retries

Cause: A writer elsewhere is holding a RESERVED or PENDING lock and not committing. WAL mode allows concurrent readers but only one writer at a time. If a separate process opened the file without WAL mode (DELETE journal), it may hold an exclusive lock that blocks all WAL writers.

Fix: Identify the competing process with lsof /path/to/file.gpkg. Ensure all processes that open the file issue PRAGMA journal_mode=WAL before their first write. If you cannot control other openers, use a file-level mutex (e.g., fcntl.flock) as a cross-process write gate.

OperationalError: unable to open database file

Cause: The storage mount is unavailable. Common on Android external storage when the device locks the screen, or on Linux when an NFS/SMB share drops.

Fix: The retry loop will catch this (it matches unable to open). If retries are exhausted, the mount has not recovered — check os.path.exists(db_path) before attempting connection and surface a user-facing “storage unavailable” alert rather than logging a raw exception.

R-tree reports corruption after a failed write

Cause: A geometry row was written but the corresponding R-tree index row was not, typically from a crash between the two internal SQLite steps. This cannot happen inside a properly scoped transaction — it only occurs when writes bypass the transaction boundary.

Fix: Rebuild the index. For GeoPackage:

python
conn.execute("SELECT gpkgAddSpatialIndex('survey_points', 'geom');")

For SpatiaLite:

python
conn.execute("SELECT RecoverSpatialIndex('survey_points', 'geom');")

Then re-run the CheckSpatialIndex verification above. For background on why R-tree indexes desynchronize, see the GeoPackage Specification Deep Dive.