How to Detect, Diagnose, and Prevent MySQL InnoDB Deadlocks

Introduction

In a MySQL database, a deadlock occurs when multiple transactions compete for the same resource and each waits for the other to release it, causing the operations to stall and the engine to automatically roll back the transaction with the lowest cost.

Investigation

Method 1

show engine innodb status;

Running this command returns a large log. Search between the sections LATEST DETECTED DEADLOCK and TRANSACTIONS for the SQL statements that caused the deadlock. The log shows details of each transaction, the locks they hold, and the lock they are waiting for.

......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-23 23:48:30 0x1f00
*** (1) TRANSACTION:
TRANSACTION 805714, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
update test set name = 'C++语言' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805714 lock_mode X locks rec but not gap
...
*** (2) TRANSACTION:
TRANSACTION 805715, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap
...
WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 805727
Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
History list length 36
...

Method 2

show status like 'innodb_row_lock%';

The command returns a table with metrics such as Innodb_row_lock_current_waits (non‑zero indicates waiting transactions), Innodb_row_lock_time (total lock wait time in ms), Innodb_row_lock_time_avg , Innodb_row_lock_time_max , and Innodb_row_lock_waits .

Method 3

This query retrieves information about currently running InnoDB transactions, idle threads, thread process details, and the SQL statements they are executing.

SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

Resolution

MySQL automatically resolves deadlocks by rolling back the transaction with the lowest cost, so the solution is not to unlock manually but to prevent deadlocks from occurring.

Prevention

Reduce transaction granularity : Smaller transactions lock fewer resources and are less likely to deadlock.

Commit transactions promptly : Faster release of locks.

Design indexes wisely : Proper indexing reduces lock contention and improves query efficiency.

Maintain a consistent access order : When multiple tables are involved, lock them in the same order across all transactions.

Example

Preparation

Create a table and insert two rows.

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `temp`.`test` (`id`, `name`) VALUES (1, 'C');
INSERT INTO `temp`.`test` (`id`, `name`) VALUES (2, 'C++');

Deadlock

Open two terminals to simulate a live environment and execute the following steps line by line:

Step 1: start transaction; on both terminals.

Step 2 (Terminal A): select * from test where id = 1 for update; Step 3 (Terminal B): delete from test where id = 2; Step 4 (Terminal A): update test set name = 'C++语言' where id = 2; (blocks waiting for Terminal B).

Step 5 (Terminal B): delete from test where id = 1; (now both transactions wait on each other, MySQL detects a deadlock and aborts the victim).

Step 6: commit; on both terminals.

The FOR UPDATE statement acts as a pessimistic lock, preventing other transactions from modifying the selected rows. In step 5, each terminal waits for the other's lock, creating a circular wait, which MySQL resolves by rolling back the transaction with the lowest cost.

What Is the Minimum-Cost Rollback Strategy?

When a deadlock occurs, MySQL's InnoDB engine selects a victim transaction to roll back based on the smallest rollback cost, considering factors such as the number of operations to undo, the system resources consumed, and the amount of work already performed. The transaction with the lowest estimated cost is automatically rolled back.