Concurrency & IsolationdebuggingAdvanced

Checkout throws "deadlock detected" under load

Symptoms

  • During peak traffic, some checkouts fail with SQLSTATE 40P01 deadlock detected.
  • The same code works fine under light load.
-- deadlock report (postgres log):
Process A waits for ShareLock on transaction ... blocked by process B.
Process B waits for ShareLock on transaction ... blocked by process A.
  A: UPDATE inventory SET qty = qty - 1 WHERE product_id = 5;  then WHERE product_id = 9;
  B: UPDATE inventory SET qty = qty - 1 WHERE product_id = 9;  then WHERE product_id = 5;

Investigate

Inspect areas in any order (0/4 inspected). When you think you know the root cause, commit to it.

The deadlock report
Order of updates in the checkout code
Transaction duration
Isolation level