Analysis of MySQL Lock Mechanisms and Deadlock Scenarios During High‑Concurrency Events

Background: During a major sales promotion (Double‑11) the online system experienced a MySQL deadlock that caused the number of active connections to spike, filling the application‑layer connection pool and causing request failures.

MySQL lock mechanisms: MySQL uses row‑level locks to serialize concurrent writes. A shared (S) lock allows concurrent reads, while an exclusive (X) lock is required for updates or deletes. Gap locks protect the space between index records to prevent phantom reads, and next‑key locks combine X locks with gap locks. Insert‑intention locks are a special form of gap lock created by INSERT statements before the actual row is added.

Code examples used in the analysis:

@Transaction

public void service(Integer id) {

delete(id);

insert(id);

}

Table definition used for testing:

create table `test` (

`id` int(11) NOT NULL,

`num` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `num` (`num`)

) ENGINE=InnoDB;

insert into test values (10,10),(20,20),(30,30),(40,40),(50,50);

Lock‑log excerpts illustrate how MySQL records lock information, e.g., lock_mode X locks rec but not gap for exclusive row locks and lock_mode X locks gap before rec for gap locks.

Deadlock analysis: The article walks through a timeline (T1‑T5) where multiple sessions acquire gap locks and insert‑intention locks on the same index range, creating a circular wait. MySQL’s deadlock detector rolls back one transaction, but the remaining sessions still wait on each other's gap locks, leading to prolonged blocking. Under high concurrency the O(n²) deadlock detection cost and the accumulation of waiting transactions dramatically increase CPU usage and active connections, eventually exhausting the connection pool.

Conclusion: The primary cause of the outage was high‑concurrency delete‑then‑insert (or update‑then‑insert) operations on the same rows, which generate gap and next‑key locks that easily form deadlocks. To avoid similar problems, developers should add idempotent checks, minimize rapid successive modifications of the same row, and be aware that gap locks reduce overall concurrency, especially under repeatable‑read isolation.