# I Added Retry Logic to My SQLite Failure Library. Here's the Exponential Backoff Pattern That Works. > Source: > Published: 2026-07-24 17:43:00+00:00 My SQLite failure library started throwing "database is locked" errors under concurrent load. Multiple agents writing failures simultaneously caused write contention. I needed retry logic — but not the dumb kind. The problem: SQLite's default timeout is 5 seconds. Under heavy concurrent writes, agents timeout before the lock is released. The fix is a retry wrapper with exponential backoff and jitter: ``` python import sqlite3 import time import random from typing import Optional DB_PATH = Path.home() / ".mcp" / "failures.db" def execute_with_retry(func, max_retries=5, base_delay=0.1): """Execute a database function with exponential backoff retry.""" for attempt in range(max_retries): try: return func() except sqlite3.OperationalError as e: if "database is locked" not in str(e): raise if attempt == max_retries - 1: raise # Exponential backoff with jitter delay = base_delay * (2 ** attempt) + random.uniform(0, 0.05) time.sleep(delay) return func() def log_failure(task: str, error: str, attempted_fix: str, result: str, env: str = None): """Log a failure with retry logic.""" def _insert(): conn = sqlite3.connect(str(DB_PATH), timeout=30) conn.execute( "INSERT INTO failures (task, error, attempted_fix, result, timestamp, env) VALUES (?, ?, ?, ?, ?, ?)", (task, error, attempted_fix, result, time.time(), env) ) conn.commit() conn.close() execute_with_retry(_insert, max_retries=5, base_delay=0.1) ``` The key parameters: Exponential backoff works because it spreads out retry attempts. When multiple agents hit the same lock simultaneously, they don't all retry at the same time. The jitter ensures they retry at slightly different intervals. The timeout parameter is the first line of defense — SQLite itself waits for the lock before raising an error. The retry wrapper handles the case where the timeout expires. I also added a connection pool to reduce the number of simultaneous connections: ``` python class ConnectionPool: def __init__(self, db_path, pool_size=5): self.db_path = db_path self.pool_size = pool_size self._pool = [] def get(self): if self._pool: return self._pool.pop() return sqlite3.connect(str(self.db_path), timeout=30) def put(self, conn): if len(self._pool) < self.pool_size: self._pool.append(conn) else: conn.close() pool = ConnectionPool(DB_PATH) def log_failure(task: str, error: str, attempted_fix: str, result: str, env: str = None): conn = pool.get() try: conn.execute( "INSERT INTO failures (task, error, attempted_fix, result, timestamp, env) VALUES (?, ?, ?, ?, ?, ?)", (task, error, attempted_fix, result, time.time(), env) ) conn.commit() finally: pool.put(conn) conn = sqlite3.connect(str(DB_PATH), timeout=30) conn.execute("PRAGMA journal_mode=WAL") ``` **Busy timeout vs retry**: SQLite's `timeout` parameter handles the first wait. The retry wrapper handles cases where the timeout expires. Don't rely on just one. **Deadlock detection**: If you see consistent "database is locked" errors even with retries, you may have a deadlock. Add logging to identify which operations are holding locks: ``` python import traceback def log_failure_with_deadlock_detection(...): try: _insert() except sqlite3.OperationalError as e: if "database is locked" in str(e): print(f"Deadlock detected: {traceback.format_exc()}") raise ``` Here's the complete retry + pool + WAL setup I'm running in production: ``` python import sqlite3 import time import random from pathlib import Path DB_PATH = Path.home() / ".mcp" / "failures.db" def init_db(): conn = sqlite3.connect(str(DB_PATH), timeout=30) conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA busy_timeout=30000") conn.execute(""" CREATE TABLE IF NOT EXISTS failures ( id INTEGER PRIMARY KEY AUTOINCREMENT, task TEXT NOT NULL, error TEXT NOT NULL, attempted_fix TEXT, result TEXT NOT NULL, timestamp REAL NOT NULL, env TEXT ) """) conn.execute("CREATE INDEX IF NOT EXISTS idx_task ON failures(task)") conn.execute("CREATE INDEX IF NOT EXISTS idx_timestamp ON failures(timestamp)") conn.commit() conn.close() class RetryConfig: def __init__(self, max_retries=5, base_delay=0.1, max_delay=2.0, jitter=True): self.max_retries = max_retries self.base_delay = base_delay self.max_delay = max_delay self.jitter = jitter def execute_with_retry(func, config=None): if config is None: config = RetryConfig() last_error = None for attempt in range(config.max_retries): try: return func() except sqlite3.OperationalError as e: last_error = e if "database is locked" not in str(e): raise if attempt == config.max_retries - 1: raise delay = min(config.base_delay * (2 ** attempt), config.max_delay) if config.jitter: delay += random.uniform(0, 0.05) time.sleep(delay) raise last_error class ConnectionPool: def __init__(self, db_path, pool_size=5): self.db_path = db_path self.pool_size = pool_size self._pool = [] def get(self): if self._pool: return self._pool.pop() conn = sqlite3.connect(str(self.db_path), timeout=30) conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA busy_timeout=30000") return conn def put(self, conn): if len(self._pool) < self.pool_size: self._pool.append(conn) else: conn.close() pool = ConnectionPool(DB_PATH) def log_failure(task: str, error: str, attempted_fix: str, result: str, env: str = None): def _insert(): conn = pool.get() try: conn.execute( "INSERT INTO failures (task, error, attempted_fix, result, timestamp, env) VALUES (?, ?, ?, ?, ?, ?)", (task, error, attempted_fix, result, time.time(), env) ) conn.commit() finally: pool.put(conn) execute_with_retry(_insert) # Initialize on startup init_db() ``` This is what I run across 3 concurrent agents. Zero "database is locked" errors since deployment. Have you dealt with SQLite "database is locked" errors in production? What's your retry strategy — exponential backoff, fixed intervals, or something else? I'm curious whether a connection pool actually helps or if it just adds complexity. Drop a comment below — I read every response.