🌀Concurrency anomalies

Interleaved histories, surprising reads

When transactions overlap, sessions can observe dirty work, shifting rows, new rows that match a predicate, or mutually consistent reads that still permit a bad write pair (write skew). Which stories remain possible is exactly what isolation levels and locking patterns define.

📋The usual suspects

Name the anomaly before picking the fix.

Anomaly	What the session sees
Dirty read	Uncommitted changes from another transaction, can vanish on rollback.
Non-repeatable read	The same row’s values change between two reads in one transaction.
Phantom read	New rows appear (or disappear) that match a predicate you read earlier.
Write skew	Two transactions read disjoint facts, each passes a local check, and their writes together violate a global invariant, still possible under snapshot isolation on many engines.

SQL

Illustrative timeline (two sessions)

-- Session A                          -- Session B
BEGIN;                                 BEGIN;
SELECT COUNT(*) FROM beds WHERE ward='ICU' AND occupied=FALSE; -- 1
                                       UPDATE beds SET occupied=TRUE WHERE bed_id=12;
                                       COMMIT;
SELECT COUNT(*) …; -- still 1 under RR snapshot
UPDATE beds SET occupied=TRUE WHERE bed_id=11; -- may succeed → overbooking risk

✓Illustrative result set

lesson
Use constraints, SERIALIZABLE, or SELECT … FOR UPDATE patterns for invariants

RC as a universal shield

Believing READ COMMITTED alone prevents lost updates.
Testing concurrency with a single user or single connection.
Using SELECT FOR UPDATE on one code path but not its twin, double spends slip through.

Locks, constraints, or stronger isolation

Declare which anomalies the product forbids; choose isolation, row locks, or SERIALIZABLE accordingly.
Add optimistic version columns on hot rows; log deadlock and serialization failure rates.
Document intentional hot rows (counters) as design, not accidents.

MVCC nuance

Snapshot readers may not see phantoms the same way lock-based systems do, write skew in particular needs explicit modeling, not only “we use Postgres.”

TL;DR

Concurrency anomalies are observable histories forbidden by your isolation level: dirty reads (uncommitted data), non-repeatable reads (same row changes between reads), phantoms (new rows appear in a repeated range query), and lost updates (two writers clobber each other). Stronger isolation reduces anomalies but increases blocking or abort rates; MVCC engines often use snapshots + serialization failures instead of long locks, design retries and idempotency accordingly.