Skip to content

~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.

18.5 KB
Raw Blame
"""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