db-concurrency

db-concurrency (M14)

Correctness under concurrent writes is a Performance & Scale (axis performance) concern: it governs both throughput and whether data stays consistent under load. Feeds relational Concurrencia w12 (and the Idempotencia category in KV/document/wide-column profiles).

What it checks

1. Isolation level — is the workload's chosen isolation (READ COMMITTED default vs REPEATABLE READ/SERIALIZABLE) appropriate for its invariants? Money/inventory updates under READ COMMITTED without locking are exposed to anomalies.
2. Lost update / read-modify-write — SELECT balance; …app math…; UPDATE balance=$new without SELECT … FOR UPDATE, atomic UPDATE … SET x = x - $n, or optimistic version check. Classic double-spend race.
3. Queue contention — worker pollers using SELECT … FOR UPDATE without SKIP LOCKED serialize all workers onto one row; recommend FOR UPDATE SKIP LOCKED.
4. Idempotency (KV/doc/WC) — writes to non-transactional stores (Redis, DynamoDB, Cassandra, Mongo without a session) lacking an idempotency key / conditional write, so a retry double-applies.
5. Advisory locks — when a critical section has no row to lock, recommend Postgres advisory locks to serialize it: pg_advisory_xact_lock() (transaction-scoped, auto-released at commit/rollback) for leader election, single-runner cron jobs, and migration guards. Prefer the _xact_ variant over session-scoped pg_advisory_lock() so the lock can't leak on a dropped connection. Note (cross-ref M15): session-scoped advisory locks break transaction-mode pooling (PgBouncer) — the lock can land on a different backend than the unlock; transaction-scoped advisory locks are pooling-safe.

Score / axis

Feeds performance only (relational Concurrencia w12; Idempotencia in KV w18 / document / wide-column profiles).

Tier-0 (static)

Grep ORM/transaction source for isolation settings, read-then-write update sequences without locking, FOR UPDATE lacking SKIP LOCKED, and missing conditional-write / idempotency keys on KV/doc/WC writes. Static findings are directional — actual contention is runtime (needs_api / Tier-2).