Analysis of MySQL Lock Mechanisms and Deadlock Scenarios

During the Double‑11 promotion, an online incident occurred where a MySQL deadlock caused a surge in active connections, exhausting the application‑level connection pool.

The root cause was investigated by analyzing MySQL's lock mechanisms.

MySQL Lock Types

Shared (S) lock : Allows concurrent reads on a row; rarely used directly.

Exclusive (X) lock : Required for UPDATE or DELETE on a row; blocks other transactions from acquiring X locks on the same row.

S locks are non‑conflicting; X locks are mutually exclusive; S and X locks conflict with each other.

When multiple transactions attempt to update the same index record, the first transaction acquires an X lock, while subsequent ones wait.

Lock log example for an exclusive lock:

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

Gap Locks

Gap locks prevent phantom reads by locking the interval between rows. They do not lock the rows themselves, allowing multiple transactions to hold gap locks on the same interval, which can lead to deadlocks.

Lock log for a gap lock:

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

Next‑Key Locks

Next‑key locks combine an exclusive lock on a row with a gap lock on the preceding interval, providing repeatable‑read protection for both the row and the gap.

Insert‑Intention Locks

When inserting a new row, MySQL first acquires a gap lock on the surrounding interval; if a gap lock exists, an insert‑intention lock is created and the transaction waits.

Example of an insert‑intention lock log:

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

Deadlock Scenario

A three‑session experiment demonstrated how concurrent DELETE‑then‑INSERT operations on the same key can generate a cycle of gap locks and insert‑intention locks, leading to a deadlock. MySQL's deadlock detector selects one transaction to roll back, but the remaining sessions may still be blocked by other gap locks, causing prolonged lock waiting.

When the request volume spikes (e.g., 300 concurrent threads), the O(n²) deadlock detection algorithm becomes a performance bottleneck, dramatically increasing CPU usage and active connections, ultimately exhausting the connection pool.

Conclusion

The primary cause of the outage was high‑concurrency DELETE‑then‑INSERT (or UPDATE‑then‑INSERT) on the same row, which creates extensive gap‑lock contention and deadlocks. To mitigate similar issues, developers should implement idempotent checks, avoid rapid successive modifications of the same row, and be aware that in REPEATABLE READ isolation, UPDATE/DELETE automatically acquire next‑key locks.

For further reading, see the recommended articles on MTTR reduction, large‑scale pagination optimization, LangChain, and mobile component architecture.