Database concurrency audit

spaCR’s measurement workers, annotation writer, run-status ledger, schema migrations, and Database Browser can access the same SQLite file from different processes or threads. The shared spacr.database_concurrency contract makes those accesses explicit:

• every thread or process owns and closes its own connection;

• every connection has a finite busy_timeout and foreign-key enforcement;

• read-only work uses SQLite mode=ro plus query_only;

• multi-statement writes use BEGIN IMMEDIATE and roll back completely on a body or commit failure;

• only lock/busy failures while acquiring a transaction are retried, with a bounded exponential backoff;

• an exhausted lock budget raises spacr.database_concurrency.DatabaseBusy instead of dropping a write or continuing silently.

Measurement writes retain their specialized recovery for concurrent CREATE TABLE and schema-widening races. Run-status table creation and row insertion are now one atomic transaction. Database Browser edit validation and its single-row update also share one transaction, closing the former gap between checking a row address and writing it. Resume’s multi-table delete-before-remeasure validates and deletes under one retried write transaction. Annotate preserves the configured journal mode, rolls back a failed coalesced batch, retains an unsaved/error state, and reports that state in the module instead of marking a failed commit as saved.

Journal mode and network storage

spaCR does not enable WAL automatically. WAL is useful for concurrent local readers and writers, but SQLite’s WAL index requires shared-memory coordination and is not safe on many NFS, SMB, NAS, or distributed filesystems. Existing databases retain their journal mode unless a caller explicitly requests WAL or DELETE.

spacr.database_concurrency.inspect_database() reports the active journal mode, filesystem type, lock timeout, SQLite threading level, sidecar sizes, and optional PRAGMA quick_check result. It emits a warning when it detects WAL on a known network filesystem. Filesystem detection is advisory: storage inside a container or automounter can conceal its actual backing system, so vendor guidance remains authoritative.

Command-line audit

Inspect an existing database without modifying it:

spacr-db-audit /data/plate/measurements/measurements.db --quick-check

Run simultaneous readers and writers against a new disposable database:

spacr-db-audit --probe --writers 4 --readers 3 --writes 100

Use --json for CI or monitoring. --scratch PATH is accepted only when PATH does not exist; the audit deliberately refuses to place probe tables inside scientific results. Without --scratch, its temporary database is removed after metrics are collected. The command returns nonzero for corrupt input, failed integrity checks, thread errors, timeouts, or a row-count mismatch.

Transaction API

Plugin and pipeline writers should use the same primitives:

from spacr.database_concurrency import connect, transaction

connection = connect("measurements.db", timeout=30)
try:
    with transaction(connection):
        connection.execute(
            "INSERT INTO audit_event(name, value) VALUES (?, ?)",
            ("complete_field", "plate1_A01_1"),
        )
finally:
    connection.close()

Connections must never be passed between threads. Do not retry statements from inside a transaction: earlier statements might already have run. The context manager retries only transaction acquisition, then either commits the complete body once or rolls it back.

Stress coverage

tests/test_database_concurrency.py uses real database files to verify:

• exact row counts under simultaneous reader/writer pressure;

• lock release and bounded lock exhaustion;

• atomic success, rollback, and nested-transaction refusal;

• enforced read-only connections and WAL snapshot visibility;

• concurrent run-ledger stamps with no lost rows;

• annotation-batch rollback and fail-loud status;

• atomic resume cleanup across every measure-owned table;

• integrity/network-storage diagnostics and CLI exit behavior;

• refusal to run a destructive probe against an existing database.

The existing Measure multiprocessing, schema migration, unreadable run-status, and Qt Database Browser suites provide integration coverage for their respective production paths.

API reference

SQLite connection, transaction, and concurrency-audit primitives.

spaCR uses one SQLite database as the meeting point for Measure worker processes, the annotator writer thread, read-only GUI queries, run-status stamps, and schema migrations. This module provides the rules those paths share:

• every thread/process opens and closes its own connection;

• busy timeouts are explicit and write transactions retry only lock errors;

• multi-statement writes use BEGIN IMMEDIATE with rollback on every error;

• WAL is opt-in because SQLite WAL shared memory is unsafe on many network filesystems;

• a real reader/writer probe can verify the local SQLite/filesystem behavior.

Only the Python standard library is imported, so image workers can use it without pulling in pandas, Qt, torch, or Cellpose.

class spacr.database_concurrency.ConcurrencyProbeResult(path: str, journal_mode: str, writers: int, readers: int, writes_per_writer: int, expected_rows: int, actual_rows: int, reader_queries: int, duration_seconds: float, errors: ~typing.Sequence[str] = <factory>)

Bases: object

Outcome of a disposable simultaneous reader/writer stress probe.

actual_rows: int

duration_seconds: float

