MySQL Lock Types and Deadlock Causes with Practical Examples

MySQL Lock Types

MySQL provides three lock granularity levels: table‑level, row‑level, and page‑level.

Table‑level lock : low overhead, fast to acquire, never deadlocks, but coarse granularity leads to high lock‑conflict probability and low concurrency.

Row‑level lock : high overhead, slower to acquire, can deadlock, finest granularity gives the lowest conflict probability and highest concurrency.

Page‑level lock : overhead and acquisition time lie between table and row locks; can deadlock; granularity is between table and row, giving moderate concurrency.

Lock Algorithms

Next‑Key lock: locks the record and the gap before it.

Gap lock: locks only the gap, not the record.

Record lock: locks the record only, not the gap.

Thus, Next‑Key lock = Gap lock + Record lock.

Deadlock Causes and Examples

1. Causes

A deadlock occurs when two or more sessions wait for each other’s resources, typically because they acquire locks in different orders. Table‑level locks never cause deadlocks; the focus is on InnoDB row‑level locks.

2. Example Cases

Case 1

Two users invest simultaneously and the application selects borrowers with SELECT ... FOR UPDATE in different orders, leading to 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

Case 2

Typical upsert pattern (select‑then‑insert) can deadlock when two sessions lock different rows and then try to insert conflicting primary keys. Using MySQL’s INSERT ... ON DUPLICATE KEY UPDATE avoids the problem because the statement acquires only row‑level locks.

INSERT INTO t3(xx,xx) ON DUPLICATE KEY UPDATE `xx`='XX';

Case 3

Two sessions lock overlapping ranges with SELECT ... FOR UPDATE and then one session inserts a new row inside the other’s locked range, causing a deadlock similar to Case 1.

Case 4 & 5

Illustrated with diagrams: two sessions each hold a lock on one row and then request the lock held by the other, creating a classic circular wait.

Case 6

Multiple concurrent DELETE statements on a table with a unique composite index (a,b,c) can deadlock under Repeatable Read isolation. The DELETE acquires a next‑key lock on the matching record; when several transactions target the same record, they wait on each other’s next‑key locks.

DELETE FROM dltask WHERE a=? AND b=? AND c=?;

InnoDB Lock‑Prevention Strategy

InnoDB distinguishes between page locks (S/X) and transaction locks (row/table). Page locks are short‑lived; transaction locks are long‑lived. To avoid deadlocks, a session holding a transaction lock may wait for a page lock, but a session holding a page lock cannot wait for a transaction lock. If a row lock cannot be obtained while holding a page lock, InnoDB releases the page lock, waits for the row lock, then reacquires the page lock and re‑checks the record.

The relevant source code is row0sel.c::row_search_for_mysql(), which implements this complex logic.

Analysis of Deadlock Roots

Key conditions that lead to the described deadlocks:

DELETE on a unique index with equality condition under Repeatable Read.

At least three concurrent delete transactions.

All transactions target the same existing row.

InnoDB storage engine with default innodb_locks_unsafe_for_binlog disabled.

Page‑level lock handling combined with next‑key lock acquisition.

Understanding these factors helps design safer SQL patterns and choose appropriate isolation levels.