Understanding MySQL InnoDB Deadlocks: Types, Causes, and Prevention Strategies

MySQL deadlocks are a frequent interview topic, and this article consolidates common deadlock problems and solutions for developers.

MySQL Lock Types and Analysis

MySQL provides three lock levels: page‑level, table‑level, and row‑level. Table‑level locks have low overhead and cannot cause deadlocks, but they lock large data ranges, reducing concurrency. Row‑level locks have higher overhead, can cause deadlocks, and offer the highest concurrency. Page‑level locks fall between the two.

Lock Algorithms

Next‑Key Locks: lock the record and the preceding gap.

Gap Locks: lock only the gap before a record.

Record Locks: lock the record without the gap.

Deadlock Causes and Examples

The core cause of deadlocks is inconsistent lock acquisition order across sessions. The article presents several cases:

Case 1

Two users invest money, splitting it into random portions and updating borrower balances with SELECT ... FOR UPDATE. Different lock orders on borrower IDs lead to deadlock.

SELECT * FROM xxx WHERE id IN (xx,xx,xx) FOR UPDATE;

Case 2

Insert‑or‑update logic on a unique key can cause deadlock when two sessions try to insert different rows that share the same unique index.

INSERT INTO t3(id, name, ...) VALUES (22, 'ac', ...) ON DUPLICATE KEY UPDATE name='XX';

Case 3‑6

Various scenarios involving deletes, gaps, and next‑key locks are demonstrated with detailed session logs and SQL statements, showing how InnoDB handles deleted records, gap locks, and the interaction between page locks and row locks.

InnoDB Locking Strategies

When a query matches a deleted record, InnoDB applies a next‑key lock (record + gap). If no matching record exists, a gap lock is placed to prevent new qualifying rows. For existing valid records, a plain row lock (no‑gap) is used.

Deadlock Prevention

InnoDB distinguishes between short‑lived page locks and long‑lived transaction locks. To avoid deadlocks, a session holding a transaction lock may wait for a page lock, but a session holding a page lock must release it before waiting for a transaction lock. This strategy reduces lock ordering conflicts.

Analysis of Deadlock Scenarios

By understanding the three lock types, the next‑key lock behavior, and InnoDB’s deadlock‑prevention algorithm (implemented in row0sel.c::row_search_for_mysql()), developers can predict and mitigate deadlocks in concurrent workloads.

References

https://blog.csdn.net/mine_song/article/details/71106410

http://hedengcheng.com/?p=844

http://www.cnblogs.com/sessionbest/articles/8689082.html