After a deploy, logs show hundreds of 'deadlock detected' errors per hour on checkout. Walk through how you'd investigate.

Summary

Read the deadlock reports to find the two statements and tables involved, map them to the two code paths, and compare their lock order. Usually a new code path locks the same rows in a different order (or holds locks longer); fix the ordering and keep a retry for the remainder.

Longer Answer

1. Get the report: Postgres logs deadlock detected with Process X waits for ShareLock on transaction Y; blocked by process Z and each blocked query; MySQL shows LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS (enable innodb_print_all_deadlocks).
2. Remember the queries shown are the ones that were blocked; the conflicting locks were taken earlier in the same transactions.
3. Correlate with the deploy: which new code touches those tables? A loop over cart lines in customer order, a new FK insert, a longer transaction with an HTTP call inside.
4. Fix: lock rows in a deterministic order (sort ids), shorten transactions, add missing indexes (fewer rows locked), move external calls out.
5. Keep a retry for deadlock errors (40P01 / 1213) — but only once the transaction body is side-effect free.

Key Points

• Read the database's deadlock report
• Find both code paths and compare lock order
• Deterministic lock order + short transactions
• Retry as a safety net, not the fix

Deep dive: Reading Deadlock Reports