MySQL InnoDB Deadlock Analysis Prompt
Decode the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS and prescribe a fix.
- Target user
- Engineers and DBAs debugging deadlocks and lock-wait timeouts
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL/MariaDB DBA who debugs InnoDB locking. You read the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS fluently — you understand record locks, gap locks, next-key locks, insert-intention locks, shared (S) vs exclusive (X) locks, lock waits, and how the chosen isolation level (REPEATABLE READ vs READ COMMITTED) changes gap locking. I will provide: - The `LATEST DETECTED DEADLOCK` block from `SHOW ENGINE INNODB STATUS`: [PASTE] - The statements each transaction was running (and the surrounding transaction, if known): [PASTE] - `SHOW CREATE TABLE` for the involved tables, including indexes: [PASTE SCHEMA] - The transaction isolation level and whether autocommit is on: [DESCRIBE] Work through this: 1. **Reconstruct both transactions.** For each, list which locks it HELD and which it was WAITING for, and on which index (note whether it is the clustered PK or a secondary index). Identify the exact lock type — record, gap, next-key, or insert-intention. 2. **Find the lock-ordering cycle.** State the circular wait in plain terms (T1 holds A waits B, T2 holds B waits A) so the root cause is unambiguous. 3. **Explain why these locks exist.** Tie the lock type back to the query and isolation level — for example, gap/next-key locks under REPEATABLE READ on a range scan, or locks taken via a secondary index that escalate to the PK. 4. **Prescribe fixes** in order of preference: consistent lock ordering across transactions, narrower/indexed predicates to lock fewer rows, shorter transactions, isolation level change (READ COMMITTED to drop gap locks where safe), and application-level retry on deadlock (error 1213). 5. **Note what to monitor** — innodb_deadlocks, lock waits via performance_schema.data_lock_waits, and Lock_time in the slow log. Output: (a) Reconstructed transactions and the cycle, (b) Root cause, (c) Ranked fixes with rationale, (d) Retry/monitoring guidance, (e) Risks. Guardrails: validate any query/index change on a replica or staging copy first and back up before schema changes; treat isolation-level changes as behaviour-changing and review their effect on application correctness; deadlock retry belongs in the app, not in disabling the deadlock detector; never push lock-changing DDL or config to prod without review.
Why this prompt works
The deadlock section of SHOW ENGINE INNODB STATUS is dense and intimidating, and most engineers stop at “MySQL killed transaction 2” without understanding the cycle. This prompt makes the model do the actual forensic work: for each transaction, separate the locks held from the locks waited on, identify whether the lock sits on the clustered PK or a secondary index, and name the exact lock type. That reconstruction is what turns an opaque dump into a clear circular wait.
It connects locking behaviour to its cause rather than treating deadlocks as random. Many real deadlocks come from gap and next-key locks under REPEATABLE READ, or from locks taken through a secondary index that then reach for the primary key. By tying each lock back to the query and isolation level, the prompt surfaces fixes that address the mechanism — consistent lock ordering, tighter indexed predicates, shorter transactions — instead of superstition.
The guardrails reflect hard-won operational wisdom. Deadlocks under contention are expected, so the right durable answer is application-side retry on error 1213, not disabling the detector. And because dropping to READ COMMITTED changes both query results and replication safety with statement-based binlog, the prompt flags it as a correctness decision a human must own, keeping the engineer in control of the trade-off.