Concurrency Catalog

Canonical Scenarios & Fixes

Inspect 10 concurrency failure scenarios based on academic isolation literature and real-world financial/distributed systems.

A5A

Financial Read Skew

An audit query observes a partially applied transfer, detecting an artificial discrepancy in total wealth.

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance INT NOT NULL
);

-- Seed
INSERT INTO accounts VALUES (1, 1000), (2, 1000);

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "financial_read_skew"
operations:
  - name: "transfer_100"
    steps:
      - "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
      - "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
  - name: "audit_total"
    steps:
      - "SELECT balance FROM accounts WHERE id = 1 -> b1"
      - "SELECT balance FROM accounts WHERE id = 2 -> b2"
invariants:
  - name: "total_wealth_preserved"
    query: "SELECT sum(balance) AS total FROM accounts;"
    assert: "total == 2000"

Why the invariant breaks

Under READ COMMITTED, each SELECT inside the audit transaction takes a separate snapshot. Audit reads account 1 before transfer (-$100), but reads account 2 after transfer (+$100), seeing $1000 + $1100 = $2100.

T1 ──(rw)──► T2 ──(wr)──► T1

Delta debugging reduces 25 operations to the 3 that cause the failure.

Production fix

REPEATABLE READ / SNAPSHOT Isolation for Audit Transactions

Under READ COMMITTED, each SELECT statement constructs a new point-in-time snapshot. If an independent fund transfer commits between reading account 1 and account 2, the audit query sees balances at different historical moments. Elevating the audit transaction to REPEATABLE READ (or Snapshot Isolation) pins an immutable Point-in-Time Snapshot across all queries, guaranteeing consistent multi-row reads without requiring locks.

✓ PostgreSQL✓ MySQL (InnoDB)✓ SQLite (WAL)

PostgreSQL, MySQL (InnoDB), SQLite (WAL)

remediation_fix.sql

-- Audit Transaction in REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
-- All subsequent queries read from the exact same point-in-time snapshot:
SELECT balance AS bal1 FROM accounts WHERE id = 1;
-- Even if an external transfer commits right here...
SELECT balance AS bal2 FROM accounts WHERE id = 2;
-- bal1 + bal2 will always sum to exactly 2000!
COMMIT;