Concurrency Catalog

Canonical Scenarios & Fixes

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

G1a

Flash Crash Dirty Read

Liquidation bot reads uncommitted margin drop from a transaction that subsequently rolls back.

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE margin_accounts (
    id INT PRIMARY KEY,
    trader TEXT NOT NULL,
    collateral INT NOT NULL,
    status TEXT NOT NULL
);

-- Seed
INSERT INTO margin_accounts VALUES (1, 'Whale_01', 50000, 'ACTIVE');

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "flash_crash_dirty_read"
operations:
  - name: "market_order_rollback"
    steps:
      - "UPDATE margin_accounts SET collateral = 5000 WHERE id = 1"
      - "SELECT 1/0" # Forced division by zero fault to trigger ROLLBACK
  - name: "liquidation_bot"
    steps:
      - "SELECT collateral FROM margin_accounts WHERE id = 1 -> col"
      - "UPDATE margin_accounts SET status = 'LIQUIDATED' WHERE id = 1 AND {col < 10000}"
invariants:
  - name: "no_spurious_liquidation"
    query: "SELECT collateral, status FROM margin_accounts WHERE id = 1;"
    assert: "collateral == 50000 AND status == 'ACTIVE'"

Why the invariant breaks

T1 updates collateral to $5000 and then aborts. Under READ UNCOMMITTED, T2 reads the uncommitted $5000 drop and triggers premature liquidation of a solvent trader.

w1(collateral=5000) ... r2(collateral) ... a1

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

Production fix

Enforce Minimum READ COMMITTED in Database Connection Pool

Dirty Reads (G1a / Aborted Reads) occur when a transaction reads uncommitted mutations from another transaction that subsequently aborts (ROLLBACK). In financial and DeFi systems, this triggers catastrophic phantom liquidations. The architectural fix enforces READ COMMITTED as the strict floor in the connection pool, ensuring queries only access tuples durably committed in the WAL.

✓ PostgreSQL (Default)✓ SQLite (WAL)✓ MySQL (Default)

PostgreSQL (Default), SQLite (WAL), MySQL (Default)

remediation_fix.sql

-- Enforce Minimum READ COMMITTED Isolation Level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Liquidation Routine with Row Guard Lock
BEGIN;
SELECT collateral, status FROM margin_accounts WHERE id = 1 FOR UPDATE;
-- Only durably committed state is visible; aborted transactions never leak!
UPDATE margin_accounts SET status = 'LIQUIDATED' WHERE id = 1 AND collateral < 10000;
COMMIT;