Diagnosing and Resolving InnoDB Deadlocks in MySQL

The Deadlock Condition

A MySQL deadlock occurs when two or more transactions mutually hold locks that the others require, creating a cycle where neither can proceed. MySQL detects this state and automatically kills one of the transactions (the "victim") to break the cycle, returning ERROR 1213 (40001): Deadlock found when trying to get lock.

While application-level retries can mask the symptom, frequent deadlocks indicate structural inefficiencies in transaction design or indexing. Left unaddressed, they lead to increased latency, spikes in aborted transactions, and reduced throughput during write-heavy workloads.

Cause Mapping and Diagnostics

Deadlocks are rarely random; they are typically the result of specific locking behaviors tied to the InnoDB storage engine and the chosen isolation level.

Symptom/Pattern	Likely Cause	Locking Mechanism
Deadlocks on range updates or deletes	Gap Lock / Next-Key Lock contention	REPEATABLE READ isolation
Deadlocks on specific rows across different tables	Inconsistent update ordering	Row-level X-locks
Deadlocks during inserts into tables with Foreign Keys	Parent table validation locks	Shared (S) locks on parent rows
Wide-scale deadlocks during simple updates	Missing indexes causing full table scans	Locking more rows than necessary

Ordered Diagnostic Steps

Follow these steps in order to isolate the root cause. These commands assume you are using MySQL 8.0+ with performance_schema enabled.

1. Extract the Deadlock Report

The first step is to identify the exact queries and locks involved. Run this command in the MySQL shell as a user with PROCESS privileges:

SHOW ENGINE INNODB STATUS;

Locate the LATEST DETECTED DEADLOCK section. This report identifies the two transactions, the specific index pages they were accessing, and the type of lock (e.g., X for exclusive, S for shared) they were requesting versus holding.

2. Analyze Active Lock Waits

If deadlocks are occurring frequently, you can inspect the current lock state before the deadlock detector kills a transaction. Run these queries to see who is blocking whom:

SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

Check for long-running transactions that hold locks for extended periods, as these increase the probability of a collision.

3. Verify Index Usage

If the deadlock report shows locks on a primary key but the query uses a non-indexed column in the WHERE clause, InnoDB may be performing a scan and locking every row it encounters. Run an EXPLAIN on the offending queries:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND user_id = 123;

If the type is ALL or index (full index scan), the lock scope is too wide.

Remediation Strategies

Apply the fix that corresponds to your diagnostic finding.

Finding: Inconsistent Update Order

Scenario: Transaction A updates Table 1 then Table 2. Transaction B updates Table 2 then Table 1.

• Fix: Enforce a strict global ordering of updates in the application code. All transactions must access tables (and rows within tables) in the same sequence.

Finding: Gap Lock Contention

Scenario: Deadlocks occur during INSERT or UPDATE operations on ranges, common in the default REPEATABLE READ isolation level.

• Fix: If your application logic allows, change the transaction isolation level to READ COMMITTED. This disables gap locking for most operations, significantly reducing deadlock frequency.
• Command: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Finding: Wide Lock Scope (Missing Indexes)

Scenario: EXPLAIN reveals a full table scan during an update.

• Fix: Create a targeted index on the columns used in the WHERE clause. This allows InnoDB to use a row-level lock instead of locking the entire table or large index gaps.

Finding: Long-Running Transactions

Scenario: Deadlock reports show transactions holding locks for seconds or minutes.

• Fix: Break large batch updates into smaller chunks (e.g., update 1,000 rows at a time and commit) to release locks faster.

Verification and Monitoring

After applying a fix, monitor the following to ensure the resolution is effective:

• Status Variable: Monitor Innodb_deadlocks. The rate of increase should drop significantly over a comparable load window.
• Lock Wait Time: Query performance_schema.events_waits_summary_global_by_event_name to verify that average wait times for wait/synch/sxlock/innodb/hash_table_locks have decreased.

Escalation Criteria

If the following conditions persist after schema and ordering fixes, escalate to a Database Administrator or Storage Engineer:

• Deadlocks persist despite strict update ordering and optimized indexing.
• Deadlocks involve system tables or replication-related metadata.
• Deadlock spikes correlate exactly with storage I/O stalls or high fsync latency.
• The deadlock rate increases linearly with traffic despite the implementation of READ COMMITTED.