🔒 Diagnosing MySQL Deadlocks With AI

A Deadlock found when trying to get lock; try restarting transaction error shows up in the app logs, the app retries, and most of the time nobody investigates because the retry papered over it. That works until deadlocks become frequent enough to cause latency spikes and failed user actions, and then you’re staring at the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS trying to reconstruct what two transactions were doing to each other. That block is one of the most cryptic things MySQL produces, and reading it correctly is a real skill.

It’s also the perfect thing to hand to AI. The deadlock report is structured, self-contained, and full of lock-mode jargon — exactly what a model decodes well. I use it to translate the report into “transaction A held X and wanted Y while transaction B held Y and wanted X,” then I find the lock-ordering fix and verify it. The model reads; I reason about the fix and confirm it doesn’t recur.

Get the report

InnoDB only keeps the most recent deadlock in the status output, so for ongoing issues, turn on persistent logging:

[mysqld]
innodb_print_all_deadlocks = ON

That writes every deadlock to the error log. To grab the latest one on demand:

SHOW ENGINE INNODB STATUS\G

Scroll to LATEST DETECTED DEADLOCK. You’ll see two transactions, the SQL each was running, and the locks each held and waited for. It’s terse: RECORD LOCKS space id ... index ... lock_mode X and so on. This is where most engineers’ eyes glaze over.

Let AI translate the lock graph

I paste the entire deadlock block plus the SHOW CREATE TABLE for the involved tables. The prompt:

Here is a LATEST DETECTED DEADLOCK block from InnoDB and the schemas of the tables involved. Translate it into plain English: which transaction held which lock, what each was waiting for, and what the lock-acquisition order was for each. Then identify the inconsistent lock ordering that caused the cycle. Reference the exact index and lock mode from the output for each claim.

A good response:

Transaction 1 was running UPDATE inventory SET qty = qty - 1 WHERE sku = 'A' and held an exclusive (X) record lock on the sku='A' row in PRIMARY. It then tried to lock sku='B'. Transaction 2 was running the same update in the opposite order — it held X on sku='B' and tried to lock sku='A'. That’s a classic lock-ordering cycle: both transactions update the same two rows but in opposite order, so each holds what the other needs. The fix is to make both code paths lock rows in a consistent order, e.g. always update SKUs sorted ascending.

That translation alone is worth the trouble. The actual root cause — opposite lock ordering — is the most common deadlock pattern, and AI spots it reliably from the report.

Reproduce it before you “fix” it

I don’t trust the diagnosis until I can reproduce the deadlock in a controlled way. Two terminals against a test instance, with autocommit off:

-- Session 1
START TRANSACTION;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A';
-- now, in Session 2:
START TRANSACTION;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'B';
-- back in Session 1:
UPDATE inventory SET qty = qty - 1 WHERE sku = 'B';   -- blocks
-- back in Session 2:
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A';   -- deadlock!

If this reliably triggers the same deadlock, the AI’s reading was correct. If it doesn’t, the real cause is something else — maybe gap locks under REPEATABLE READ, or a secondary-index lock I overlooked — and I go back to the report. Reproducing it is how I avoid shipping a “fix” for the wrong cause.

Apply the right fix, not just a retry

Once the cause is confirmed, the fixes in order of preference:

• Consistent lock ordering. Sort the keys before touching them so every transaction acquires locks in the same sequence. This eliminates the cycle outright.
• Shorter transactions. A transaction that holds locks while making an HTTP call is begging for deadlocks. Move side effects out of the critical section.
• Lower isolation where safe. Gap-lock deadlocks under REPEATABLE READ sometimes vanish under READ COMMITTED. I make AI spell out the consistency implications before I consider this, because it changes phantom-read behavior.
• Smart retry as a backstop, not a fix. Retries are fine for genuinely rare contention, but if you’re retrying constantly you’re masking a design problem.

I ask the model to weigh these against my actual code path, and then I implement the lock-ordering fix and re-run the reproduction. No deadlock means it’s solved. The MySQL guides go deeper on isolation-level trade-offs.

What AI gets wrong about locks

The model can misread gap locks and next-key locks, which lock ranges rather than single rows and behave differently under REPEATABLE READ. If the deadlock involves lock_mode X locks gap before rec, double-check the explanation against the InnoDB locking documentation for your version, because models sometimes describe gap locks as if they were record locks. It can also assume your isolation level; if you’ve changed it from the default, say so in the prompt.

I keep a deadlock-decoding prompt saved with the grounding instructions baked in — there’s a starter at /prompts/. The workflow that consistently resolves these: enable innodb_print_all_deadlocks, capture the report, let AI translate the lock graph into plain English, reproduce the deadlock on a test instance to confirm, then apply a real ordering fix and prove it doesn’t recur. AI turns the most cryptic output InnoDB produces into something readable in seconds. The reproduction step is what makes sure you’re fixing the actual deadlock and not a hallucinated one.