~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
"""Regression tests for the ``db_config.busy_timeout_ms`` setting.
Background
----------
SQLite serialises all writes through a single file lock. When a writer
tries to commit while another transaction holds the lock, SQLite blocks
the writer for up to ``busy_timeout`` milliseconds before failing with
``SQLITE_BUSY`` ("database is locked").
The background document scheduler holds a session across multi-second
PDF downloads (it queries ``ResearchHistory``, then iterates calling
``DownloadService.download_as_text`` per resource, then commits the
``last_run`` update). Under the old default of ``busy_timeout=10000`` (10 s),
short writers like the rate-limit tracker would fail after ~10 s of
waiting and the failure was logged as
``Failed to persist rate limit estimate`` -- even though their commit
would have succeeded a few hundred milliseconds later if they'd been
allowed to wait.
This file pins three things:
1. **Default value** is now ``30000`` ms (30 s) -- gives short writers
enough headroom to land their commit during a long in-flight
transaction.
2. **Env var override** -- ``LDR_DB_CONFIG_BUSY_TIMEOUT_MS`` (and the
deprecated alias ``LDR_DB_BUSY_TIMEOUT_MS``) lets operators tune the
timeout for their workload without recompiling.
3. **Under contention** -- a writer that lands inside another writer's
transaction waits up to the configured timeout, then succeeds.
The integration test (``test_writer_waits_for_long_transaction``) uses
two threads on the same SQLite file to reproduce the production pattern:
one thread holds a write transaction for longer than the writer's
``busy_timeout`` would be in the buggy version, then commits; the other
thread's commit waits, then succeeds.
"""
import shutil
import tempfile
import threading
import time
from pathlib import Path
import pytest
from local_deep_research.database.sqlcipher_compat import (
get_sqlcipher_module,
)
from local_deep_research.database.sqlcipher_utils import (
apply_cipher_defaults_before_key,
apply_performance_pragmas,
set_sqlcipher_key,
)
# ---------------------------------------------------------------------------
# Fixtures
# ---------------------------------------------------------------------------
@pytest.fixture
def temp_db_path():
"""A fresh database path; cleaned up at the end."""
temp_dir = tempfile.mkdtemp()
db_path = Path(temp_dir) / "test_busy_timeout.db"
yield db_path
shutil.rmtree(temp_dir, ignore_errors=True)
@pytest.fixture
def sqlcipher_module():
"""The SQLCipher module (sqlcipher3 or sqlite3 fallback)."""
return get_sqlcipher_module()
@pytest.fixture(autouse=True)
def _clean_env(monkeypatch):
"""Strip any operator-supplied busy_timeout override so tests get
the default. Tests that need to override set the variable themselves.
"""
for var in (
"LDR_DB_CONFIG_BUSY_TIMEOUT_MS",
"LDR_DB_BUSY_TIMEOUT_MS",
):
monkeypatch.delenv(var, raising=False)
def _open_and_configure(sqlcipher_module, db_path, password="pw"):
"""Open a SQLCipher connection with the full production pragma set."""
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()
return conn
# ---------------------------------------------------------------------------
# Default + env var
# ---------------------------------------------------------------------------
class TestBusyTimeoutDefault:
"""The default bumps from 10 s to 30 s, with a comment explaining why."""
def test_default_is_30000ms(self, sqlcipher_module, temp_db_path):
"""The production default is 30 s. The bump from 10 s gives short
writers like the rate-limit tracker enough headroom to land their
commit during a long in-flight transaction.
"""
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result is not None
assert result[0] == 30000, (
f"Expected default busy_timeout=30000, got {result[0]}. "
"If this is 10000, the production fix has been reverted."
)
finally:
conn.close()
def test_env_var_can_raise_it(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""Operators who want even more headroom set the env var."""
monkeypatch.setenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", "60000")
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 60000
finally:
conn.close()
def test_env_var_can_lower_it(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""Operators on hardware where 30 s is too generous can shorten it."""
monkeypatch.setenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", "5000")
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 5000
finally:
conn.close()
def test_deprecated_alias_still_works(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""The legacy ``LDR_DB_BUSY_TIMEOUT_MS`` alias still wins when set."""
monkeypatch.delenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", raising=False)
monkeypatch.setenv("LDR_DB_BUSY_TIMEOUT_MS", "15000")
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 15000
finally:
conn.close()
def test_out_of_range_env_value_falls_back_to_default(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""Out-of-range ``busy_timeout`` values (below 100 ms or above
300 000 ms) silently fall back to the production default of
30 000 ms. ``SettingsRegistry.get`` catches the resulting
``EnvironmentValueRangeError`` (a ``ValueError`` subclass)
and returns the setting's ``default`` so the connection
doesn't crash on open.
Pinning this so a future refactor that switches to a
fail-loud policy (e.g. ``raise`` instead of fallback) is
intentional rather than silent: the only safe alternative
is to abort startup, not to clamp the value without telling
the operator.
"""
for var in (
"LDR_DB_CONFIG_BUSY_TIMEOUT_MS",
"LDR_DB_BUSY_TIMEOUT_MS",
):
monkeypatch.setenv(var, "50") # below the 100 ms minimum
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 30000, (
f"Out-of-range {var}=50 should fall back to the "
f"30000 default; got {result[0]}. If this is 50, "
"the registry started honouring the bad value "
"instead of falling back."
)
finally:
conn.close()
monkeypatch.delenv(var, raising=False)
for var in (
"LDR_DB_CONFIG_BUSY_TIMEOUT_MS",
"LDR_DB_BUSY_TIMEOUT_MS",
):
monkeypatch.setenv(var, "999999") # above the 300000 max
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 30000, (
f"Out-of-range {var}=999999 should fall back to "
f"the 30000 default; got {result[0]}. If this is "
"999999, the registry started honouring the bad "
"value instead of falling back."
)
finally:
conn.close()
monkeypatch.delenv(var, raising=False)
def test_non_numeric_env_value_falls_back_to_default(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""Non-numeric ``busy_timeout`` values (the typical
operator typo: ``LDR_DB_CONFIG_BUSY_TIMEOUT_MS=thirty``)
silently fall back to the 30 s default. The
``IntegerSetting._convert_value`` warning path fires; we
don't fail loud because the operator's mainline behaviour
is the DB still opens.
"""
monkeypatch.setenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", "thirty")
conn = _open_and_configure(sqlcipher_module, temp_db_path)
try:
result = conn.execute("PRAGMA busy_timeout").fetchone()
assert result[0] == 30000
finally:
conn.close()
# ---------------------------------------------------------------------------
# Integration: a writer inside a long transaction waits and succeeds
# ---------------------------------------------------------------------------
class TestBusyTimeoutUnderContention:
"""Reproduce the production pattern: one writer's long transaction
forces another writer to wait up to ``busy_timeout`` before its
commit can land."""
@staticmethod
def _init_db(sqlcipher_module, db_path):
conn = _open_and_configure(sqlcipher_module, db_path)
conn.execute(
"CREATE TABLE IF NOT EXISTS counters ("
" id INTEGER PRIMARY KEY,"
" n INTEGER NOT NULL"
")"
)
conn.execute("INSERT OR REPLACE INTO counters (id, n) VALUES (1, 0)")
conn.commit()
conn.close()
@staticmethod
def _run_contention(sqlcipher_module, db_path, holder_duration):
# Finish connection setup (including SQLCipher key derivation) before
# either thread attempts a write. Measure the contended UPDATE itself.
ready = threading.Barrier(2, timeout=10)
lock_held = threading.Event()
writer_started = threading.Event()
writer_result = {
"elapsed": None,
"error": None,
"busy_timeout_ms": None,
"holder_elapsed": None,
}
holder_errors = []
def holder():
try:
conn = _open_and_configure(sqlcipher_module, db_path)
try:
ready.wait()
conn.execute("BEGIN IMMEDIATE")
hold_started = time.monotonic()
conn.execute("UPDATE counters SET n = n + 100 WHERE id = 1")
lock_held.set()
assert writer_started.wait(10), (
"Writer did not attempt UPDATE"
)
time.sleep(holder_duration)
conn.execute(
"UPDATE counters SET n = n + 1000 WHERE id = 1"
)
conn.commit()
writer_result["holder_elapsed"] = (
time.monotonic() - hold_started
)
finally:
conn.close()
except Exception as exc:
holder_errors.append(exc)
ready.abort()
def writer():
try:
conn = _open_and_configure(sqlcipher_module, db_path)
try:
writer_result["busy_timeout_ms"] = conn.execute(
"PRAGMA busy_timeout"
).fetchone()[0]
ready.wait()
assert lock_held.wait(10), (
"Holder did not acquire writer lock"
)
start = time.monotonic()
writer_started.set()
try:
conn.execute(
"UPDATE counters SET n = n + 1 WHERE id = 1"
)
conn.commit()
finally:
writer_result["elapsed"] = time.monotonic() - start
finally:
conn.close()
except Exception as exc:
writer_result["error"] = exc
ready.abort()
t_holder = threading.Thread(target=holder, name="busy-timeout-holder")
t_writer = threading.Thread(target=writer, name="busy-timeout-writer")
t_holder.start()
t_writer.start()
t_holder.join(15)
t_writer.join(15)
assert not t_holder.is_alive(), "Holder thread did not finish"
assert not t_writer.is_alive(), "Writer thread did not finish"
assert not holder_errors, f"Holder failed: {holder_errors!r}"
assert writer_result["elapsed"] is not None, (
"Writer did not attempt UPDATE"
)
conn = _open_and_configure(sqlcipher_module, db_path)
try:
writer_result["value"] = conn.execute(
"SELECT n FROM counters WHERE id = 1"
).fetchone()[0]
finally:
conn.close()
return writer_result
def test_writer_waits_for_long_transaction(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""The production bug was: a short writer (the rate-limit
tracker) would fail with ``database is locked`` after
``busy_timeout`` ms if it landed inside a long transaction (the
document scheduler's PDF-download loop). The fix sets the
default ``busy_timeout`` to 30 000 ms so short writers get
enough headroom to land their commit.
This test reproduces the pattern with two threads sharing a
single SQLite file. ``thread_holder`` opens a write
transaction and sleeps for ``holder_duration`` before
committing; ``thread_writer`` tries to commit a counter
increment immediately after. We pin two properties:
1. **The writer waits** -- its elapsed time is at least
``holder_duration`` (proving it actually blocked on the
holder's transaction rather than failing fast or running
concurrently).
2. **The writer commits** -- the elapsed time is below
``busy_timeout`` (proving the timeout was large enough for
the commit to land).
We force ``busy_timeout`` to 2 s via the env var so the test
runs in seconds, not the default 30 s. ``holder_duration`` is
1 s -- well inside the timeout, so the writer's commit lands
after the holder commits. The default-value test
(``TestBusyTimeoutDefault::test_default_is_30000ms``) pins
the production default.
"""
# Force a tight timeout so the test runs in seconds, not the
# default 30 s.
monkeypatch.setenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", "2000")
self._init_db(sqlcipher_module, temp_db_path)
holder_duration = 1.0 # seconds -- well within the 2 s timeout
writer_result = self._run_contention(
sqlcipher_module, temp_db_path, holder_duration
)
# The writer must have waited for the holder to commit, not
# failed. If the writer finished in less than
# ``holder_duration``, the lock wasn't actually held and the
# test is degenerate -- surface that as a failure rather
# than passing silently.
assert writer_result["busy_timeout_ms"] == 2000, writer_result
assert writer_result["error"] is None, (
f"Writer raised {writer_result['error']!r}; the configured "
"busy_timeout (2 s) should have been long enough to wait "
f"out the holder's {holder_duration}s lock. "
f"Actual contention: {writer_result!r}"
)
assert writer_result["elapsed"] >= holder_duration * 0.8, (
f"Writer elapsed ({writer_result['elapsed']:.2f}s) was much "
f"shorter than the holder's lock-hold ({holder_duration}s); "
"the test isn't actually exercising lock contention -- the "
"holder's BEGIN IMMEDIATE may have lost the writer lock."
)
# And the writer landed inside the timeout (otherwise the
# previous assertion's contract is moot).
assert writer_result["elapsed"] < 2.0, (
f"Writer elapsed ({writer_result['elapsed']:.2f}s) was not "
"less than the configured busy_timeout (2.0 s); the holder "
"may not have held the writer lock for the full duration."
)
assert writer_result["value"] == 1101
def test_writer_times_out_when_holder_exceeds_timeout(
self, sqlcipher_module, temp_db_path, monkeypatch
):
"""The complementary failure-path test: when the holder's
transaction runs longer than ``busy_timeout``, a concurrent
writer must fail with ``database is locked`` -- not block
forever.
Production corollary: this is the scenario the rate-limit
tracker hit in 2026-09-21. The fix is to give writers enough
timeout to ride out normal contention (30 s), not to make the
timeout infinite. If a transaction runs longer than
``busy_timeout``, the writer's commit failing is the correct
behaviour -- it surfaces the long-running transaction rather
than masking it behind an indefinite hang.
"""
# Tight timeout for a fast test.
monkeypatch.setenv("LDR_DB_CONFIG_BUSY_TIMEOUT_MS", "500")
self._init_db(sqlcipher_module, temp_db_path)
# Hold for longer than the timeout so the writer is guaranteed
# to hit it.
holder_duration = 1.5 # seconds
writer_result = self._run_contention(
sqlcipher_module, temp_db_path, holder_duration
)
# The writer must have failed with a "database is locked" error
# after waiting approximately ``busy_timeout`` (0.5 s here).
assert writer_result["busy_timeout_ms"] == 500, writer_result
assert writer_result["error"] is not None, (
"Writer did not raise; a 1.5 s holder against a 0.5 s "
"busy_timeout should have caused the writer to time out."
)
error_msg = str(writer_result["error"])
assert "locked" in error_msg.lower(), (
f"Expected 'database is locked' error, got {error_msg!r}"
)
# The writer waited roughly the timeout duration before failing,
# proving it tried to wait rather than failing immediately.
assert writer_result["elapsed"] >= 0.4, (
f"Writer elapsed ({writer_result['elapsed']:.2f}s) was "
"much shorter than the configured busy_timeout (0.5 s); "
"the writer may not have actually waited."
)
assert writer_result["value"] == 1100