Concurrency Catalog

Canonical Scenarios & Fixes

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

Banking Lost Update

Concurrent withdrawals under READ COMMITTED overwrite balances without transactional serialization, silently losing funds.

Schema and seed data

schema.sql & seed.sql

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

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

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "banking_lost_update"
database:
  driver: "sqlite"
operations:
  - name: "withdraw_100"
    steps:
      - "SELECT balance FROM accounts WHERE id = 1 -> cur"
      - "UPDATE accounts SET balance = {cur - 100} WHERE id = 1"
invariants:
  - name: "total_balance_check"
    query: "SELECT balance FROM accounts WHERE id = 1;"
    assert: "balance == 1000 - (total_completed * 100)"

Why the invariant breaks

Two concurrent read-modify-write transactions read balance 1000 simultaneously. T1 writes 900, but T2 overwrites with 900 based on its stale read, silently losing $100.

T1 ──(rw)──► T2 ──(ww)──► T1

Delta debugging reduces 20 operations to the 2 that cause the failure.

Production fix

Atomic Guarded Decrement or Pessimistic Row Locking

The anomaly is caused by the vulnerable Read-Modify-Write anti-pattern under READ COMMITTED: the application reads the balance in a SELECT statement and calculates subtraction in web server memory. If two goroutines read balance=1000 concurrently, both compute 900 and the last UPDATE overwrites the first. The mitigation delegates arithmetic directly to the database engine in a single atomic statement (UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100), serializing execution under row exclusive locks.

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

PostgreSQL, SQLite (WAL), MySQL (InnoDB)

remediation_fix.sql

-- Option 1: Atomic Database-Side Arithmetic (Recommended)
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1 AND balance >= 100;

-- Option 2: Explicit Transaction with Pessimistic Locking
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Application verifies balance >= 100 before issuing update:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;