How Two Code Paths Locking the Same Two Tables in Opposite Order Took Down Checkout

10:47 UTC. The checkout error rate alert fires, then fires again ninety seconds later at a higher threshold. Support's Slack channel starts filling with "payment went through, order says failed" messages faster than anyone can triage them. Nothing shipped in the last hour. The last deploy was a marketing banner change, eighteen hours earlier.

the setup

Checkout on our platform runs two updates inside one transaction: decrement inventory.quantity for the purchased SKU, then write the new orders row with the loyalty points earned on that purchase. Both statements sit inside a single BEGIN/COMMIT block so a partial failure can't leave stock decremented with no order to show for it.

A separate nightly reconciliation job, added eight months earlier by a different engineer to patch loyalty point rounding errors, does the same two updates for a batch of orders: it walks a list of recently corrected orders, updates each orders row's point total, then re-derives and writes the matching inventory adjustment for any SKU where a refund had changed stock. Same two tables. Same transaction. Reverse order.

That morning, a promo email went out three hours ahead of a flash sale still queued for the afternoon. Some subset of recipients checked out immediately instead of waiting, which pushed checkout concurrency to roughly four times its normal weekday floor, right as the reconciliation job's extended morning run (it had a backlog from a holiday) was still working through orders from the day before.

the scramble

First theory in the incident channel: the connection pool. Checkout traffic was up sharply, so the obvious guess was PgBouncer running out of connections and checkout requests queueing behind each other. SHOW POOLS showed headroom, nowhere near the configured max, and the errors weren't connection timeouts. They were coming back from the application's own commit call, with a Postgres error attached.

Second theory: the new loyalty-points index added two weeks earlier was slow under this kind of concurrent write load and checkout requests were timing out waiting on a lock from a long-running query. pg_stat_activity didn't back that up either, nothing had been running long enough to explain requests failing within milliseconds of being submitted.

Someone finally pulled the actual error text out of the application logs instead of guessing from the symptom.

psycopg2.errors.DeadlockDetected: deadlock detected
DETAIL:  Process 41822 waits for ShareLock on transaction 88213411; blocked by process 41905.
Process 41905 waits for ShareLock on transaction 88213398; blocked by process 41822.
HINT:  See server log for query details.

Not a slow query. Not a pool problem. Postgres itself had detected a deadlock between two transactions and killed one of them to break the cycle, which is exactly what it's supposed to do, at a rate high enough that checkout's error rate was now visible from the outside.

the hunt

Postgres logs the two competing statements whenever it kills a transaction to resolve a deadlock, provided log_lock_waits is on, which it was. Pulling the matching server-log entries for the two process IDs in the error above gave the actual query pair.

Process 41822: UPDATE inventory SET quantity = quantity - 1 WHERE sku_id = 'SKU-40218'
Process 41822: UPDATE orders SET loyalty_points = 340 WHERE id = 902117
Process 41905: UPDATE orders SET loyalty_points = 155 WHERE id = 901884
Process 41905: UPDATE inventory SET quantity = quantity + 1 WHERE sku_id = 'SKU-40218'

Process 41822 was checkout: lock inventory row first, then orders row. Process 41905 was the reconciliation job, applying a refund adjustment for the same SKU: lock orders first, then inventory. Two transactions, same two rows, opposite acquisition order. When their timing overlapped closely enough, each held the lock the other one needed next, and Postgres had no option but to abort one of them.

Under normal traffic this pattern existed but almost never triggered, the reconciliation job ran a few hundred updates a night against a mostly idle checkout path. The promo-driven concurrency spike, combined with the reconciliation job still catching up on a backlog well past its usual overnight window, put enough concurrent transactions through both code paths at once that collisions on hot SKUs became routine instead of rare.

Querying pg_stat_database confirmed the shape of it once we knew what to look for.

SELECT deadlocks FROM pg_stat_database WHERE datname = 'prod';
-- deadlocks: 214 (baseline for a normal day: under 5)

the find

Root cause: checkout and the reconciliation job updated inventory and orders in opposite order inside their own transactions. Neither path was wrong on its own, both did necessary work. The bug was that nothing enforced a consistent lock order between the two code paths, so given enough concurrent overlap, Postgres's deadlock detector was going to fire eventually. A traffic spike plus a delayed batch job supplied that overlap.

Every deadlock Postgres kills becomes a failed transaction at the application layer. Checkout had no retry logic for a serialization failure, it treated any database exception as a hard checkout failure and returned an error to the customer, even though the charge had frequently already gone through via the separate payment provider call earlier in the request. That's what produced the "charged but order failed" reports flooding support.

the fix

The immediate fix was pausing the reconciliation job's run and letting the backlog clear at a slower, throttled rate outside checkout's peak window, which stopped new deadlocks within a few minutes while we worked the real fix.

The real fix was forcing both code paths to acquire locks on inventory and orders in the same order, always inventory first. That alone removes the deadlock condition, two transactions can still block each other briefly, but they can no longer form a cycle.

def apply_adjustment(conn, order_id, sku_id, point_delta, qty_delta):
    with conn, conn.cursor() as cur:
        cur.execute(
            "UPDATE orders SET loyalty_points = loyalty_points + %s WHERE id = %s",
            (point_delta, order_id),
        )
        cur.execute(
            "UPDATE inventory SET quantity = quantity + %s WHERE sku_id = %s",
            (qty_delta, sku_id),
        )

def apply_adjustment(conn, order_id, sku_id, point_delta, qty_delta):
    # locking order matches checkout: inventory before orders, always
    with conn, conn.cursor() as cur:
        cur.execute(
            "UPDATE inventory SET quantity = quantity + %s WHERE sku_id = %s",
            (qty_delta, sku_id),
        )
        cur.execute(
            "UPDATE orders SET loyalty_points = loyalty_points + %s WHERE id = %s",
            (point_delta, order_id),
        )

As a backstop for the deadlock that's still theoretically possible under a different code path we haven't audited yet, checkout got retry logic specifically for serialization failures, since a deadlock loser in Postgres is always safe to retry from scratch.

from psycopg2.errors import DeadlockDetected

def run_checkout_transaction(conn, fn, *args, max_attempts=3):
    for attempt in range(1, max_attempts + 1):
        try:
            with conn:
                return fn(conn, *args)
        except DeadlockDetected:
            if attempt == max_attempts:
                raise
            logger.warning(f"checkout deadlock, retrying (attempt {attempt})")
    

We also added an alert on pg_stat_database.deadlocks crossing a rate of 10 per minute, sustained for two minutes. It didn't exist before because deadlocks had never been frequent enough to be worth a dedicated alert, they were something you noticed in a monthly metrics review, not something that took down checkout.

the aftermath

312 customers were charged for an order that then reported as failed, which meant 312 manual refund-or-fulfil decisions for support to work through over the following two days, a slower and more expensive cleanup than the eleven-minute outage itself.

Both code paths now go through a shared lock_order helper, and a lint rule flags any transaction that touches inventory and orders without acquiring them in the documented order. Convention alone wasn't enough to catch this the first time. The deadlock alert hasn't fired since the fix shipped.