Most teams treat deadlocks like weather. An ER_LOCK_DEADLOCK error shows up in the logs, someone says "InnoDB does that sometimes", a retry gets added, and everyone moves on until the same pair of transactions collides again at ten times the rate during a traffic spike. I used to do this too. What changed my mind is realizing that every deadlock is a fully recorded event: InnoDB writes down who held what, who wanted what, and in which order. The only missing piece is a workflow that turns that record into a fix.
This is the postmortem workflow I now run for every recurring deadlock on MySQL 8.0 and 8.4. It has five steps: capture everything, parse the report, extract the lock order, fix the application's retry behavior, and then remove the collision itself.
First, understand what InnoDB already did
When InnoDB detects a cycle in the wait-for graph, it does not hang; it picks a victim and rolls it back immediately, returning error 1213 to that session. The victim is chosen roughly by weight: the transaction that has inserted, updated, or deleted the fewest rows loses, because it is cheapest to roll back. This matters for postmortems because the transaction that errored is not necessarily the transaction that was misbehaving. The surviving transaction is half of every deadlock, and it never appears in your application error logs. Only the InnoDB report shows both sides.
Deadlock detection is on by default via innodb_deadlock_detect. Some very high-concurrency setups disable it and rely on innodb_lock_wait_timeout instead, trading instant detection for less contention on the lock system. Unless you have measured that specific problem, leave detection on; a timeout-based "deadlock" takes 50 seconds to resolve by default and produces no diagnostic report at all.
Step 1: capture every deadlock, not just the last one
By default, SHOW ENGINE INNODB STATUS shows only the most recent deadlock. If you get five different deadlocks a day, you can only ever see the latest, and the one hurting your peak traffic may never be on top when you look. Fix that first:
SET GLOBAL innodb_print_all_deadlocks = ON;
-- Verify, and check how often deadlocks actually happen
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name = 'lock_deadlocks';With innodb_print_all_deadlocks enabled, every deadlock report is written to the error log, which gives you a durable archive you can grep and diff. The lock_deadlocks counter in innodb_metrics gives you the rate. Track it over time: a stable background rate of a few per hour is a known cost, while a step change after a deploy is a regression you can attribute to a specific change. That counter belongs on the same dashboard as your other MySQL monitoring signals.
Step 2: parse the report methodically
The LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS has a rigid structure, and I read it in the same order every time. For each transaction, note four things: the query text shown, the table and index in the lock descriptions, the lock it HOLDS, and the lock it is WAITING FOR. The report labels these explicitly with "HOLDS THE LOCK(S)" and "WAITING FOR THIS LOCK TO BE GRANTED", and each lock entry names the index, the table, and the lock mode, such as lock_mode X locks rec but not gap, or lock_mode X locks gap before rec.
Two traps catch people here. First, the query shown for each transaction is only the statement running at the moment of the deadlock. The locks it holds may come from earlier statements in the same transaction that are no longer visible, so a single UPDATE that "cannot possibly deadlock with itself" often turns out to be step three of a multi-statement transaction. Second, the lock modes matter enormously. If you see gap or insert intention locks in the report, the collision involves range locking under REPEATABLE READ rather than two sessions touching the same rows, and the fix lives at the isolation and indexing level; I wrote up how that class differs from PostgreSQL's serialization failures in deadlock diagnosis in MySQL vs PostgreSQL.
Step 3: extract the lock order
The goal of parsing is one artifact: the interleaving. Write it down as a timeline. Transaction 1 locked row A in index PRIMARY of orders, then requested row B. Transaction 2 locked row B, then requested row A. Nearly every deadlock reduces to this shape once you name the rows, and the reduction tells you the fix: make both code paths touch A and B in the same order.
A classic example is two transfers between the same pair of accounts in opposite directions:
-- Session 1 -- Session 2
START TRANSACTION; START TRANSACTION;
UPDATE accounts SET balance = balance - 10 UPDATE accounts SET balance = balance - 25
WHERE id = 1; WHERE id = 2;
-- now locks id 1 -- now locks id 2
UPDATE accounts SET balance = balance + 10 UPDATE accounts SET balance = balance + 25
WHERE id = 2; WHERE id = 1;
-- waits for session 2 -- deadlock: cycle detectedThe ordering fix is mechanical: always update the lower account id first, regardless of transfer direction. The same principle generalizes to batch jobs, which should process rows in a consistent key order, and to multi-table transactions, which should touch tables in one agreed sequence across the codebase.
Step 4: make retries correct before making them rare
Even after fixes, deadlocks remain possible in any system with concurrent writers, so the application must handle error 1213 correctly. Correct means three things. Retry the entire transaction, not the failed statement, because InnoDB rolled the whole transaction back and any values your code computed from earlier reads inside it are stale. Bound the retries, three attempts with small jittered backoff is plenty, because unbounded retry against a persistent collision creates a retry storm. And log every occurrence with the transaction's identity, because silent retries are how a deadlock rate quietly grows for months. If you see error 1205, lock wait timeout, treat it differently: the transaction is usually still alive and only the statement failed, though this depends on innodb_rollback_on_timeout.
Step 5: remove the collision
With the lock order in hand, the durable fixes are ranked by how often they work for me. Consistent ordering fixes the majority outright. Shrinking transactions comes next: fewer statements between START TRANSACTION and COMMIT means fewer held locks and a smaller collision window, and moving external calls out of transactions is the single biggest win. Indexing the write predicates matters because an UPDATE that scans a range locks every examined row, so a missing index turns a one-row change into a hundred-row lock footprint. Finally, reducing gap locking, whether through unique-key equality predicates or READ COMMITTED where the workload tolerates it, removes the deadlocks whose reports are full of gap and insert intention locks.
Verification closes the loop. After shipping a fix, watch the lock_deadlocks counter across the affected window and grep the archived error-log reports for the same table and index pair. A fix that merely made the collision rarer will resurface at the next traffic peak, and the archive lets you prove whether the interleaving you removed is really gone or has simply moved to a different index.
Deadlock history and where MonPG fits
The workflow above has one operational dependency: somebody has to notice the deadlocks, keep the reports, and watch the rate. That is monitoring work, and it is exactly the kind of evidence trail that vanishes when the error log rotates. In the interest of honesty: MonPG is a PostgreSQL monitoring platform today and does not monitor MySQL yet. MySQL support is being built now, with deadlock-rate tracking and report capture in scope, and the MySQL monitoring (coming soon) page is where that lands first. If your team also operates PostgreSQL, the same postmortem discipline works there and MonPG supports it today, so you can start there and bring the workflow to MySQL when support ships.