Diagnosing and Resolving MySQL InnoDB Deadlocks: A Step-by-Step Guide

Recognizable Condition: Error 1213

When a client receives ERROR 1213 (HY000): Deadlock found when trying to get lock; try restarting transaction, InnoDB has detected a circular wait between two or more transactions and automatically rolled back one of them (the victim). The application sees a transaction failure, but the database remains consistent. The immediate takeaway: capture the deadlock details before they disappear from the status output.

Cause and Diagnostic Quick-Reference

Pattern	Typical Symptom in INNODB_STATUS	Primary Indicator
Different table access order	Two transactions hold X locks on Table A and Table B in opposite order	Lock mode X on different tables, wait-for graph shows cross dependency
Missing indexes causing full scans	Many RECORD locks on a table with no matching index for the WHERE clause	EXPLAIN shows type=ALL or large rows examined
Long-running transactions	Transaction holds locks while executing application logic or external API calls	Time in transaction state > seconds; lock hold time high
Gap/next-key lock contention	LOCK_MODE X,GAP or X,NEXT-KEY on secondary indexes under REPEATABLE READ	Isolation level REPEATABLE READ; unique-check or range scans

Ordered Diagnostic Checks

1. Capture INNODB_STATUS immediately after the error. Run:

   SHOW ENGINE INNODB STATUS\G

   Where: MySQL client connected to the same server. Permissions: PROCESS privilege (or SUPER on older versions). Note: This shows only the latest deadlock; enable innodb_print_all_deadlocks=ON (MySQL 5.7.15+/8.0+) to log every occurrence to the error log for pattern analysis.
2. Identify the two conflicting SQL statements. In the LATEST DETECTED DEADLOCK section, note each transaction's SQL line and the lock mode (S/X), lock type (RECORD/GAP/NEXT-KEY), and the index name (e.g., PRIMARY or secondary index).
3. Check execution plans. For each statement, run:

   EXPLAIN SELECT ... FROM ... WHERE ...

   Look for type=ALL (full table scan) or large rows estimates. Missing indexes cause InnoDB to lock every examined row.
4. Verify index coverage. Ensure WHERE, JOIN, and ORDER BY columns are covered by a composite index that matches the access pattern. A covering index can turn a full scan into an index range scan, drastically reducing locked rows.
5. Check transaction duration and isolation level. Run:

   SELECT @@transaction_isolation;

   Long transactions (seconds to minutes) increase deadlock probability. If the application performs HTTP calls, file I/O, or sleeps inside a transaction, move those outside.
6. Review application retry logic. The rolled-back transaction must be safely retryable. Blind retries on non-idempotent operations (e.g., sending an email, incrementing a counter) cause duplicate business effects. Use idempotency keys or a transactional outbox pattern.

Fixes Tied to Findings

A. Access Order Mismatch → Enforce Consistent Order

If Transaction 1 locks Table A then Table B, while Transaction 2 locks Table B then Table A, deadlocks are inevitable. Fix: define a global ordering (e.g., alphabetical by table name) and ensure every code path acquires locks in that order. This includes implicit locks from foreign key checks and triggers.

B. Missing Index → Add Covering Index

Example: UPDATE accounts SET balance = balance - 100 WHERE user_id = 5 AND status = 'active' scans the whole table because there's no index on (user_id, status). Adding CREATE INDEX idx_user_status ON accounts(user_id, status) reduces locked rows from millions to one. Risk: extra write overhead and disk space; test on staging with production-like data volume before deploying.

C. Long Transaction → Shorten or Change Isolation

Move external calls (payment gateway, email) outside the transaction. Split a large batch update into smaller chunks (e.g., 1000 rows per transaction). If contention persists, consider switching the session to READ COMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Requirement: binlog_format=ROW on all replicas (check with SHOW VARIABLES LIKE 'binlog_format'). Changing isolation level affects consistent-read semantics application-wide; verify that phantom reads are acceptable.

D. Gap Lock Contention → Redesign or Use READ COMMITTED

Under REPEATABLE READ, a SELECT ... FOR UPDATE on a non-unique secondary index acquires gap locks to prevent phantom inserts. If many sessions insert into the same index range, they block each other. Options:

• Use a unique index so InnoDB can lock only the record (no gap).
• Switch to READ COMMITTED (with ROW binlog) which eliminates gap locks for plain SELECTs.
• Redesign the unique-check pattern: e.g., attempt INSERT and catch duplicate-key error instead of SELECT ... FOR UPDATE.

Escalation Criteria

Escalate to schema or application redesign when:

• Deadlocks persist after applying index and ordering fixes.
• Deadlock frequency exceeds 1% of write throughput (monitor SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks').
• Application cannot safely retry (non-idempotent operations without idempotency keys).
• Changing to READ COMMITTED is required but replication topology uses statement-based binlog (cannot change).
• Suspected InnoDB bug — verify against fixed versions (5.7.36+, 8.0.27+).

Verification After Fix

1. Reproduce in staging: run two concurrent sessions with the exact SQL from INNODB_STATUS, confirm deadlock recurs, apply fix, then run a load test (sysbench or custom) for at least 30 minutes with concurrent writers.
2. Check INNODB_STATUS after sustained load: the LATEST DETECTED DEADLOCK timestamp should be old or the section absent.
3. Monitor metrics: Innodb_deadlocks counter should trend to zero; Innodb_row_lock_waits should decrease.
4. Validate replication compatibility: if isolation level changed, confirm binlog_format=ROW on all nodes.
5. Test retry behavior: inject a deadlock via a test harness (e.g., two threads forcing opposite lock order), verify the application retries once and succeeds without duplicate side effects.

Limitations

• SHOW ENGINE INNODB STATUS only retains the most recent deadlock; enable innodb_print_all_deadlocks for full history.
• Deadlock victim selection is based on internal weight (rows modified, locks held), not transaction age; the rolled-back transaction may not be the youngest.
• Adding indexes increases write latency and storage; always benchmark write throughput on realistic data.
• Changing isolation level affects all transactions in the session; test thoroughly for phantom-read anomalies.

Practical Check: Is the Deadlock Gone?

Run this query periodically to see the deadlock counter:

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

If the value stops increasing under normal load, the fixes are working. Combine with application-level logging of retry attempts to catch any residual issues.