Skip to main content
IncidentPaged 11:30 UTC, Monday (Black Friday traffic ramp)

Postgres deadlock on every 100th order

Service: order-service (Flask + Postgres)

Symptom: ~1% of POST /orders return 500. Postgres logs show 'deadlock detected'. Rate scales linearly with traffic; never happens in staging.

The code that's running in prod

# order_service.py
# Two endpoints touch the same two tables. place_order updates
# inventory then balance; issue_refund updates balance then inventory.
# Both run inside a transaction.

def place_order(tx, order):
    """Decrement stock, then decrement the customer's balance."""
    tx.execute(
        "UPDATE inventory SET qty = qty - %s WHERE sku = %s",
        (order.qty, order.sku),
    )
    tx.execute(
        "UPDATE balances SET amount = amount - %s WHERE user_id = %s",
        (order.total, order.user_id),
    )


def issue_refund(tx, refund):
    """Restore the balance first, then restore stock."""
    tx.execute(
        "UPDATE balances SET amount = amount + %s WHERE user_id = %s",
        (refund.total, refund.user_id),
    )
    tx.execute(
        "UPDATE inventory SET qty = qty + %s WHERE sku = %s",
        (refund.qty, refund.sku),
    )

Log dashboard (last 60 seconds)

11:30:14INFO[api]POST /orders user=u_4711 sku=SKU-A qty=2 amount=$80
11:30:14INFO[api]POST /refunds user=u_4711 sku=SKU-A qty=1 amount=$40
11:30:14INFO[db]Tx#A1 UPDATE inventory WHERE sku=SKU-A (acquired RowExclusiveLock)
11:30:14INFO[db]Tx#B7 UPDATE balances WHERE user_id=u_4711 (acquired RowExclusiveLock)
11:30:14INFO[db]Tx#A1 UPDATE balances WHERE user_id=u_4711 — waiting for Tx#B7
11:30:14INFO[db]Tx#B7 UPDATE inventory WHERE sku=SKU-A — waiting for Tx#A1
11:30:14ERROR[db]deadlock detected — aborting Tx#B7 (refund) — error 40P01
11:30:14ERROR[api]POST /refunds → 500 (psycopg.errors.DeadlockDetected)
11:30:14INFO[api]POST /orders → 200 (Tx#A1 committed)
11:31:02WARN[ops]Deadlock count last 60s: 7. Rate climbing with traffic.

Pick the root cause

Four candidates. Three are plausible but mistake symptom for cause. Pick what you'd write in the postmortem timeline.