Concurrency Catalog

Canonical Scenarios & Fixes

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

Ticket Anti-Dependency Cycle

Concert ticket reservations generate phantom conflicts across seat range predicates under Snapshot Isolation.

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE seats (
    id INT PRIMARY KEY,
    section TEXT NOT NULL,
    seat_no INT NOT NULL,
    reserved_by TEXT
);

-- Seed
INSERT INTO seats VALUES (1, 'VIP', 101, NULL), (2, 'VIP', 102, NULL);

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "ticket_anti_dependency_g2"
operations:
  - name: "book_adjacent_left"
    steps:
      - "SELECT count(*) AS reserved FROM seats WHERE section = 'VIP' AND reserved_by IS NOT NULL -> c"
      - "UPDATE seats SET reserved_by = 'Fan_A' WHERE id = 1 AND {c == 0}"
  - name: "book_adjacent_right"
    steps:
      - "SELECT count(*) AS reserved FROM seats WHERE section = 'VIP' AND reserved_by IS NOT NULL -> c"
      - "UPDATE seats SET reserved_by = 'Fan_B' WHERE id = 2 AND {c == 0}"
invariants:
  - name: "max_one_seat_rule"
    query: "SELECT count(*) AS reserved FROM seats WHERE section = 'VIP' AND reserved_by IS NOT NULL;"
    assert: "reserved <= 1"

Why the invariant breaks

Predicate anti-dependency cycle: T1 checks if any VIP seat is booked (finds 0) and books seat 1. T2 simultaneously checks if any VIP seat is booked (finds 0) and books seat 2. Both commit, violating VIP exclusivity.

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

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

Production fix

Structural Unique Constraints and Serializable Predicate Locks

G2 cycles manifest when aggregate queries or range checks inspect row existence via predicates (COUNT(*) WHERE ...). Under Snapshot Isolation, queries do not lock non-existent keys. Introducing a schema-level UNIQUE constraint converts silent race conditions into deterministic index integrity violations, while SELECT ... FOR UPDATE or SSI serializes reservations safely.

✓ PostgreSQL✓ MySQL✓ SQLite

PostgreSQL, MySQL, SQLite

remediation_fix.sql

-- 1. Composite Unique Constraint for Index-Level Atomicity
ALTER TABLE seats 
ADD CONSTRAINT uq_section_reservation UNIQUE (section, seat_no);

-- 2. Predicate Lock via SELECT FOR UPDATE or SSI
BEGIN;
SELECT id FROM seats 
WHERE section = 'VIP' AND reserved_by IS NULL 
LIMIT 1 FOR UPDATE;

UPDATE seats SET reserved_by = 'Fan_A' WHERE id = :seat_id;
COMMIT;