MySQL InnoDB Deadlock Diagnosis: Detection, Fixes, and Escalation

When a client application receives ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, MySQL has detected a circular wait between two or more InnoDB transactions and rolled back one to break the cycle.

\n\n

Recognizable condition and likely causes

\n

Condition	Typical cause
ERROR 1213 returned immediately after a DML statement	Two transactions locked the same rows or index ranges in opposite order, creating a circular wait.
Frequent deadlocks under high concurrent writes	Gap-locking under REPEATABLE READ isolation, or missing indexes that cause InnoDB to lock broader row sets.
Long lock waits that do not resolve to a deadlock	Transactions holding locks while awaiting application logic, slow I/O, or external locks.

\n\n

Ordered diagnostic checks

\n

\n
1. Confirm the error code - verify the client receives error 1213; run SHOW WARNINGS in the MySQL session if needed.
\n
2. Capture the most recent InnoDB engine status - in the mysql client run

   SHOW ENGINE INNODB STATUS;

   This requires the PROCESS privilege (MySQL 5.6+; older versions may require SUPER). Locate the LATEST DETECTED DEADLOCK block.
\n
3. Map live lock waiters using information_schema - if the deadlock has not yet been recorded, run:

   SELECT r.trx_id AS waiting_trx_id, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.request_trx_id = r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

   Requires PROCESS privilege. The result shows the waiting transaction's SQL and the blocking transaction's SQL.
\n
4. Review application transaction order and isolation level - examine the code paths that produced the deadlock. Note whether the same tables are accessed in different orders across sessions, and whether the default REPEATABLE READ isolation level is in effect.
\n

\n\n

Fixes tied to diagnostic findings

\n

Enforce a consistent access order

\n

If the deadlock output shows Transaction A updating rows in order (id=1, then id=2) while Transaction B updates (id=2, then id=1), modify the application so that all code paths lock the affected rows or tables in the same sequence. For example, always issue the UPDATE accounts statement before the UPDATE ledger statement. This eliminates the circular wait that InnoDB cannot resolve automatically.

\n\n

Narrow lock scope with appropriate indexes

\n

When the deadlock output references gap locks or a lock on a non-unique index, adding a targeted index reduces the lock range. For instance, if a statement such as UPDATE accounts SET balance = balance - 10 WHERE customer_id = 42 lacks an index on customer_id, InnoDB may scan the clustered index and lock the primary-key records of matching rows, which can appear as a wide lock if many rows satisfy the condition. Executing ALTER TABLE accounts ADD INDEX idx_customer_id (customer_id) limits the lock to the single matching row.

\n\n

Evaluate a different isolation level after testing

\n

If the application does not require strict phantom-read protection, switching the session to READ COMMITTED disables many gap-locking behaviors. Run once per connection: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED. Validate this change in a staging environment that mirrors the production write pattern, as the lower isolation level affects read consistency.

\n\n

Implement idempotent deadlock retry logic

\n

In the application code, catch error 1213 and retry the entire transaction a limited number of times (e.g., three attempts). Ensure the transaction is idempotent - meaning re-executing it produces the same outcome without unintended side effects, such as double-charging or duplicate row inserts.

\n\n

Escalation criteria

\n

\n
• Deadlocks persist after applying consistent row-access order and adding targeted indexes.
\n
• The frequency of ERROR 1213 rises with load or approaches the innodb_lock_wait_timeout threshold.
\n
• Lock waits observed via information_schema.innodb_lock_waits remain elevated for extended periods under normal workload.
\n
• In these cases, engage a DBA to review the schema, consider partitioning, or refactor the problematic queries.
\n

\n\n

Practical verification after a fix

\n

\n
1. Set up a reproducible test: open two MySQL client sessions. In the first session, begin a transaction and issue UPDATE accounts SET balance = balance - 10 WHERE id = 101. In the second session, begin a separate transaction and issue UPDATE accounts SET balance = balance + 10 WHERE id = 102 (or reverse the row order). Confirm that one session receives ERROR 1213.
\n
2. Apply the intended fix (e.g., reorder statements, add the missing index, or adjust the isolation level).
\n
3. Repeat the concurrent test; both transactions should complete, with the second simply waiting for the first to commit.
\n
4. Monitor SHOW ENGINE INNODB STATUS under load for several minutes. Verify that the LATEST DETECTED DEADLOCK section does not update, indicating the deadlock pattern is resolved.
\n