MySQL Deadlock Case Study: Auxiliary Index Update vs Primary‑Key Delete

Background

Deadlocks are a common and challenging problem for DBAs and developers. The case presented involves a deadlock triggered by an update that uses a secondary index and a delete that targets the primary key.

Case Analysis

Business Logic

The application selects rows SELECT ... FOR UPDATE, processes business logic, and then deletes the row. Other concurrent logic updates rows, which can lead to a deadlock.

Environment

Database: MySQL 8.0.30

Transaction isolation level: REPEATABLE‑READ

create table dl(
  id int auto_increment primary key,
  c1 int not null,
  c2 int not null,
  key idx_c1(c1)
);

insert into dl(c1,c2) values (3,1),(3,2),(3,2),(3,3),(4,4),(5,5);

Test Case

An illustration (image) shows the steps that reproduce the deadlock.

Deadlock Log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-03 16:43:59 140261132850944
*** (1) TRANSACTION:
TRANSACTION 1416764, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 15, OS thread handle 140261086668544, query id 283 localhost msandbox updating
update dl set c2=10 where c1=5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416764 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416764 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 1416759, ACTIVE 23 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140261085611776, query id 286 localhost msandbox updating
delete from dl where id=6

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

Deadlock Analysis

Session 1 starts a transaction, executes SELECT ... FOR UPDATE on the row with id=6 , and holds an X record lock on that primary‑key row.

Session 2 later runs an UPDATE dl SET ... WHERE c1=5 . The update first acquires a lock on the secondary index idx_c1 , succeeds, and then attempts to lock the primary‑key row id=6 . Because Session 1 already holds the X lock on that primary key, Session 2 must wait, as shown in the log line: RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table test.dl trx id 1416764 lock_mode X locks rec but not gap waiting

Session 1 proceeds to DELETE FROM dl WHERE id=6 . Deleting a row also requires locking the secondary index entry idx_c1 . That lock is already held by Session 2, creating a circular wait and causing the deadlock. Session 1 receives an error and rolls back.

How to Solve

The deadlock originates from Session 2 updating via the auxiliary index. The recommended fix is to rewrite the update to use the primary key directly, eliminating the need to lock idx_c1 and thus preventing the circular wait.

Summary

Key point: deadlocks occur when different transactions lock the same rows in different orders, leading to mutual waiting. Analyzing lock order helps resolve most deadlock scenarios.

Recall the basic MySQL locking principles:

Principle 1: The basic lock unit is a next‑key lock.

Principle 2: Only objects accessed during the search are locked.

Optimization 1: For equality queries on a unique index, the next‑key lock degrades to a row lock.

Optimization 2: For equality queries on a non‑unique index that scan past the last matching value, the next‑key lock degrades to a gap lock.

Under READ‑COMMITTED isolation, row locks acquired during statement execution are released as soon as the statement finishes, without waiting for transaction commit.