MySQL Deadlock Diagnosis: Uncovering the Real Culprit via SHOW ENGINE INNODB STATUS

At 02:17 am an alert indicated a surge of errors in the order service, all reporting the same message:

Deadlock found when trying to get lock; try restarting transaction

. The author’s first instinct was to blame a deadlock.

Running SHOW ENGINE INNODB STATUS and locating the LATEST DETECTED DEADLOCK section reveals the exact lock state of the two transactions involved. A simplified log shows that Transaction 1 holds a lock on idx_user_id and waits for idx_status, while Transaction 2 holds a lock on idx_status and waits for idx_user_id – a classic circular wait.

The log also contains a line lock_mode X locks gap before rec, indicating a gap (or next‑key) lock. In REPEATABLE READ isolation, InnoDB locks not only the matched rows but also the gaps between them to prevent phantom reads. For example, UPDATE order_record SET status = 1 WHERE status = 0 locks the rows with status = 0 and the gaps (‑∞,0] and (0,1), blocking inserts or updates of new rows with status = 0.

Because the two transactions use different indexes ( idx_user_id vs idx_status) they acquire locks in opposite order, creating the cross‑waiting deadlock. InnoDB’s deadlock detector then chooses the transaction with the lower rollback cost as the victim, so only one transaction reports an error.

The author outlines a three‑step troubleshooting process:

Identify the lock type. Examine the lock_mode line: X locks rec but not gap (row lock) vs X locks gap before rec (gap lock) vs a combination indicating a next‑key lock.

Draw the lock‑order diagram. Write down each transaction’s lock path, e.g.

Transaction A: idx_user_id → needs idx_status → waits for idx_status
Transaction B: idx_status → needs idx_user_id → waits for idx_user_id

The directed cycle confirms a deadlock.

Find the root cause of inconsistent lock order. In the example, Transaction A accesses rows by order_no (using idx_user_id) while Transaction B updates by status (using idx_status), causing the opposite lock acquisition sequence.

Three practical solutions are presented:

Unify the index access path (preferred). Rewrite Transaction B to first fetch primary keys and then update by primary key, ensuring both transactions lock rows in the same order.

-- before
UPDATE order_record SET user_id = 10086 WHERE status = 0;

-- after
SELECT id FROM order_record WHERE status = 0;
UPDATE order_record SET user_id = 10086 WHERE id IN (1,2,3);

Shorten the transaction. Split large transactions into smaller ones so each holds locks for a shorter window, reducing deadlock probability.

Lower the isolation level (use with caution). Switching from REPEATABLE READ to READ COMMITTED removes gap locks but allows phantom reads; suitable only for non‑critical tables such as logs.

The author notes a pitfall: adding a composite index (

ALTER TABLE order_record ADD INDEX idx_status_user_id (status, user_id)

) temporarily eliminated the original deadlock but introduced a new one because the index changed the lock order without aligning the access paths of the two transactions.

Key take‑aways compiled as a checklist:

Always start with SHOW ENGINE INNODB STATUS – never guess.

Pay attention to lock_mode to distinguish row locks from gap locks.

Draw the lock‑order graph; a cycle means a deadlock.

Ensure different transactions that touch the same data use the same index path.

Keep transactions as short as possible.

After changing indexes, run regression tests – an index is not a cure‑all.

Enable online deadlock monitoring to catch issues early.

In summary, the core of MySQL deadlock troubleshooting is not merely changing application code but guaranteeing that all concurrent transactions acquire locks in a consistent order.