Two AI agents fulfill orders in parallel. Both read the same inventory. Both validate. Both commit. Both report SUCCESS ✅. 100 phantom units now in transit.
Product:Apex Pro Runners
SKU:SKU-7701
Starting inventory:300 units
Agent A order:200 units (#A-7741)
Agent B order:200 units (#B-9923)
DB locking:NONE (optimistic)
Agent A
Awaiting assignment...
IDLE
[ waiting for orchestrator ]
Confidence0%
Shared Inventory DB
inventory.stock WHERE sku='SKU-7701'
STOCK
300
SKU-7701 · Apex Pro Runners
🔓 UNLOCKED — no transaction in progress
Write History
— no writes yet —
Agent B
Awaiting assignment...
IDLE
[ waiting for orchestrator ]
Confidence0%
STEP 0 / 11Waiting — press Next Step to begin
12 steps to collision
Execution Timeline
Agent A
Agent B
spawn
assign
read
validate
WRITE
⚡
commit
success
audit
Agent A active
Agent B active
Write collision
Reports success
💥
INVENTORY AUDIT FAILURE — GHOST STOCK DETECTED
Fulfillment pipeline committed 400 units from a stock of 300. 100 phantom units currently in transit.
Phantom units
100
don't exist in warehouse
Revenue at risk
$17,900
100 × $179/pair
DB reads stale?
Both
B's read was stale at t+0ms
Errors surfaced?
Zero
both agents: 100% confidence
[AUDIT 03:14 UTC] Stock reconciliation FAILED
committed_units = 400 (Order A: 200 + Order B: 200)
starting_stock = 300
discrepancy = −100 units
[AUDIT] Root cause: last-write-wins DB semantics + no read locking
Agent A read 300 at t=0ms → wrote 100 at t=18ms
Agent B read 300 at t=2ms → wrote 100 at t=19ms [OVERWROTE A]
Final DB value: 100 (should be −100 if checked)
[FULFILLMENT] 100 shipment labels generated for non-existent units
[FULFILLMENT] Warehouse pick failure will surface in ~4 hours at dispatch
What went wrong
This is a read-modify-write race condition. Both agents followed the correct logic individually:
read inventory → check if sufficient → subtract → write back. Neither agent made a reasoning error.
The failure happened at the systems level, not the agent level.
Agent A read 300 units and began preparing its write. In the 2ms gap before A committed,
Agent B also read the database — and got 300 units, the same stale value.
When A wrote 100 (300−200=100), that was correct. When B wrote 100 (also 300−200=100),
it overwrote A's value with a calculation based on data that was already wrong the moment B read it.
The final DB state of 100 looks plausible. No error was raised. Both agents logged success.
The discrepancy only surfaces when the warehouse tries to pick 400 units from a shelf of 300.
The deeper problem: this failure mode scales quadratically. With N parallel agents, you have
N(N-1)/2 potential write conflicts. Most monitoring tools watch agent outputs — not database consistency.
The agents were working perfectly. The architecture was broken.
✓ The fix — pessimistic locking with SELECT FOR UPDATE
# ❌ BROKEN — agents run this in parallel and corrupt statedeffulfill_order_broken(sku, qty):
stock = db.query("SELECT stock FROM inventory WHERE sku=:s", s=sku)
if stock < qty:
raiseStockError()
db.execute("UPDATE inventory SET stock=:n WHERE sku=:s", n=stock-qty, s=sku)
# ✅ FIXED — row-level lock held until transaction commitsdeffulfill_order_safe(sku, qty):
with db.begin() as txn:
# FOR UPDATE acquires a row lock — B blocks until A commits
stock = txn.query(
"SELECT stock FROM inventory WHERE sku=:s FOR UPDATE",
s=sku
)
if stock < qty:
raiseInsufficientStock(f"need {qty}, have {stock}")
txn.execute(
"UPDATE inventory SET stock=:n WHERE sku=:s",
n=stock-qty, s=sku
)
# Lock releases here — B can now proceed with accurate stock
-- Agent A transaction (holds lock until COMMIT)BEGIN;
SELECT stock FROM inventory
WHERE sku ='SKU-7701'FOR UPDATE; -- 👈 row-level lock acquired-- returns 300. Agent computes 300-200=100UPDATE inventory SET stock =100WHERE sku ='SKU-7701';
COMMIT; -- lock released. now B unblocks-- Agent B transaction (was blocked during A's transaction)BEGIN;
SELECT stock FROM inventory
WHERE sku ='SKU-7701'FOR UPDATE; -- returns 100 (A already committed)-- 100 < 200 → B raises InsufficientStock. Order rejected cleanly.ROLLBACK;
## Design patterns to prevent write-race failures## Pattern 1: Pessimistic locking (shown above) → SELECT FOR UPDATE before any read-modify-write → Best for: low contention, OLTP inventory, seat reservations → Downside: serializes writes, can bottleneck under high load## Pattern 2: Optimistic locking with version check
db.execute("""
UPDATE inventory
SET stock = stock - :qty, version = version + 1
WHERE sku = :sku AND version = :expected_version
""", qty=qty, sku=sku, expected_version=read_version)
if affected_rows ==0:
raiseConflictError("someone else modified this row — retry")
## Pattern 3: Atomic DB operation (no read in application code)
db.execute("""
UPDATE inventory
SET stock = stock - :qty -- DB handles the math atomically
WHERE sku = :sku AND stock >= :qty -- DB enforces the constraint
""")
→ Cleanest solution. Let the DB handle it. Zero chance of stale read.## The agent architecture rule: Parallel agents sharing mutable state = distributed transactions. If you skip the transaction, you ARE still in a race. You just lost.