阅读中文版本

Detect MySQL Lock Wait and Deadlock Patterns

Read InnoDB lock-wait and deadlock diagnostics to find the transaction that always grabs locks in the opposite order.

SHOW ENGINE INNODB STATUS\G

SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

What this recipe does

Analyse InnoDB deadlock output to identify the opposite lock ordering causing recurring deadlocks.

Bulk updates intermittently fail with deadlock errors, and reruns succeed, suggesting lock ordering rather than correctness bugs.

Prerequisites

• MySQL 8 with innodb_print_all_deadlocks enabled
• Access to the error log
• The transaction logic under test

Procedure

1. Show the latest deadlock from InnoDB status.

SHOW ENGINE INNODB STATUS\G

Expected result: The LATEST DETECTED DEADLOCK section shows both transactions, their locks, and the victim chosen.

2. Fix by acquiring locks in a consistent order.

SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Expected result: Ordering rows by id before locking prevents circular waits between concurrent transactions.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows after consistent lock ordering, the deadlock rate drops to zero under the same concurrent workload.

Troubleshooting

Deadlocks vanish from status quickly — Enable innodb_print_all_deadlocks to log every event for later analysis.

Still deadlocking after ordering — Check for gap locks from range scans; narrow the WHERE clause or lower isolation where safe.

Limitations

• Deadlock output is verbose and version-specific.
• Consistent ordering reduces but cannot eliminate all deadlocks.
• Does not evaluate application retry logic.