errors: Sequence[str]

expected_rows: int

journal_mode: str

property ok: bool

True when no thread failed and every committed row exists.

path: str

reader_queries: int

readers: int

to_dict() → Dict[str, Any]

Return a JSON-serializable result including ok.

writers: int

writes_per_writer: int

exception spacr.database_concurrency.DatabaseBusy

Bases: OperationalError

A lock remained busy after the configured retry budget.

exception spacr.database_concurrency.DatabaseConfigurationError

Bases: RuntimeError

SQLite could not apply a requested safety configuration.

class spacr.database_concurrency.DatabaseHealth(path: str, sqlite_version: str, sqlite_threadsafe: int, journal_mode: str, foreign_keys: bool, busy_timeout_ms: int, filesystem: str | None, network_filesystem: bool, quick_check: str | None, file_bytes: int, wal_bytes: int, shm_bytes: int, warnings: ~typing.Sequence[str] = <factory>)

Bases: object

Read-only SQLite configuration and integrity snapshot.

busy_timeout_ms: int

file_bytes: int

filesystem: str | None

foreign_keys: bool

journal_mode: str

network_filesystem: bool

path: str

quick_check: str | None

shm_bytes: int

sqlite_threadsafe: int

sqlite_version: str

to_dict() → Dict[str, Any]

Return a JSON-serializable snapshot.

wal_bytes: int

warnings: Sequence[str]

spacr.database_concurrency.connect(path: PathLike | str, *, readonly: bool = False, timeout: float = 30.0, journal_mode: str | None = None, foreign_keys: bool = True) → Connection

Open one configured connection owned by the calling thread.

Parameters:

• path – SQLite database path.

• readonly – open with URI mode=ro and query_only=ON.

• timeout – seconds SQLite waits inside a lock operation.

• journal_mode – optional explicit "WAL" or "DELETE". Omit to preserve the database’s current mode. WAL must not be enabled blindly on shared/NFS storage; use filesystem_type() or the concurrency probe first.

• foreign_keys – enable SQLite foreign-key enforcement on this connection. SQLite defaults it off per connection.

Returns:

connection in autocommit mode; use transaction() for multi-statement writes.

Raises:

DatabaseConfigurationError – for an unsafe/unsupported requested journal mode or when SQLite refuses to apply it.

spacr.database_concurrency.filesystem_type(path: PathLike | str) → str | None

Best-effort Linux filesystem type for path; None elsewhere.

The longest matching mount point in /proc/mounts wins. This is advisory only—containers and automounters can hide the real backing store.

spacr.database_concurrency.inspect_database(path: PathLike | str, *, quick_check: bool = False, timeout: float = 5.0) → DatabaseHealth

Inspect journal/locking configuration without changing the database.

spacr.database_concurrency.is_busy_error(error: BaseException) → bool

Return True only for SQLite lock/busy errors worth retrying.

spacr.database_concurrency.run_concurrency_probe(path: PathLike | str | None = None, *, writers: int = 4, readers: int = 3, writes_per_writer: int = 50, journal_mode: str = 'WAL') → ConcurrencyProbeResult

Stress a new disposable database with simultaneous readers/writers.

An explicit path must not exist: the probe never adds audit tables to scientific data. When omitted, a temporary database is created and removed after its metrics are collected.

spacr.database_concurrency.transaction(connection: Connection, *, mode: str = 'IMMEDIATE', attempts: int = 8, initial_delay: float = 0.01, maximum_delay: float = 0.25, busy_timeout: float | None = None) → Iterator[Connection]

Run an all-or-nothing transaction with bounded lock retry.

Only BEGIN is retried. Once a transaction starts, retrying individual statements could duplicate earlier writes. Any body or commit error rolls the complete transaction back and propagates.

Parameters:

• connection – calling thread’s open autocommit connection.

• mode – DEFERRED, IMMEDIATE (default), or EXCLUSIVE.

• attempts – maximum attempts to acquire the transaction.

• initial_delay – first backoff between lock failures.

• maximum_delay – backoff cap.

• busy_timeout – total seconds this transaction may spend waiting on locks inside SQLite, shared over attempts and floored at MINIMUM_ATTEMPT_BUSY_TIMEOUT_MS per attempt. Omit to inherit the connection’s configured busy_timeout; pass it when the write’s own tolerance differs from whatever timeout the connection happened to be opened with.

Raises:

• DatabaseBusy – when the lock outlives the retry budget.

• RuntimeError – when asked to nest inside an active transaction.

Command-line SQLite health and concurrency audit.

spacr.cli_database.build_parser() → ArgumentParser

Build the spacr-db-audit argument parser.

spacr.cli_database.main(argv=None) → int

Inspect/probe SQLite and return zero only when every requested check passes.