How to Diagnose and Resolve MySQL InnoDB Deadlocks in Production

Introduction

MySQL deadlock exceptions frequently appear in complex online services when concurrent operations contend for the same resources. Because they only reproduce under specific data and timing conditions, diagnosing them requires understanding MySQL lock conflicts and analyzing both the InnoDB deadlock log and the binlog.

Standard Operating Procedure

Adopting a SOP for handling online anomalies improves individual efficiency and helps spread best practices across the team.

The typical investigation workflow for an occasional MySQL deadlock is:

Detect the deadlock from alerting or error‑log notifications.

Examine the stack trace in the error log.

Fetch the MySQL deadlock log.

Use the binlog to view the full transaction content.

Identify the two conflicting transactions, analyze the SQL statements involved, and modify the business code accordingly.

Obtaining the Deadlock Log

After a deadlock occurs, run the command: show engine innodb status This returns the most recent deadlock information. For continuous monitoring, enable the InnoDB standard monitor or lock monitor, which periodically writes InnoDB status to the MySQL error log (default every 15 seconds).

Additionally, set the system variable innodb_print_all_deadlocks to record every deadlock in the error log:

set GLOBAL innodb_print_all_deadlocks=ON;

Analyzing the Deadlock Log

The log lists the timestamp, the two transactions involved, the SQL statements each executed at the moment of deadlock, and the lock information (held and waiting).

Example excerpt (simplified):

TRANSACTION 2078, ACTIVE 74 sec starting index read // transaction 1 basic info

Key fields include:

tables in use and locked – number of tables accessed and locked.

LOCK WAIT – the transaction is waiting for a lock.

lock struct(s) – length of the lock chain.

2 row lock(s) – number of row locks held.

undo log entries – number of undo records, indicating rows modified.

The most critical lines show the exact lock type and the table involved. MySQL defines four row‑lock types:

Record lock (LOCK_REC_NOT_GAP): locks the record but not the gap.

Gap lock (LOCK_GAP): locks the gap before the record.

Next‑key lock (LOCK_ORNIDARY): locks the record and the gap.

Insert‑intention lock (LOCK_INSERT_INTENTION): locks the gap for an insert.

By matching the lock type with the transaction’s SQL, you can infer why the deadlock occurred.

Retrieving and Analyzing the Binlog

The binlog records every SQL statement executed by a transaction, allowing you to see the full sequence that led to the deadlock.

Use the MySQL utility Mysqlbinlog to fetch the remote binlog:

Mysqlbinlog -h127.0.0.1 -u root -p --read-from-remote-server binlog.000001 --base64-output=decode-rows -v

Options explained: --base64-output=decode-rows – decodes row‑based binlog events (the common mode for modern MySQL). -v – prints row events as pseudo‑SQL statements.

By correlating the timestamp and transaction identifiers from the deadlock log with entries in the binlog, you can locate the exact SQL statements executed by the winning transaction.

Once you have the full list of statements, you can perform a detailed lock‑conflict analysis and modify the application code to avoid the deadlock in future runs.