Why MySQL Deadlocks Occur: A Deep Dive into Locks, Gaps, and Next‑Key Mechanisms

Background

During a Double‑Eleven promotion, a MySQL deadlock caused a spike in active connections, exhausting the application‑layer connection pool and making all requests fail. The simplified service code is:

@Transaction
public void service(Integer id) {
    delete(id);
    insert(id);
}

The incident prompted a detailed analysis of MySQL lock types to help developers quickly diagnose similar issues.

MySQL Lock Mechanisms

1. Shared (S) and Exclusive (X) Locks

Shared locks allow concurrent reads, while exclusive locks block other transactions from reading or writing the same row. The lock compatibility matrix is:

S locks are compatible with other S locks.

X locks are mutually exclusive.

S and X locks are mutually exclusive.

When two sessions try to update the same row, the first obtains an X lock; the second waits, as shown in the lock log:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap

2. Gap Locks

Gap locks prevent phantom reads by locking the interval between index records. They do not lock existing rows, so they cannot prevent an insert that would create a new row in the gap.

In the illustrated scenario, Session A holds a gap lock on the interval (20,30]; Session B’s attempt to insert a row with id 25 must wait for the gap lock to be released.

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test`
trx id 38849 lock_mode X locks gap before rec

3. Next‑Key Locks

Next‑key locks combine an X lock on a row with a gap lock on the preceding interval, protecting both the row and the gap against phantom inserts. In the example, the lock on id 20 is a next‑key lock.

4. Insert Intention Locks

Before inserting a row, InnoDB checks the next record for gap locks. If a gap lock exists, the inserter creates an insert‑intention lock and waits.

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test`
trx id 38850 lock_mode X locks gap before rec insert intention waiting

Online Incident Analysis

Using the lock knowledge, the article revisits the original service method. Two cases are considered: the id exists or does not exist. The focus is on the non‑existent‑id case, where a delete is issued on a missing row followed by an insert.

When id 15 is absent, Session 1 acquires a gap lock on the interval after id 20. Sessions 2 and 3 simultaneously acquire gap locks on the same interval, leading to multiple insert‑intention locks waiting on each other and forming a deadlock.

MySQL’s deadlock detector selects one transaction (e.g., Session 2) to roll back, but the remaining sessions still wait on each other’s gap locks, prolonging the blockage.

Performance Impact at Scale

Under higher concurrency (e.g., 300 threads), the lock‑waiting queue grows, and MySQL’s deadlock detection algorithm, O(n²), repeatedly scans the queue, consuming CPU and increasing latency. The result is a surge in active connections, connection‑pool exhaustion, and cascading request failures.

Conclusion

The root cause is high‑concurrency delete‑then‑insert (or update‑then‑insert) patterns on the same key, which generate gap and next‑key locks that easily deadlock. Mitigation strategies include adding idempotent checks, avoiding rapid delete‑insert cycles, and being aware that under REPEATABLE‑READ isolation, updates/deletes automatically acquire next‑key locks.

Understanding MySQL’s lock hierarchy—shared, exclusive, gap, next‑key, and insert‑intention—helps engineers design safer concurrent workloads and prevent connection‑pool saturation.