Concurrency Catalog

Canonical Scenarios & Fixes

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

Inventory Oversell

Concurrent checkouts reserve the final available item due to predicate anti-dependencies (phantom reads).

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE products (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    stock INT NOT NULL
);

-- Seed
INSERT INTO products VALUES (1, 'RTX 5090 GPU', 1);

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "inventory_oversell"
operations:
  - name: "checkout"
    steps:
      - "SELECT stock FROM products WHERE id = 1 -> avail"
      - "UPDATE products SET stock = {avail - 1} WHERE id = 1 AND {avail > 0}"
invariants:
  - name: "stock_non_negative"
    query: "SELECT stock FROM products WHERE id = 1;"
    assert: "stock >= 0"

Why the invariant breaks

Both workers evaluate {avail > 0} as true before either commit updates the physical stock row, driving inventory to -1.

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

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

Production fix

Atomic Guarded Decrement with Rows-Affected Check

The guard condition {avail > 0} evaluated in application memory becomes stale by the time writes hit disk. By pushing the check into the WHERE clause of the UPDATE statement (AND stock >= 1), the database engine guarantees atomic predicate verification and exclusive row locks. When multiple clients purchase the final item concurrently, exactly one decrements stock to 0 while others receive 0 rows affected.

✓ PostgreSQL✓ SQLite✓ MySQL (InnoDB)

PostgreSQL, SQLite, MySQL (InnoDB)

remediation_fix.sql

-- Atomic Guarded Decrement in Database Engine
UPDATE products 
SET stock = stock - 1 
WHERE id = 1 AND stock >= 1;

-- In application code (Go / Node):
-- Verify rows_affected == 1.
-- If rows_affected == 0, return ErrOutOfStock without charging payment.