~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.
Python academiaanthropicarxivbravedeep-researchencryptionhome-automationhomeserverlocallocal-deep-researchlocal-llmmistralollamaopenaipubmedresearchresearch-toolretrieval-augmented-generationsearxngself-hosted
"""Unit tests for ``retry_on_db_lock``.
The helper is the production answer to the rate-limit tracker's
cascading ``database is locked`` failures (2026-09-21). It retries the
wrapped callable on ``OperationalError`` whose DBAPI message is exactly
``database is locked``, and only on that exception class -- every other
failure (IntegrityError, ProgrammingError, plain ConnectionError,
SQLITE_LOCKED, etc.) propagates immediately so the caller can decide
how to handle it.
These tests pin the contract:
1. **Locks retry** -- the helper runs the callable multiple times when
the failure is a "database is locked" error, and the total number
of calls is bounded by ``attempts``.
2. **Backoff is exponential** -- the gap between attempts grows.
3. **Other errors propagate immediately** -- only the lock error is
retryable.
4. **Successful first try is fast** -- zero backoff, zero retries.
5. **Returns the callable's value** -- not None, not a re-raised
sentinel.
"""
import sqlite3
import threading
import time
from pathlib import Path
import pytest
from sqlalchemy import create_engine, event
from sqlalchemy.exc import OperationalError as SAOperationalError
from sqlalchemy.orm import sessionmaker
from local_deep_research.database.sqlcipher_compat import (
get_sqlcipher_module,
)
from local_deep_research.database.sqlcipher_utils import (
_db_lock_error_types,
apply_cipher_defaults_before_key,
apply_performance_pragmas,
retry_on_db_lock,
set_sqlcipher_key,
)
# ---------------------------------------------------------------------------
# Pure-Python unit tests (no DB needed)
# ---------------------------------------------------------------------------
class TestRetryOnLockContract:
"""Pin the helper's behaviour without touching a database."""
def test_returns_callable_value_on_first_success(self):
"""A happy-path callable returns its value untouched; the helper
does not wrap or transform it.
"""
result = retry_on_db_lock(lambda: 42)
assert result == 42
def test_does_not_retry_on_non_lock_operationalerror(self):
"""``OperationalError`` without ``'locked'`` in the message is
NOT a transient lock -- it propagates on the first failure.
Other classes of error (``ValueError``, ``KeyError``, etc.) also
propagate unchanged.
"""
calls = []
def raises_value_error():
calls.append(1)
raise ValueError("not a lock error")
with pytest.raises(ValueError, match="not a lock error"):
retry_on_db_lock(raises_value_error)
assert calls == [1], (
"Non-lock errors must propagate on the first attempt; the "
f"helper called the callable {len(calls)} times."
)
def test_does_not_retry_on_lock_without_substring_match(self):
"""An ``OperationalError`` whose message does NOT contain the
substring ``'locked'`` must propagate on the first attempt.
Pinning this prevents a future SQLCipher driver change (which
might rename the error message) from accidentally turning the
helper into a generic OperationalError retry.
"""
calls = []
def raises_other_op_error():
calls.append(1)
raise sqlite3.OperationalError("readonly database")
with pytest.raises(sqlite3.OperationalError, match="readonly"):
retry_on_db_lock(raises_other_op_error)
assert calls == [1]
def test_does_not_retry_on_sqlite_locked(self):
"""``database table is locked`` is SQLITE_LOCKED, not the busy
timeout the helper waits out, so it propagates on the first
attempt.
"""
calls = []
def raises_table_locked():
calls.append(1)
raise sqlite3.OperationalError("database table is locked")
with pytest.raises(sqlite3.OperationalError, match="table is locked"):
retry_on_db_lock(raises_table_locked, base_delay_seconds=0)
assert calls == [1]
def test_does_not_retry_when_only_the_sql_mentions_locked(self):
"""``str()`` of a SQLAlchemy ``OperationalError`` renders the
statement and its parameters. A non-lock error whose query or
parameters mention ``locked`` must still propagate on the first
attempt.
"""
calls = []
def raises_wrapped_error():
calls.append(1)
raise SAOperationalError(
"UPDATE accounts SET status = ? WHERE note = 'locked'",
("locked",),
sqlite3.OperationalError("no such table: accounts"),
)
with pytest.raises(SAOperationalError, match="no such table"):
retry_on_db_lock(raises_wrapped_error, base_delay_seconds=0)
assert calls == [1]
def test_retries_a_wrapped_busy_error(self):
"""The SQLAlchemy wrapper around a real ``database is locked`` is
retried: the helper reads the DBAPI message, not the rendered
statement.
"""
calls = []
def fails_then_succeeds():
calls.append(1)
if len(calls) < 2:
raise SAOperationalError(
"UPDATE accounts SET status = ?",
("active",),
sqlite3.OperationalError("database is locked"),
)
return "ok"
assert (
retry_on_db_lock(fails_then_succeeds, base_delay_seconds=0) == "ok"
)
assert len(calls) == 2
@staticmethod
def _busy_error(message, code=None):
error = sqlite3.OperationalError(message)
if code is not None:
error.sqlite_errorcode = code
return error
@pytest.mark.parametrize(
"code",
[
sqlite3.SQLITE_BUSY,
sqlite3.SQLITE_BUSY_RECOVERY,
sqlite3.SQLITE_BUSY_SNAPSHOT,
],
)
def test_retries_on_a_busy_error_code_whatever_the_message(self, code):
"""With ``sqlite_errorcode`` set, the code decides, so a busy
error still retries if a driver words its message differently.
"""
calls = []
def fails_then_succeeds():
calls.append(1)
if len(calls) < 2:
raise self._busy_error(
"database is locked (5) (SQLITE_BUSY)", code
)
return "ok"
assert (
retry_on_db_lock(fails_then_succeeds, base_delay_seconds=0) == "ok"
)
assert len(calls) == 2
def test_does_not_retry_on_a_non_busy_error_code(self):
"""A code other than a busy one is not retried, even with the
busy message.
"""
calls = []
def raises_locked():
calls.append(1)
raise self._busy_error("database is locked", sqlite3.SQLITE_LOCKED)
with pytest.raises(sqlite3.OperationalError):
retry_on_db_lock(raises_locked, base_delay_seconds=0)
assert calls == [1]
@pytest.mark.parametrize(
"message", [" database is locked ", "Database Is Locked"]
)
def test_matches_busy_message_case_and_whitespace_insensitive(
self, message
):
calls = []
def fails_then_succeeds():
calls.append(1)
if len(calls) < 2:
raise sqlite3.OperationalError(message)
return "ok"
assert (
retry_on_db_lock(fails_then_succeeds, base_delay_seconds=0) == "ok"
)
assert len(calls) == 2
def test_falls_back_to_the_wrapped_error_when_orig_is_falsey(self):
"""An error whose ``orig`` is falsey is read itself."""
class BusyWithoutOrig(sqlite3.OperationalError):
orig = None
calls = []
def fails_then_succeeds():
calls.append(1)
if len(calls) < 2:
raise BusyWithoutOrig("database is locked")
return "ok"
assert (
retry_on_db_lock(fails_then_succeeds, base_delay_seconds=0) == "ok"
)
assert len(calls) == 2
def test_retries_until_success(self):
"""The callable fails with a lock error N-1 times, then
succeeds on the Nth attempt -- the helper returns the success
value.
"""
attempts_before_success = [3]
calls = []
def fails_then_succeeds():
calls.append(1)
if len(calls) < attempts_before_success[0]:
raise sqlite3.OperationalError("database is locked")
return "ok"
result = retry_on_db_lock(
fails_then_succeeds, attempts=5, base_delay_seconds=0
)
assert result == "ok"
assert len(calls) == attempts_before_success[0]
def test_gives_up_after_attempts(self):
"""When every attempt raises the lock error, the helper raises
after ``attempts`` total calls -- not silently swallowing.
"""
calls = []
def always_fails():
calls.append(1)
raise sqlite3.OperationalError("database is locked")
with pytest.raises(sqlite3.OperationalError, match="locked"):
retry_on_db_lock(always_fails, attempts=4, base_delay_seconds=0)
assert len(calls) == 4, (
f"Expected 4 calls (one per attempt), got {len(calls)}."
)
def test_attempts_one_disables_retry(self):
"""``attempts=1`` makes the helper a transparent wrapper. Useful
for callers who want the lock-detection logic but not the
retry behaviour.
"""
calls = []
def fails():
calls.append(1)
raise sqlite3.OperationalError("database is locked")
with pytest.raises(sqlite3.OperationalError):
retry_on_db_lock(fails, attempts=1)
assert calls == [1]
def test_exponential_backoff(self):
"""The gap between attempts grows. With ``base_delay_seconds=0.05``
the three gaps should be 0.05, 0.10, 0.20 seconds (1x, 2x, 4x).
We use 50 ms / 100 ms / 200 ms rather than 100 ms / 200 ms / 400 ms
to keep the test fast.
"""
timestamps = []
def record_and_fail():
timestamps.append(time.monotonic())
raise sqlite3.OperationalError("database is locked")
with pytest.raises(sqlite3.OperationalError):
retry_on_db_lock(
record_and_fail, attempts=4, base_delay_seconds=0.05
)
assert len(timestamps) == 4
gap_1 = timestamps[1] - timestamps[0]
gap_2 = timestamps[2] - timestamps[1]
gap_3 = timestamps[3] - timestamps[2]
# Generous lower bounds because the OS scheduler adds noise.
# The strict invariant we care about is *monotonic growth* --
# if a future refactor regressed to a constant sleep, the
# growth assertion would fail even if the absolute timings
# drifted.
assert gap_1 >= 0.04, f"First gap {gap_1:.3f}s too small"
assert gap_2 >= gap_1 * 1.5, (
f"Second gap {gap_2:.3f}s did not grow vs first {gap_1:.3f}s"
)
assert gap_3 >= gap_2 * 1.5, (
f"Third gap {gap_3:.3f}s did not grow vs second {gap_2:.3f}s"
)
# ---------------------------------------------------------------------------
# Integration test: real SQLCipher + real contention
# ---------------------------------------------------------------------------
class TestRetryOnLockIntegration:
"""Reproduce the production pattern end-to-end: a writer inside a
SQLite database hits ``database is locked``, the helper retries,
and the next attempt succeeds."""
@pytest.fixture
def sqlcipher_module(self):
"""Local fixture: this file lives under ``tests/database/`` and
doesn't inherit the ``sqlcipher_module`` fixture from
``tests/auth_tests/conftest.py``.
"""
return get_sqlcipher_module()
@staticmethod
def _open_with_tight_timeout(
sqlcipher_module, db_path, password="pw", busy_timeout_ms=200
):
conn = sqlcipher_module.connect(str(db_path))
cursor = conn.cursor()
apply_cipher_defaults_before_key(cursor)
set_sqlcipher_key(cursor, password)
apply_performance_pragmas(cursor)
cursor.close()
# Override busy_timeout so the test fails fast when the holder
# doesn't release the lock.
conn.execute(f"PRAGMA busy_timeout = {busy_timeout_ms}")
return conn
@pytest.mark.slow
def test_retries_locked_error_then_succeeds(
self, sqlcipher_module, tmp_path: Path
):
"""Integration: the helper catches a real ``database is locked``
error from a SQLCipher session and retries the callable. On
the second attempt (after the holder's transaction releases
the lock) the commit lands and the helper returns normally.
``busy_timeout`` only applies to the spinner that waits for
the writer lock. With Python sqlite3's default *deferred*
transactions, the lock is acquired at the first write, not at
BEGIN; some Python wrappers don't surface busy_timeout's
deadline on that path. The test pins the writer to
``BEGIN IMMEDIATE`` so the writer actually waits for the lock
and the timeout is honoured -- same pattern the production
rate-limit tracker's session ends up in via
``metrics_writer.get_session``.
"""
db_path = tmp_path / "test_retry_on_lock.db"
# Init the schema.
conn = self._open_with_tight_timeout(sqlcipher_module, db_path)
conn.execute(
"CREATE TABLE counters (id INTEGER PRIMARY KEY, n INTEGER)"
)
conn.execute("INSERT INTO counters (id, n) VALUES (1, 0)")
conn.commit()
conn.close()
holder_started = threading.Event()
holder_release = threading.Event()
holder_done = threading.Event()
call_count = []
def holder():
"""Hold a write transaction until the test signals."""
c = self._open_with_tight_timeout(
sqlcipher_module, db_path, busy_timeout_ms=200
)
try:
c.execute("BEGIN IMMEDIATE")
c.execute("INSERT INTO counters (id, n) VALUES (2, 100)")
holder_started.set()
# Hold the writer lock until the test releases us.
holder_release.wait()
c.commit()
finally:
c.close()
holder_done.set()
def writer_callable():
"""Open a fresh session, do an ``UPDATE`` inside an
``IMMEDIATE`` transaction, and commit.
``IMMEDIATE`` is what makes ``busy_timeout`` apply: it
acquires the writer lock at BEGIN, so a contended writer
hits the spinner that ``busy_timeout`` bounds. Release
the holder only after observing that real lock error,
regardless of how long opening this connection takes.
"""
call_count.append(1)
c = self._open_with_tight_timeout(
sqlcipher_module, db_path, busy_timeout_ms=200
)
try:
try:
c.execute("BEGIN IMMEDIATE")
except sqlcipher_module.OperationalError as exc:
assert "locked" in str(exc).lower()
holder_release.set()
assert holder_done.wait(timeout=5.0), (
"Holder did not release its transaction"
)
raise
c.execute("UPDATE counters SET n = n + 1 WHERE id = 1")
c.commit()
return "committed"
finally:
c.close()
t_holder = threading.Thread(target=holder)
t_holder.start()
try:
# The first attempt must observe contention before the
# holder releases its transaction and permits the retry.
assert holder_started.wait(timeout=5.0)
result = retry_on_db_lock(
writer_callable, attempts=3, base_delay_seconds=0.1
)
assert result == "committed"
finally:
# Also release the holder if setup or the retry helper fails.
holder_release.set()
t_holder.join(timeout=5.0)
assert not t_holder.is_alive(), "Holder thread did not finish"
# The helper must have retried after the real lock error.
assert len(call_count) >= 2, (
f"Expected at least 2 attempts (first lock + retry), got "
f"{len(call_count)}"
)
# ---------------------------------------------------------------------------
# SQLAlchemy-session integration: drives the production-shaped wrapped
# ``sqlalchemy.exc.OperationalError`` path, not the raw DBAPI path
# that ``test_retries_locked_error_then_succeeds`` above already
# exercises. Without ``sqlalchemy.exc.OperationalError`` in the helper's
# caught-tuple the production call site
# (``rate_limit_tracker._persist_estimate`` -> SQLAlchemy session
# commit) propagates after one attempt and the retry never fires; this
# test pins that the helper does fire on the wrapped path and that the
# rate-limit-shaped table actually persists the row.
# ---------------------------------------------------------------------------
class TestRetryOnLockSQLAlchemySession:
"""Pin the SQLAlchemy-wrapped ``OperationalError`` path. The
reviewer (PR 6718, item 1) demonstrated that without adding
``sqlalchemy.exc.OperationalError`` to ``_DB_LOCK_ERROR_TYPES``,
the helper's only production call site silently no-ops on a real
lock contention -- ``sqlalchemy.exc.OperationalError`` does NOT
inherit from ``sqlite3.OperationalError`` or
``sqlcipher3.dbapi2.OperationalError``, and the message guard
alone would never fire if the helper never sees the exception.
"""
def test_sqlalchemy_operationalerror_is_caught(self):
"""Static guard: ``sqlalchemy.exc.OperationalError`` must be
in the helper's caught-tuple, otherwise the SQLAlchemy
commit path is invisible to it. This assertion fires if a
future refactor accidentally drops the SQLAlchemy type from
``_db_lock_error_types``.
"""
caught = _db_lock_error_types()
assert SAOperationalError in caught, (
"sqlalchemy.exc.OperationalError must be in "
f"_DB_LOCK_ERROR_TYPES; got {caught!r}. Without it, the "
"helper's only production call site "
"(rate_limit_tracker._persist_estimate) commits via "
"SQLAlchemy and the wrapped error escapes the helper without "
"ever being retried."
)
@pytest.mark.slow
def test_retries_locked_error_via_sqlalchemy_session(self, tmp_path: Path):
"""End-to-end: drive the production-shaped SQLAlchemy session
path against a real SQLite file, hold the writer lock from
another connection, and assert that the helper actually
retries (not just propagates after one call) and the row
is durable after the holder releases.
Uses plain ``sqlite://`` (not SQLCipher) so the test stays
fast and self-contained; SQLCipher would only change the
connection ``creator``, not the wrapping behaviour. The
production encrypted path wraps the same DBAPI error in the
same ``sqlalchemy.exc.OperationalError`` -- this test pins
that wrapping.
"""
db_path = tmp_path / "test_retry_sqlalchemy.db"
engine = create_engine(f"sqlite:///{db_path}")
# Apply ``busy_timeout`` via an ``engine.connect`` event
# listener, the same pattern production uses
# (``create_sqlcipher_connection`` listens for ``connect``
# and emits ``apply_performance_pragmas``). Setting it via
# a raw PRAGMA on the engine's pool connection doesn't
# propagate to connections checked out later, so a freshly
# created session wouldn't honor the timeout -- mirroring
# the production symptom this test is here to pin.
@event.listens_for(engine, "connect")
def _set_writer_busy_timeout(dbapi_con, _record):
cur = dbapi_con.cursor()
cur.execute("PRAGMA busy_timeout = 100")
cur.close()
try:
with engine.connect() as conn:
conn.execute(
__import__("sqlalchemy").text(
"CREATE TABLE rate_estimates ("
" engine TEXT PRIMARY KEY,"
" base_wait REAL NOT NULL"
")"
)
)
conn.commit()
holder_started = threading.Event()
holder_release = threading.Event()
attempt_count = []
observed_error_types = []
def hold_writer_lock():
"""Open a raw connection, BEGIN IMMEDIATE, hold for
600 ms, then commit. The other thread's SQLAlchemy
session commit races this holder and the writer's
spinner (set by ``PRAGMA busy_timeout`` above) is
what produces the ``sqlalchemy.exc.OperationalError``
the helper has to retry on.
"""
import sqlite3 as _sqlite3
raw = _sqlite3.connect(str(db_path), isolation_level=None)
try:
raw.execute("PRAGMA busy_timeout = 100")
raw.execute("BEGIN IMMEDIATE")
raw.execute(
"INSERT OR REPLACE INTO rate_estimates "
"(engine, base_wait) VALUES ('other', 0.0)"
)
holder_started.set()
holder_release.wait(timeout=5.0)
raw.execute("COMMIT")
finally:
raw.close()
def sqlalchemy_write_callable():
"""Replicate ``_persist_estimate``: a fresh
SQLAlchemy session per attempt, a row insert/update,
and ``session.commit()`` -- which is where the wrapped
``OperationalError`` surfaces on contention.
Uses ``with sessionmaker()() as session:`` (the same
context-manager shape ``metrics_writer.get_session``
uses in production) and wraps the body in
``try``/``except`` so we can record the wrapped
``OperationalError`` type before re-raising for the
helper to retry on. The session's ``__exit__``
rolls back on the exception; we just re-raise so
``retry_on_db_lock`` sees the failure.
"""
attempt_count.append(1)
Session = sessionmaker(bind=engine)
with Session() as session:
try:
session.execute(
__import__("sqlalchemy").text(
"INSERT OR REPLACE INTO rate_estimates "
"(engine, base_wait) VALUES "
"('engine_x', 1.5)"
)
)
session.commit()
return "ok"
except SAOperationalError as exc:
observed_error_types.append(type(exc))
raise
t_holder = threading.Thread(target=hold_writer_lock)
t_holder.start()
assert holder_started.wait(timeout=2.0)
# Holder releases 2.5 s from now -- well past the
# connection's busy_timeout (100 ms) so the SQLAlchemy
# writer's first three commits time out before the lock
# is released. Helper backoff (0.1 s base, exponential:
# 0.1 / 0.2 / 0.4 / 0.8 / 1.6 s between attempts) and
# five timeouts of 100 ms each give a worst-case ~2.4 s
# before the helper gives up; the 2.5 s release lands
# the holder's COMMIT in the gap before the sixth
# attempt commits successfully.
def release_holder():
time.sleep(
2.5
) # allow: unmarked-sleep -- thread-coordination delay
holder_release.set()
t_release = threading.Thread(target=release_holder)
t_release.start()
result = retry_on_db_lock(
sqlalchemy_write_callable,
attempts=6,
base_delay_seconds=0.1,
)
t_release.join()
t_holder.join()
assert result == "ok"
# The first attempt must have hit a SQLAlchemy-wrapped
# OperationalError (not raw sqlite3.OperationalError) --
# if this is empty the helper is no-oping on the wrapped
# path and the production fix is dead code.
assert observed_error_types, (
"Writer did not raise sqlalchemy.exc.OperationalError; "
"either the holder didn't actually hold the writer "
"lock or the helper is not catching the SQLAlchemy "
"wrapper. Helper is silently a no-op on this path."
)
assert all(
issubclass(t, SAOperationalError) for t in observed_error_types
)
# And the helper must have retried: the production fix is
# worthless if it propagates after one call.
assert len(attempt_count) >= 2, (
f"Expected ≥2 attempts (initial + retry), got "
f"{len(attempt_count)}. Without retry the production "
"rate-limit tracker still logs "
"'Failed to persist rate limit estimate' on the "
"first lock."
)
# Durable row: the rate-limit-shaped table actually
# persisted the value. The whole point of the helper is
# that the writer's commit lands despite contention.
with engine.connect() as conn:
row = conn.execute(
__import__("sqlalchemy").text(
"SELECT base_wait FROM rate_estimates "
"WHERE engine = 'engine_x'"
)
).fetchone()
assert row is not None, (
"The row was not persisted; the retry succeeded "
"but the transaction was rolled back."
)
assert row[0] == 1.5
finally:
engine.dispose()