Concurrency Catalog

Canonical Scenarios & Fixes

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

Auction Dirty Write

Two concurrent bidders overwrite separate columns of the same auction row without strict 2PL locking.

Schema and seed data

schema.sql & seed.sql

-- Schema
CREATE TABLE auctions (
    id INT PRIMARY KEY,
    item TEXT NOT NULL,
    highest_bid INT NOT NULL,
    winner TEXT NOT NULL
);

-- Seed
INSERT INTO auctions VALUES (1, 'Rare Stamp', 100, 'Original');

Concurrent workload (chaos.yaml)

chaos.yaml

version: "1.0"
name: "auction_dirty_write"
operations:
  - name: "bid_alice"
    steps:
      - "UPDATE auctions SET highest_bid = 150 WHERE id = 1"
      - "UPDATE auctions SET winner = 'Alice' WHERE id = 1"
  - name: "bid_bob"
    steps:
      - "UPDATE auctions SET highest_bid = 200 WHERE id = 1"
      - "UPDATE auctions SET winner = 'Bob' WHERE id = 1"
invariants:
  - name: "bid_winner_consistency"
    query: "SELECT highest_bid, winner FROM auctions WHERE id = 1;"
    assert: "(highest_bid == 150 AND winner == 'Alice') OR (highest_bid == 200 AND winner == 'Bob')"

Why the invariant breaks

Without atomic row locking or single-statement updates, Alice writes bid 150, Bob writes bid 200, but Alice's second step writes winner='Alice', creating an inconsistent state: $200 bid won by Alice.

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

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

Production fix

Multi-Column Atomic Update with Monotonic Predicate

Dirty Writes (G0) occur when uncommitted writes from concurrent transactions interleave on the same physical row. The definitive fix combines price and winner updates into a single atomic UPDATE statement guarded by a monotonic predicate (WHERE id = 1 AND highest_bid < new_bid). This enforces strict 2PL lock retention and guarantees monotonic bid progression without hybrid corruption.

✓ PostgreSQL✓ SQLite✓ MySQL

PostgreSQL, SQLite, MySQL

remediation_fix.sql

-- Atomic Single-Statement Update with Monotonic Guard
BEGIN;
UPDATE auctions 
SET highest_bid = 200, 
    winner = 'Bob' 
WHERE id = 1 AND highest_bid < 200;

-- If rows_affected == 0, the bid has already been surpassed concurrently;
-- Issue ROLLBACK and notify bidder.
COMMIT;