spacr.database_concurrency

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.

Exceptions

DatabaseConfigurationError

SQLite could not apply a requested safety configuration.

Classes

ConcurrencyProbeResult	Outcome of a disposable simultaneous reader/writer stress probe.
DatabaseBusy	A lock remained busy after the configured retry budget.
DatabaseHealth	Read-only SQLite configuration and integrity snapshot.

Functions

connect(→ sqlite3.Connection)	Open one configured connection owned by the calling thread.
enable_wal_where_safe(→ Optional[str])	Put path into WAL when the filesystem allows it. Never raises.
filesystem_type(→ Optional[str])	Best-effort filesystem type for path, or None when unknowable.
inspect_database(→ DatabaseHealth)	Inspect journal/locking configuration without changing the database.
is_busy_error(→ bool)	Return True only for SQLite lock/busy errors worth retrying.
run_concurrency_probe(→ ConcurrencyProbeResult)	Stress a new disposable database with simultaneous readers/writers.
transaction(→ Iterator[sqlite3.Connection])	Run an all-or-nothing transaction with bounded lock retry.
wal_is_safe_here(→ bool)	Is path on a filesystem where WAL is known to behave?

Module Contents

exception spacr.database_concurrency.DatabaseConfigurationError

Bases: RuntimeError

SQLite could not apply a requested safety configuration.

Initialize self. See help(type(self)) for accurate signature.

class spacr.database_concurrency.ConcurrencyProbeResult

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.

to_dict() → Dict[str, Any]

Return a JSON-serializable result including ok.

property ok: bool

True when no thread failed and every committed row exists.

class spacr.database_concurrency.DatabaseBusy

Bases: sqlite3.OperationalError

A lock remained busy after the configured retry budget.

class spacr.database_concurrency.DatabaseHealth

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.

to_dict() → Dict[str, Any]

Return a JSON-serializable snapshot.

spacr.database_concurrency.connect(path: os.PathLike | str, *, readonly: bool = False, timeout: float = 30.0, journal_mode: str | None = None, foreign_keys: bool = True) → sqlite3.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: os.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: os.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: os.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: os.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: sqlite3.Connection, *, mode: str = 'IMMEDIATE', attempts: int = 8, initial_delay: float = 0.01, maximum_delay: float = 0.25, busy_timeout: float | None = None) → Iterator[sqlite3.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: os.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().