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:
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
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:
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:
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:
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)
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.