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 one atomic transaction. Database Browser edit validation and its single-row update also share one transaction, so a row address cannot change between the check and the write. 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 enables WAL only where it is known to be safe. WAL lets readers proceed without blocking a committing writer, but SQLite’s WAL index requires shared-memory coordination and is not safe on many NFS, SMB, NAS, or distributed filesystems. Before a Measure run, spacr.database_concurrency.enable_wal_where_safe() switches measurements/measurements.db to WAL only when its filesystem is positively identified as a local type on an allowlist (ext4, XFS, Btrfs, ZFS, APFS, tmpfs and similar); network, unrecognised and undetectable filesystems keep the rollback (DELETE) journal. Other 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;

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

• refusal to run a destructive probe against an existing database, and removal of a probe’s scratch database when it fails.

Annotation-batch rollback and fail-loud status are covered in tests/qt/test_annotate.py, and resume cleanup across every measure-owned table in tests/test_resume.py. 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.

Parameters:

• path – scratch database path; a clean temporary probe removes it, while explicit or stalled probes retain it for inspection.

• journal_mode – actual uppercase journal mode read after the run.

• writers – validated number of writer threads launched.

• readers – validated number of polling reader threads launched.

• writes_per_writer – one-row committed transactions each writer tries.

• expected_rows – writers * writes_per_writer, independent of any worker failures.

• actual_rows – final row count verified after the bounded joins.

• reader_queries – total successful COUNT queries across readers.

• duration_seconds – monotonic worker start-to-join elapsed time, excluding setup and final verification.

• errors – immutable worker exceptions and surviving-thread timeout messages collected by the 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.

Parameters:

• path – normalized absolute path of the inspected database.

• sqlite_version – SQLite runtime version exposed by Python.

• sqlite_threadsafe – DB-API thread-safety level reported by sqlite3.threadsafety.

• journal_mode – actual uppercase journal mode read from the database.

• foreign_keys – whether enforcement is enabled on the audit connection, not a persistent database-wide promise.

• busy_timeout_ms – audit connection’s effective busy timeout in milliseconds.

• filesystem – detected filesystem type, or None when unavailable.

• network_filesystem – whether the detected type is in the known network-filesystem set; false with an unknown type does not prove the storage is local.

• quick_check – joined PRAGMA quick_check result when requested, otherwise None.

• file_bytes – main database-file size at inspection time.

• wal_bytes – -wal sidecar size at inspection time, or zero when it is absent.

• shm_bytes – -shm sidecar size at inspection time, or zero when it is absent.

• warnings – actionable integrity or unsafe network-WAL findings.

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.WAL_SAFE_FILESYSTEMS = {'apfs', 'btrfs', 'ext2', 'ext3', 'ext4', 'f2fs', 'hfs', 'hfsplus', 'jfs', 'overlay', 'reiserfs', 'tmpfs', 'xfs', 'zfs'}

Filesystems on which WAL is known to behave. An ALLOWLIST, not the complement of NETWORK_FILESYSTEMS: “not a type I recognise as networked” is a weaker claim than “local”, and the gap between them is someone’s corrupted database on a cluster nobody here has seen. An unrecognised type stays on DELETE, which is what shipped.

exFAT and vfat are deliberately absent: neither has the byte-range locking WAL’s shared-memory index depends on.

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.enable_wal_where_safe(path: PathLike | str) → str | None

Put path into WAL when the filesystem allows it. Never raises.

