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