Concurrency Catalog

Canonical Scenarios & Fixes

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

A5B

Hospital Write Skew

Two doctors independently sign off duty under Snapshot Isolation because their write sets do not overlap.

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE doctors (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    on_duty BOOLEAN NOT NULL
);

-- Seed
INSERT INTO doctors VALUES (1, 'Dr. Alice', true), (2, 'Dr. Bob', true);

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "hospital_write_skew"
operations:
  - name: "sign_off_alice"
    steps:
      - "SELECT count(*) AS active FROM doctors WHERE on_duty = true -> act"
      - "UPDATE doctors SET on_duty = false WHERE id = 1 AND {act >= 2}"
  - name: "sign_off_bob"
    steps:
      - "SELECT count(*) AS active FROM doctors WHERE on_duty = true -> act"
      - "UPDATE doctors SET on_duty = false WHERE id = 2 AND {act >= 2}"
invariants:
  - name: "at_least_one_doctor_on_duty"
    query: "SELECT count(*) AS active FROM doctors WHERE on_duty = true;"
    assert: "active >= 1"

Why the invariant breaks

Under Snapshot Isolation, T1 and T2 read the same snapshot (2 active doctors). T1 updates Alice, T2 updates Bob. Because their write sets are disjoint, standard SI permits both commits, leaving 0 doctors on duty.

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

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

Production fix

Elevation to Serializable Snapshot Isolation (SSI) or Materialized Conflict Lock

Write Skew (A5B) is the canonical failure mode of Snapshot Isolation (SI): since T1 writes row 1 and T2 writes row 2, write sets are disjoint (no ww conflict), but generate a cyclic read-write anti-dependency (rw) loop. The standard solution is SERIALIZABLE isolation (where modern SSI engines track rw dependencies and abort concurrent transactions with SQLSTATE 40001 serialization_failure) or synchronizing transactions via a parent roster lock with FOR UPDATE.

✓ PostgreSQL (SSI)✓ CockroachDB✓ MySQL (SERIALIZABLE)

PostgreSQL (SSI), CockroachDB, MySQL (SERIALIZABLE)

remediation_fix.sql

-- Solution A: Elevate Isolation Level to SERIALIZABLE (SSI)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT count(*) FROM doctors WHERE on_duty = true;
-- If count >= 2:
UPDATE doctors SET on_duty = false WHERE id = 1;
COMMIT; -- Engine detects rw conflict cycle and aborts T2 with 40001!

-- Solution B: Shared Pessimistic Lock on Parent Shift Row
BEGIN;
SELECT shift_id FROM duty_shifts WHERE shift_id = 1 FOR UPDATE;
SELECT count(*) FROM doctors WHERE on_duty = true;
UPDATE doctors SET on_duty = false WHERE id = 1;
COMMIT;