Why Do MySQL RR Transactions Deadlock? A Deep Dive into Index Locking

Introduction

Deadlocks are a fascinating yet challenging technical problem that many DBAs and developers encounter. This series of case analyses aims to help readers understand why deadlocks occur in MySQL and how to diagnose them.

Case 1: Non‑unique index

Environment

MySQL 5.6 with transaction isolation level REPEATABLE READ (RR).

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

Test case

Insert several rows into ty:

INSERT INTO ty(a,b) VALUES (2,3),(5,4),(6,7);

Deadlock log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION: 462308399, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct, heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
DELETE FROM ty WHERE a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
*** (2) TRANSACTION: 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB5000
mysql tables in use 1, locked 1
5 lock struct, heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
INSERT INTO ty(a,b) VALUES (5,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Analysis

1. In RR mode, locking the same column requires a queue. The non‑unique index idxa creates gap locks around the target values. Session 1 (sess1) first executes DELETE FROM ty WHERE a=5, acquiring an X lock on the row and two gap locks [1,2]-[2,5] and [2,5]-[3,6].

2. Session 2 (sess2) attempts to lock a=5 for its INSERT. Because sess1 still holds the X lock, sess2 waits for the lock to be released.

3. The INSERT statement also needs a gap lock (insert intention lock) that falls between the existing gap locks, so it must wait for sess1’s delete to finish. This creates a circular wait: sess1’s delete waits for sess2’s delete, and sess2’s insert waits for sess1’s delete, resulting in a deadlock.

Question posed: If sess1 runs INSERT INTO ty(a,b) VALUES (5,10) instead, would sess2 still encounter a deadlock?

Case 2: Unique index

Environment

MySQL 5.6 with REPEATABLE READ isolation, but the index on column a is defined as UNIQUE.

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Test case

INSERT INTO t2(a,b) VALUES (2,3),(5,4),(6,7);

Deadlock log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-10 00:03:31 7f78ea936700
*** (1) TRANSACTION: 462308445, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct, heap size 360, 1 row lock(s)
MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
DELETE FROM t2 WHERE a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
*** (2) TRANSACTION: 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB5000
mysql tables in use 1, locked 1
4 lock struct, heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
INSERT INTO t2(a,b) VALUES (5,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
*** WE ROLL BACK TRANSACTION (1)

Analysis

When the index is UNIQUE, the delete acquires an X lock on the exact row ( lock_mode X locks rec but not gap) and no gap locks. The subsequent insert must first perform a duplicate‑key check, which acquires a shared (S) lock on the index entry. Because sess1’s delete still holds the X lock, sess2’s insert waits for the S lock, and sess2’s delete waits for sess1’s delete, again forming a circular wait.

The key difference from Case 1 is that a UNIQUE index eliminates gap locks, and the insert uses an Insert‑Intention Lock (S) instead of a gap lock.

Conclusion

The article studies deadlock scenarios under RR isolation for both ordinary and unique indexes. To mitigate such deadlocks, it recommends using READ‑COMMITTED isolation together with ROW‑BASE BINLOG, but acknowledges that deadlocks caused by unique‑key conflicts in RC/RR are often unavoidable. Designing tables with fewer UNIQUE indexes can reduce the risk.