MySQL Deadlock Case Study: How a Gap Lock Triggers a Deadlock

Environment

MySQL version 8.0.27, isolation level REPEATABLE‑READ (default for InnoDB).

Create sample data

create table passjava_test_lock2 (a int, b int, c int, primary key(a), key idx_b(b));
insert into passjava_test_lock2 values (10,10,10),(15,15,15),(20,20,20);
select * from passjava_test_lock2;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 10  | 10  | 10  |
| 15  | 15  | 15  |
| 20  | 20  | 20  |
+-----+-----+-----+

Deadlock demonstration

Session 1

SET GLOBAL innodb_print_all_deadlocks = ON;
BEGIN;
update passjava_test_lock2 set c = 106 where b = 10;
insert into passjava_test_lock2 values (12,12,12);

Session 2

SET GLOBAL innodb_print_all_deadlocks = ON;
BEGIN;
update passjava_test_lock2 set c = 206 where b = 15;
insert into passjava_test_lock2 values (14,14,14);
commit;

Timeline (≈5 seconds) shows Session 2 reporting a deadlock error:

Deadlock found when trying to get lock; try restarting transaction

Lock situation analysis

Step 3 – Session 1 executes update

Result: Affected rows: 1, Time: 0s Locks held after the update:

TABLE – lock mode IX (intent‑write)

idx_b – record lock X on 10,10; this is a Next‑Key lock covering the range (‑∞,10] PRIMARY – record lock X,REC_NOT_GAP on a=10 idx_b – record lock X,GAP on 15,15; this is a pure GAP lock covering the interval (10,15) In REPEATABLE‑READ, InnoDB locks the matching record, the preceding gap, and additionally the next distinct value ( b=15) to prevent phantom reads. The extra gap appears as lock_data = 15,15 with mode X,GAP.

Step 4 – Session 2 executes update

Result: Affected rows: 1, Time: 0s Locks held after the second update:

TABLE – IX idx_b – record lock X on 15,15; Next‑Key lock on (10,15] PRIMARY – record lock X,REC_NOT_GAP on a=15 idx_b – record lock X,GAP on 20,20; pure GAP lock on

(15,20)

Step 5 – Session 1 executes insert

The statement waits because it needs an insert‑intention lock on the gap (10,15), which is already held by Session 2’s Next‑Key lock.

Waiting lock details:

idx_b – lock mode X,GAP,INSERT_INTENTION (waiting) on 15,15; the transaction wants to insert into (10,15) but is blocked.

Session 1 is therefore in LOCK WAIT for X,GAP,INSERT_INTENTION.

Step 6 – Session 2 executes insert

The insert fails with the same deadlock error:

Deadlock found when trying to get lock; try restarting transaction

Step 7 – Session 1 finally succeeds

After Session 2 rolls back, Session 1 acquires the needed lock and completes:

Affected rows: 1, Time: 5s

Deadlock log analysis

Excerpt from /usr/local/mysql/data/mysqld.local.err confirms the lock states:

TRANSACTION 2766242, ACTIVE 15 sec inserting
... lock_mode X,GAP before rec insert intention waiting ...
TRANSACTION 2766243, ACTIVE 11 sec inserting
... lock_mode X,GAP before rec insert intention waiting ...

Key observations from the log:

Session 1 holds a GAP lock on (10,15) (mode X,GAP on record 15,15).

Session 2 holds a Next‑Key lock on (10,15] (covers the same gap plus the record 15).

Both sessions attempt to acquire an insert‑intention lock on the same gap; the insert‑intention lock must wait for all GAP locks to be released, creating a circular wait.

Conclusion

The deadlock is caused by two transactions updating different rows on a non‑unique secondary index. Each UPDATE acquires a GAP lock that overlaps the other transaction’s range, and both subsequently try to INSERT into the same gap, resulting in a circular wait.

Mitigation observations

Use a unique index : a unique index on column b would cause only a record lock, not a Next‑Key lock.

Adjust isolation level : switching to READ COMMITTED changes GAP‑lock behavior.

Keep lock order consistent : when multiple rows are locked, acquire them in the same order across transactions.