WHY THIS EXISTS (issue #15, “measurements sometimes hangs”). Measure runs one worker per field and every append goes through pandas’ DataFrame.to_sql, which issues a has_table probe – a READ – before writing. So each worker alternates read, write, read, write against one file.

Under the shipped rollback journal that combination starves the writer. A reader holds SHARED for the length of its statement, and a writer cannot COMMIT until every SHARED lock is gone, so with enough workers there is almost always someone reading and the committing worker waits out its busy timeout and raises “database is locked” – usually surfacing on the next process’s has_table, which is the statement in the reporter’s traceback.

Measured on this exact shape, a commit attempted while one reader holds an open SELECT:

journal_mode=delete writer waited 1.037 s journal_mode=wal writer waited 0.002 s

WAL readers do not block a writer at all, which removes the starvation. It does NOT make two WRITERS concurrent – SQLite still serialises those – so this fixes the reader-blocks-writer half, which is the half the traceback is in.

(The first version of this note had the direction backwards, claiming reads were blocked by writes. The test written to prove it measured 0.000 s and refuted it: in rollback-journal mode a writer holding RESERVED does not block readers, only its brief EXCLUSIVE commit does.)

Called once when a database is opened for a run rather than per write: the mode is a property of the FILE and persists, so paying for it per connection would buy nothing.

Parameters:

path – the database to switch. A file that does not exist yet is created by the connection, which is fine – the mode persists.

Returns:

the journal mode in force afterwards, or None when the database could not be opened at all.

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

Best-effort filesystem type for path, or None when unknowable.

Reads /proc/mounts on Linux and falls back to psutil’s partition table elsewhere, so macOS and Windows get a real answer rather than None. The longest matching mount point wins on both paths. Advisory only–containers and automounters can hide the real backing store.

Parameters:

path – file or directory to look up. ~ is expanded and the path resolved; on Linux a path that does not exist yet is walked up to its nearest existing parent before the mount table is searched.

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.

Parameters:

• path – the SQLite database file (~ is expanded). It must already exist and is opened read-only.

• quick_check – also run PRAGMA quick_check and add a warning unless it reports ok.

• timeout – seconds SQLite waits inside a lock operation.

Raises:

FileNotFoundError – when path is not an existing file.

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

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

Parameters:

error – the exception to classify. Only a sqlite3.OperationalError whose message contains “locked” or “busy” (case-insensitively) counts.

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.

Parameters:

• path – scratch database to create. It must not already exist (FileExistsError); missing parent directories are created, and a run that FINISHES leaves the file on disk along with any -wal and -shm sidecars, so every explicit run needs a fresh path. Omit it to probe a temporary database instead, which is removed after a clean finish. A run that RAISES – a journal_mode connect() refuses, most often – removes its scratch database either way: it never ran, so there is nothing in it to keep, and leaving one at an explicit path made the next run on it fail with FileExistsError. The one deliberate survivor is a worker that outlives the 30-second join deadline, whose database is kept for inspection.

• writers – concurrent writer threads. Each owns a connection opened with a 50 ms busy timeout and commits one transaction per row, so expected_rows is writers * writes_per_writer. Must be a genuine positive integer; booleans, text and floats are refused.

• readers – concurrent read-only threads polling COUNT(*) until the last writer exits. They move only reader_queries, never expected_rows; at least one is required, so a writers-only probe cannot be expressed. Must be a genuine positive integer.

• writes_per_writer – rows each writer inserts, one row per transaction. Must be a genuine positive integer.

• journal_mode – mode applied once by the setup connection and then inherited by every worker connection. Only "WAL" (the default) and "DELETE" are accepted, case-insensitively; anything else raises DatabaseConfigurationError after the scratch database has already been created. None is refused: a stress result must state which locking mode it actually intended to exercise.

Returns:

result whose journal_mode is read back from the finished database rather than echoed from this argument, and whose errors carry per-thread failures instead of raising.

Raises:

• ValueError – when writers, readers, or writes_per_writer is below 1.

• TypeError – when one of the work sizes is not an integer. In particular, 2.9 is not silently truncated and "2" is not accepted merely because the CLI parser would have converted it.

• DatabaseConfigurationError – when journal_mode is not an explicit "WAL" or "DELETE" string.

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.

spacr.database_concurrency.wal_is_safe_here(path: PathLike | str) → bool

Is path on a filesystem where WAL is known to behave?

True only for a POSITIVELY IDENTIFIED local filesystem. Anything else – a network type, an unrecognised type, or a platform where filesystem_type() cannot tell (it reads /proc/mounts, so macOS and Windows always answer None) – is False.

That asymmetry is the point. The cost of a wrong False is the lock contention this project already survives; the cost of a wrong True is WAL shared memory on storage that cannot support it, which is a corrupted database.

Parameters:

path – the database file (or its directory) whose filesystem is looked up with filesystem_type().

Command-line SQLite health and concurrency audit.

spacr.cli_database.build_parser() → ArgumentParser

Build the spacr-db-audit argument parser.

Returns:

parser for read-only database checks and disposable concurrency probes.

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

Inspect or probe SQLite and report one combined audit result.

Parameters:

argv – command-line arguments without the program name; None reads the process arguments.

Returns:

0 when every requested integrity check and probe passes, or 1 when any requested operation fails or raises an exception.

Raises:

SystemExit – with status 2 for invalid arguments or when neither a database nor --probe is supplied.