They Both Needed Each Other’s Lock
A deadlock is not a random failure.
Two transactions each hold a lock that the other one needs. Neither can proceed. MySQL resolves it by choosing one as the victim and rolling it back. The application receives an error. Most applications retry silently.
Nobody investigated. The pattern repeats.
This script surfaces what happened, who was involved, and what they were fighting over.
Loading…
Reading the Results
Query 1 — SHOW ENGINE INNODB STATUS
The LATEST DETECTED DEADLOCK section shows the two transactions involved, which locks each held and which they were waiting for, which transaction was rolled back (the victim), and the actual SQL statements from each side.
This only shows the most recent deadlock. It is overwritten every time a new one occurs.
Query 2 — Deadlock Count
A low number is expected on any busy server. A rapidly climbing number means a recurring pattern that needs investigation, not just retry logic.
Query 5 — InnoDB Transactions
| Column | What It Tells You |
|---|---|
trx_state | RUNNING or LOCK WAIT |
wait_seconds | How long this transaction has been blocked |
trx_rows_locked | How many row locks this transaction holds |
trx_rows_modified | How many rows it has changed (not yet committed) |
trx_query | The SQL currently executing |
Long-running transactions with many rows locked are deadlock generators. Find them early.
What to Watch For
| Signal | What It Means |
|---|---|
Innodb_deadlocks climbing rapidly | A recurring access pattern is causing repeated deadlocks |
| Same table appearing in every deadlock | Index design or transaction ordering issue on that table |
Long trx_rows_locked counts | A transaction is holding too many locks for too long |
LOCK WAIT state without a matching deadlock | A slower blockage — not a deadlock yet, but could become one |
| Application retrying errors silently | Deadlocks may be happening but nobody knows — check error logs |
Reducing Deadlocks
Deadlocks are usually fixable at the application level:
- Access tables in the same order across all transactions
- Keep transactions short — commit quickly, hold locks for as little time as possible
- Use appropriate indexes — full table scans take gap locks on far more rows than needed
- Retry on deadlock (error 1213) — but treat it as a signal, not a solution
Gareth Winterman