Concurrency Catalog

Canonical Scenarios & Fixes

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

G-DL

Deadlock Cycle & Recovery

Two transfer workers lock accounts in reverse order, inducing a cyclic lock-wait graph (WFG) deadlock.

Schema and seed data

schema.sql & seed.sql

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

-- Seed
INSERT INTO accounts VALUES (1, 500), (2, 500);

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "deadlock_cycle_recovery"
operations:
  - name: "transfer_1_to_2"
    steps:
      - "SELECT balance FROM accounts WHERE id = 1 -> b1"
      - "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
      - "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
  - name: "transfer_2_to_1"
    steps:
      - "SELECT balance FROM accounts WHERE id = 2 -> b2"
      - "UPDATE accounts SET balance = balance - 100 WHERE id = 2"
      - "UPDATE accounts SET balance = balance + 100 WHERE id = 1"
invariants:
  - name: "total_balance_exact"
    query: "SELECT sum(balance) AS total FROM accounts;"
    assert: "total == 1000"

Why the invariant breaks

T1 locks Account 1 and requests lock on Account 2. Concurrently, T2 locks Account 2 and requests lock on Account 1. Mutual wait forms a cycle in the Wait-For Graph (WFG).

T1 ──(waits)──► T2 ──(waits)──► T1

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

Production fix

Deterministic Canonical Lock Ordering

Deadlocks emerge when concurrent transactions acquire locks on the same resources in opposing sequences (T1 locks 1 then 2; T2 locks 2 then 1). The canonical solution enforces a strict resource hierarchy before acquiring any lock (e.g. always acquiring locks in ascending ID order). With canonical lock ordering at the repository layer, cycles in the Wait-For Graph become mathematically impossible.

✓ PostgreSQL (Code 40P01)✓ MySQL (Error 1213), SQLite

PostgreSQL (Code 40P01), MySQL (Error 1213), SQLite

remediation_fix.sql

-- Standardized Lock Ordering by Ascending Primary Key
-- In application layer:
-- first_id = MIN(account_a, account_b)
-- second_id = MAX(account_a, account_b)

BEGIN;
-- Always acquire exclusive locks in ascending canonical order:
SELECT id, balance FROM accounts WHERE id = :first_id FOR UPDATE;
SELECT id, balance FROM accounts WHERE id = :second_id FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = :source_id;
UPDATE accounts SET balance = balance + 100 WHERE id = :target_id;
COMMIT;