Diagnosing and fixing a production deadlock

Prerequisites

• ▹Access to the database logs / `pg_stat_activity` / `SHOW ENGINE INNODB STATUS`.
• ▹The ability to deploy a code change to the transaction paths involved.

Procedure

1. 1

   Capture the two conflicting statements and their lock order

   A deadlock always involves at least two transactions locking the same resources in opposite orders. Get the database to tell you which. Postgres logs both statements on deadlock; MySQL's InnoDB status shows the latest one. You need to see what each side locked first.

   -- PostgreSQL: ensure deadlocks are logged with both statements.
   SET log_lock_waits = on;
   -- Then read the server log for 'deadlock detected' + the two queries.
   
   -- MySQL / MariaDB: the LATEST DETECTED DEADLOCK section names both.
   SHOW ENGINE INNODB STATUS;

2. 2

   Map the opposing lock order

   Write down, for each transaction, which rows/tables it locks and in what sequence. The deadlock is the point where one path takes lock A then B while the other takes B then A — a sale locking stock-then-unit against a void locking unit-then-stock, for example. Naming the two orders is the whole diagnosis.

3. 3

   Impose one global lock order through a single helper

   Every path that touches the shared rows must acquire their locks in the same order. Route them all through one helper so the order can't drift, and enforce it in review. A consistent order removes the cycle entirely — there is nothing left to deadlock on.

   public function withOrderedLocks(int $stockId, int $unitId, Closure $fn): mixed
   {
       return DB::transaction(function () use ($stockId, $unitId, $fn) {
           $stock = StockLevel::whereKey($stockId)->lockForUpdate()->firstOrFail(); // A
           $unit  = SerialUnit::whereKey($unitId)->lockForUpdate()->firstOrFail();  // B
           return $fn($stock, $unit);
       }, attempts: 3); // deadlock victim is retried, not surfaced as a 500
   }

4. 4

   Make the work idempotent and retry the victim

   A deadlock is a transient, expected condition — the database picked a victim to break the tie. Retry the transaction rather than returning an error, but only after making it idempotent (guard on current state, not blind increments) so a retry can't double-apply.

5. 5

   Reconcile the drift the deadlock already caused

   Silent rollbacks may have already desynced your data. Run an invariant check to find and correct it, and schedule it to run continuously so future drift surfaces the same day rather than at audit.

   SELECT s.product_id, s.on_hand, COUNT(u.id) AS in_stock_units
   FROM   stock_levels s
   LEFT JOIN serial_units u
          ON u.product_id = s.product_id AND u.status = 'in_stock'
   GROUP BY s.product_id, s.on_hand
   HAVING s.on_hand <> COUNT(u.id);