MySQL Lock Types, Deadlock Causes, and Prevention Strategies

Recently I summarized a series of deadlock problems to share with everyone; the content is compiled from various blogs and references are listed at the end.

MySQL Lock Types and Locking Analysis

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

Table‑level lock: low overhead, fast acquisition, no deadlocks, but coarse granularity leads to high contention and low concurrency.

Row‑level lock: higher overhead, slower acquisition, can cause deadlocks, but fine granularity gives the lowest contention and highest concurrency.

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

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, not the gap.

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

Deadlock Causes and Examples

Root Cause

A deadlock ( <DeadLock>) occurs when two or more sessions wait for each other’s resources, causing a standstill unless external intervention occurs. Table‑level locks do not cause deadlocks; the focus is on InnoDB row‑level locks.

The key to deadlocks is inconsistent lock acquisition order across sessions.

Resolving deadlocks therefore requires enforcing a consistent lock order.

Example 1

Case 1

Requirement: split an investment amount into several random portions and assign them to borrowers.

Original logic uses multiple SELECT ... FOR UPDATE statements on different borrowers, leading to opposite lock orders and a deadlock.

Improvement: lock all target borrowers in a single statement, e.g.,

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

Case 2

Scenario: upsert pattern (select then insert) on a primary‑key column can cause deadlocks when two sessions try to insert different rows simultaneously.

-- Session 1
SELECT * FROM t3 WHERE id=22 FOR UPDATE; -- empty
INSERT INTO t3 VALUES(22,'ac','a',NOW());

-- Session 2
SELECT * FROM t3 WHERE id=23 FOR UPDATE; -- empty
INSERT INTO t3 VALUES(23,'bc','b',NOW()); -- deadlock error

MySQL uses row locks for existing rows and gap locks for non‑existent rows, which explains the deadlock.

Case 3

Similar to Case 1, but Session 1 inserts a row with an id lower than the range locked by Session 2, causing circular waiting.

Case 4

Two sessions each hold a lock on different rows and then request the other's row, forming a classic deadlock.

Case 5

Two single‑statement SQLs lock the same set of rows in opposite order, leading to deadlock.

Case 6

Deadlock scenario involving a DELETE on a table with a unique composite index (a,b,c) under Repeatable Read isolation.

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

Only one SQL per transaction: DELETE FROM dltask WHERE a=? AND b=? AND c=?; InnoDB’s deadlock prevention strategy ensures that a session holding a page lock cannot wait for a row lock; instead, it releases the page lock and retries.

Deadlock Prevention Strategy

InnoDB employs multiple lock types: transaction locks (row, table), mutexes, and RWLocks (latches) for page protection.

When modifying a record, InnoDB:

Locates the page containing the target record.

Acquires an X lock on the page (RWLock).

Within the page lock, acquires the appropriate transaction lock on the record (row lock, next‑key lock, or gap lock) based on the record’s existence and state.

The prevention rule: a session holding a transaction lock may wait for a page lock, but a session holding a page lock must not wait for a transaction lock. If a row lock cannot be obtained, the page lock is released, the row lock is retried, and the page lock is reacquired.

This logic is implemented in row0sel.c::row_search_for_mysql().

Analyzing Deadlock Causes

After understanding the three lock strategies for DELETE and InnoDB’s prevention policy, we can dissect the original deadlock examples.

Key prerequisites for the illustrated deadlocks:

DELETE on a unique index with equality condition.

At least three concurrent delete operations.

All deletions target the same existing row.

Isolation level is Repeatable Read and innodb_locks_unsafe_for_binlog is disabled.

Storage engine is InnoDB.

How to Detect Deadlocks in Code

https://blog.csdn.net/kk185800961/article/details/79528841

https://blog.csdn.net/yucaifu1989/article/details/79400446

References

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

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

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