Mastering MySQL Lock Types and Deadlock Prevention

MySQL Lock Types and Analysis

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

Table‑level lock: low overhead, fast to acquire, never causes deadlock, but locks a large data range, leading to the highest contention and lowest concurrency.

Row‑level lock: higher overhead, slower to acquire, can cause deadlocks, locks the smallest data range, offering the lowest contention and highest concurrency.

Page‑level lock: overhead and acquisition time lie between table and row locks; it can cause deadlocks and its granularity is between the other two.

Lock Algorithms

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

Gap lock: locks only the gap before a record.

Record lock: locks the record itself without the gap.

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

Deadlock Definition and Causes

A deadlock ( <DeadLock>) occurs when two or more sessions wait for each other’s resources, preventing any progress unless external intervention occurs. Table‑level locks never cause deadlocks; the focus is on InnoDB row‑level locking.

The key cause is inconsistent lock acquisition order across sessions.

Case 1: Random Investment Allocation

Two users allocate investment amounts to borrowers using SELECT ... FOR UPDATE on different borrower rows, leading to opposite lock orders and a deadlock. The fix is to lock all target rows in a single statement, e.g., SELECT * FROM borrowers WHERE id IN (… ) FOR UPDATE, which MySQL automatically orders by primary key.

Session1:
SELECT * FROM t3 WHERE id IN (8,9) FOR UPDATE;
Session2:
SELECT * FROM t3 WHERE id IN (10,8,5) FOR UPDATE;
...

Case 2: Insert‑Or‑Update Pattern

Two sessions concurrently check for a row’s existence and then insert, causing a deadlock when both attempt to lock the same primary‑key gap. Using INSERT ... ON DUPLICATE KEY UPDATE avoids the problem because the operation acquires only a row lock.

Session1:
SELECT * FROM t3 WHERE id=22 FOR UPDATE;
INSERT INTO t3 VALUES(22,'ac','a',NOW());
Session2:
SELECT * FROM t3 WHERE id=23 FOR UPDATE;
INSERT INTO t3 VALUES(23,'bc','b',NOW());
ERROR 1213 (40001): Deadlock found...

Case 3: Mixed Range and Point Locks

A session locks a specific row while another locks a range that includes that row, leading to a deadlock when the first session later inserts a new row within the locked range.

Session1:
SELECT * FROM t3 WHERE id=9 FOR UPDATE;
Session2:
SELECT * FROM t3 WHERE id<20 FOR UPDATE;
Session1:
INSERT INTO t3 VALUES(7,'ae','a',NOW());
ERROR 1213 (40001): Deadlock...

Case 4 & 5: Simple Two‑Statement Deadlocks

Images illustrate two sessions each holding a lock on one row and then requesting the other's locked row, creating a classic deadlock.

Case 6: Delete with Unique Index

A table with a unique composite index (a,b,c) and primary key id is used. Multiple concurrent DELETE statements on the same unique key can deadlock under Repeatable Read isolation because InnoDB may acquire next‑key locks on deleted rows.

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';

Each transaction runs a single DELETE:

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

InnoDB Lock‑Prevention Strategy

InnoDB uses several lock types: transaction locks (row, table), mutexes, and RWLocks (latches). Page locks (S for read, X for write) are short‑lived, while transaction locks are long‑lived. To avoid deadlocks between page and transaction locks, InnoDB allows a transaction lock to wait for a page lock, but not the reverse. If a transaction holding a page lock must wait for a row lock, it releases the page lock, acquires the row lock, then re‑acquires the page lock.

Analyzing Deadlock Roots

After understanding the three delete‑lock strategies and InnoDB’s prevention logic, the article revisits the initial deadlock example, showing how differing lock acquisition orders and the presence of next‑key locks lead to the observed deadlock.

Delete on a unique index with equality condition.

At least three concurrent delete operations.

All target rows exist.

Isolation level is Repeatable Read without disabling gap locks.

Storage engine is InnoDB.

References

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

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

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