MysqlHow-ToBeginner · 4 min read

How to Detect Deadlock in MySQL: Simple Steps and Examples

To detect a deadlock in MySQL, use the SHOW ENGINE INNODB STATUS command which provides detailed deadlock information. Additionally, check for error code 1213 in your application logs, which indicates a deadlock occurred during a transaction.

📐

Syntax

The main command to detect deadlocks in MySQL is SHOW ENGINE INNODB STATUS. This command shows the latest InnoDB engine status, including deadlock details.

SHOW ENGINE INNODB STATUS;: Displays InnoDB internal status.
Look for the LATEST DETECTED DEADLOCK section in the output.

mysql

SHOW ENGINE INNODB STATUS;

💻

Example

This example shows how to detect a deadlock by running SHOW ENGINE INNODB STATUS after a deadlock error occurs. The output includes the deadlock details.

mysql

/* Step 1: Run transactions that cause deadlock (example scenario) */
-- Transaction 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction 2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Now both transactions try to update the other's row causing deadlock
-- Transaction 1 tries:
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Transaction 2 tries:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

/* Step 2: Detect deadlock using: */
SHOW ENGINE INNODB STATUS;

Output

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-06-01 12:00:00 0x7f9c8b3f9700 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 140735194123264, query id 4567 localhost root updating UPDATE accounts SET balance = balance + 100 WHERE id = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12345 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ... *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 5 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 102, OS thread handle 140735194124000, query id 4568 localhost root updating UPDATE accounts SET balance = balance - 100 WHERE id = 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12346 lock_mode X locks rec but not gap Record lock, heap no 1 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ... *** WE ROLL BACK TRANSACTION (1)

⚠️

Common Pitfalls

Deadlocks often happen when transactions lock rows in different orders. Common mistakes include:

Not checking SHOW ENGINE INNODB STATUS after errors.
Ignoring error code 1213 which signals deadlock.
Holding locks too long or mixing large transactions.

Always handle deadlock errors in your application by retrying the transaction.

mysql

/* Wrong approach: Ignoring deadlock error */
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

/* Right approach: Catch error 1213 and retry transaction */
-- Pseudocode example
BEGIN TRANSACTION;
TRY {
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
} CATCH (error) {
  IF error.code == 1213 THEN
    ROLLBACK;
    -- Retry transaction
  END IF;
}

📊

Quick Reference

Use this quick reference to detect and handle deadlocks in MySQL:

Action	Command or Tip
Detect deadlock details	SHOW ENGINE INNODB STATUS;
Identify deadlock error code	Check for error code 1213 in logs or application
Handle deadlock in app	Catch error 1213 and retry transaction
Avoid deadlocks	Access tables in consistent order and keep transactions short

✅

Key Takeaways

Use SHOW ENGINE INNODB STATUS to see detailed deadlock information.

Deadlock error code 1213 indicates a deadlock occurred during a transaction.

Always handle deadlock errors in your application by retrying the transaction.

Avoid deadlocks by accessing tables in a consistent order and keeping transactions short.

Check the LATEST DETECTED DEADLOCK section in InnoDB status for root cause analysis.