MySQL Lock Types and Deadlock Analysis

1. MySQL Lock Types

MySQL provides three lock granularity levels: table‑level, row‑level, and page‑level (also called "page lock"). Table locks have low overhead and cannot cause deadlocks but have the highest contention. Row locks have higher overhead, can cause deadlocks, but offer the finest granularity. Page locks fall between the two in cost and contention.

Lock Mode Overview

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

Gap Lock : locks only the gap, not the record itself.

Record Lock : locks only the record, not the surrounding gap.

Thus, a Next‑Key Lock is effectively a combination of Gap Lock + Record Lock.

2. Causes of Deadlocks and Examples

1) Root Cause

A deadlock occurs when two or more sessions acquire locks in different orders, causing each to wait for the other. Table‑level locks never deadlock; the focus is on InnoDB row‑level locks.

2) Example Cases

Case 1

Two users invest simultaneously, each splitting the amount into two random parts and updating borrowers with SELECT ... FOR UPDATE. Because the order of borrower IDs differs, a deadlock occurs.

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

Solution: lock all target rows in a single statement, e.g.:

SELECT * FROM t3 WHERE id IN (8,9) FOR UPDATE

Case 2

Insert‑or‑update pattern where two sessions try to insert rows with different primary keys but overlapping unique indexes, leading to a deadlock:

INSERT INTO t3 VALUES (22,'ac','a',NOW());

INSERT INTO t3 VALUES (23,'bc','b',NOW());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Resolution: use INSERT ... ON DUPLICATE KEY UPDATE which acquires only row locks.

Case 3

Session1 inserts a row with id 7 while Session2 holds a lock on a range that includes id 9, causing a deadlock similar to Case 1.

INSERT INTO t3 VALUES (7,'ae','a',NOW());

Case 4 & 5

Illustrated with diagrams: two sessions each hold a lock on a different row and then request the other's row, creating a classic deadlock.

Case 6

Delete operation on a table with a unique composite index (a,b,c). Under Repeatable Read isolation, InnoDB may acquire a next‑key lock on a deleted record, leading to deadlock when multiple transactions delete the same key.

CREATE TABLE dltask (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto id',
  a VARCHAR(30) NOT NULL COMMENT 'uniq.a',
  b VARCHAR(30) NOT NULL COMMENT 'uniq.b',
  c VARCHAR(30) NOT NULL COMMENT 'uniq.c',
  x VARCHAR(30) NOT NULL COMMENT 'data',
  PRIMARY KEY (id),
  UNIQUE KEY uniq_a_b_c (a,b,c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='deadlock test';

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

Locking Strategies for DELETE

When a matching record is found and valid, InnoDB acquires an X lock (No‑Gap).

If the matching record is a deleted (ghost) row, InnoDB acquires a Next‑Key lock (X lock + gap).

If no matching record exists, InnoDB acquires a Gap lock on the first greater record.

InnoDB Deadlock Prevention

InnoDB distinguishes between short‑lived page locks (RWLocks) and long‑lived transaction locks (row/table locks). 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; it must release the page lock first.

If a transaction needs to wait for a row lock 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.

3. Summary

Understanding MySQL lock types, the three lock modes, and InnoDB's deadlock prevention mechanisms helps developers design SQL statements that avoid lock ordering issues. Using consistent lock ordering, batch locking with IN (...), and INSERT ... ON DUPLICATE KEY UPDATE are practical ways to eliminate many deadlock scenarios.