I Added Retry Logic to My SQLite Failure Library. Here's the Exponential Backoff Pattern That Works.
Hook
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 Fix
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:
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:
timeout=30- SQLite waits up to 30 seconds for a lock before raising an errormax_retries=5- After 5 failed attempts, give up and let the caller handle itbase_delay=0.1- Start with 100ms delay, double each retryjitter=0.05- Add random 0‑50ms to prevent thundering herd
Why This Works
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:
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)
Gotchas
WAL mode reduces lock contention: Enable Write-Ahead Logging to allow concurrent readers while writing:
conn = sqlite3.connect(str(DB_PATH), timeout=30) conn.execute("PRAGMA journal_mode=WAL")Busy timeout vs retry: SQLite's
timeoutparameter 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:
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()}") raiseConnection pool sizing: Too many connections increase lock contention. Start with
pool_size=5and adjust based on your agent count.
Putting It All Together
Here's the complete retry + pool + WAL setup I'm running in production:
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.
What about you? 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.
Comments
No comments yet. Start the discussion.