MySQL Deadlock Case Study and Analysis

Deadlocks are a challenging technical problem that many DBAs and developers encounter. This article presents a series of case analyses to help readers understand and resolve MySQL deadlocks.

1. Environment Description

MySQL 5.6.24 with transaction isolation level set to REPEATABLE READ.

create table tx (
  id int not null primary key auto_increment,
  c1 int not null default 0,
  c2 int not null default 0,
  key idx_c1(c1)
) engine=innodb;

insert into tx values
  (24,3,4),
  (25,3,4),
  (26,3,4),
  (30,5,8);

2. Test Cases

An illustration image (omitted) shows the test scenario.

3. Deadlock Log

----------------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-03-27 15:40:40 0x7f75cafce700
*** (1) TRANSACTION:
TRANSACTION 1850, ACTIVE 20 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 379040, OS thread handle 140143994337024, query id 1521958 localhost root updating
update tx set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1850 lock_mode X locks rec but not gap
*** (2) TRANSACTION:
TRANSACTION 1849, ACTIVE 32 sec updating or deleting, thread declared inside InnoDB 4999
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 379016, OS thread handle 140143893473024, query id 1521976 localhost root updating
delete from tx where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 5 n bits 72 index idx_c1 of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (1)

4. Analysis of the Deadlock Log

The key points are:

Locks on the same column must be queued.

The non‑unique index idx_c1 causes transactions to acquire locks in different orders, leading to a circular wait.

T1: Session 2 executes SELECT FOR UPDATE and holds the primary‑key row lock for id=30 . T2: Session 1 runs UPDATE using the non‑unique index idx_c1 (c1=5) and then tries to lock the primary‑key row id=30 , which is already held by Session 2. T3: Session 2 attempts to DELETE the row id=30 , needing both the primary‑key lock and the idx_c1 lock that Session 1 holds.

This creates a circular wait: Session 2 (DELETE) waits for Session 1 (UPDATE), which waits for Session 2 (SELECT FOR UPDATE), resulting in a deadlock.

The root cause of deadlocks in RDBMS systems is differing lock acquisition orders across transactions.

5. Solution

Modify Session 1’s UPDATE to use the primary key directly, e.g.: update tx set c2 = x where id = 30; This enforces a consistent lock order (primary‑key first) and avoids cross‑index locking that leads to deadlocks.

6. Conclusion

The deadlock occurred because different sessions competed for the non‑unique index idx_c1 and the primary key, causing a circular wait. In high‑concurrency environments, avoid updating the same row via different indexes to prevent deadlocks.

Further reading links are provided for additional deadlock cases and analysis.