Analyze MySQL Deadlocks with SHOW ENGINE INNODB STATUS

This article provides a practical guide on how to locate, read, and analyze deadlock information using the SHOW ENGINE INNODB STATUS command in MySQL. You will learn how to identify the conflicting transactions, understand the specific locks causing the blockages, and implement strategies to prevent these database conflicts in the future.

Step 1: Generate the Status Report

To analyze a deadlock, you must first retrieve the InnoDB engine status report. Run the following command in your MySQL client:

SHOW ENGINE INNODB STATUS;

This command outputs a large block of text containing performance metrics, memory usage, and transaction details.

Step 2: Locate the Deadlock Section

Scroll through the output to find the section labeled LATEST DETECTED DEADLOCK. If no deadlock has occurred since the MySQL server last started, this section will not appear.

This section displays detailed information about the two transactions involved in the most recent deadlock, showing what they were doing and why they collided.

Step 3: Analyze the Transactions

The deadlock output is divided into three main parts: TRANSACTION 1, TRANSACTION 2, and the WE ROLL BACK TRANSACTION statement at the end. For both transactions, examine the following key elements:

• Transaction ID and Active Seconds: Look for lines like TRANSACTION 24053, ACTIVE 5 sec. This tells you how long the transaction was active before the deadlock occurred.
• The SQL Query: Locate the active statement or the query text. This shows the exact SQL statement that triggered the lock request.
• Locks Held (HOLDS THE LOCKS): This subsection describes the locks the transaction successfully acquired before the conflict.
• Locks Waited For (WAITING FOR THIS LOCK TO BE GRANTED): This subsection describes the specific lock the transaction is waiting for.

Step 4: Interpret the Lock Types

To understand why the deadlock happened, you must look at the lock details. InnoDB will display information such as:

• RECORD LOCKS: Indicates a lock on specific index records.
• lock_mode X: An exclusive lock, usually acquired during UPDATE or DELETE statements, which prevents other transactions from reading or writing.
• lock_mode S: A shared lock, usually acquired during SELECT ... FOR SHARE statements, which allows others to read but not write.
• gap before rec: A gap lock, which locks the space between index records to prevent inserts.
• insert intention: A lock requested by an INSERT statement before inserting a row, which often conflicts with gap locks.

A deadlock occurs because Transaction 1 holds Lock A and wants Lock B, while Transaction 2 holds Lock B and wants Lock A.

Step 5: Identify the Victim

At the very bottom of the deadlock section, you will see a line similar to:

*** WE ROLL BACK TRANSACTION (2)

MySQL automatically detects deadlocks and rolls back the transaction that has performed the fewest modifications (the “victim”) to allow the other transaction to complete.

How to Prevent Future Deadlocks

Once you have identified the queries and indexes involved, use these strategies to resolve the issue:

1. Keep Transactions Short: Commit transactions as quickly as possible to reduce the time locks are held.
2. Consistent Access Order: Ensure that all applications access tables and rows in the exact same order (e.g., always update Table A before Table B).
3. Add Proper Indexes: Ensure your queries use indexes. If a query performs a full table scan, it will lock many more rows than necessary, drastically increasing the chance of a deadlock.
4. Use Lower Isolation Levels: If your application logic allows, consider changing the transaction isolation level from REPEATABLE READ to READ COMMITTED to eliminate gap locking.