MySQL Lock Types and Deadlock Causes with Practical Examples

MySQL uses three lock granularity levels: table‑level, row‑level, and page‑level, each with distinct performance and concurrency characteristics. Table locks are cheap and deadlock‑free but coarse; row locks are fine‑grained but can cause deadlocks; page locks fall in between.

The locking algorithm includes next‑key locks (which lock a record and the preceding gap), gap locks (which lock only the gap), and record locks (which lock only the record). Consequently, a next‑key lock equals a gap lock plus a record lock.

Deadlocks arise when two or more sessions acquire locks in different orders, causing a circular wait. InnoDB’s default Repeatable Read isolation level and the presence of gap locks in range queries make deadlocks common.

Example 1

Two investment sessions allocate random funds to borrowers using SELECT ... FOR UPDATE on different borrower IDs, leading to lock order reversal and a deadlock. The fix is to lock all target rows in a single statement, e.g.:

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

Example 2

A typical upsert pattern (select‑then‑insert) can deadlock when two sessions try to insert different primary‑key values concurrently. Using MySQL’s INSERT ... ON DUPLICATE KEY UPDATE avoids the problem because the statement always acquires only row‑level locks.

Example 3

A range SELECT ... FOR UPDATE followed by an insert into the same range can cause a deadlock when the insert needs a lock that the range query already holds.

Example 4 & 5

Illustrations show two sessions each holding a lock on different rows and then requesting the other’s row, creating a classic deadlock cycle.

Example 6

Deleting rows from a table with a unique composite index (a,b,c) under Repeatable Read can produce next‑key locks on deleted records, leading to deadlocks when multiple concurrent deletes target overlapping keys.

InnoDB’s deadlock‑prevention strategy distinguishes three lock‑acquisition cases: (1) existing valid rows get X‑locks without gaps, (2) deleted rows get next‑key locks, and (3) non‑existent rows get gap locks. Additionally, InnoDB allows a transaction holding a row lock to wait for a page lock, but not the reverse, reducing lock‑order inversions.

The article concludes that understanding these lock behaviours and ordering constraints enables developers to design queries that avoid deadlocks, such as locking rows in a deterministic order or using atomic upsert statements.