Understanding MySQL Lock Types and How to Prevent Deadlocks

Lock Types in MySQL

MySQL supports three lock granularity levels:

Table‑level lock : low overhead, fast acquisition, never causes deadlocks, but provides the lowest concurrency.

Row‑level lock (InnoDB) : higher overhead, can cause deadlocks, but offers the highest concurrency.

Page‑level lock : cost and concurrency lie between table and row locks; deadlocks are possible.

Lock Algorithms (InnoDB)

Next‑Key Lock : locks the target record and the gap preceding it.

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

Record Lock : locks the record without locking the surrounding gap.

Thus, a Next‑Key Lock = Gap Lock + Record Lock.

What Triggers a Deadlock?

A deadlock occurs when two or more sessions acquire locks in different orders, causing each to wait indefinitely for the other. Table‑level locks never deadlock, so the focus is on row‑level (InnoDB) locks. The key to avoiding deadlocks is to enforce a consistent lock acquisition order across sessions.

Practical Examples

Example 1 – Random Allocation

Two users split an investment into random parts and assign them to borrowers. Using separate SELECT … FOR UPDATE statements on different borrower IDs leads to a deadlock because the lock order differs.

Solution: lock all target borrowers in a single statement; MySQL automatically sorts the IN list, guaranteeing a consistent order.

SELECT * FROM borrowers WHERE id IN (1,2,3) FOR UPDATE;

Example 2 – Upsert with Duplicate Key

Two sessions check for a row's existence and insert if absent. Each session locks a different primary‑key value first, then attempts to insert the other's key, causing a deadlock.

-- Session 1
INSERT INTO t3(id, col1, col2, created_at) VALUES(22,'ac','a',NOW());
-- Session 2
INSERT INTO t3(id, col1, col2, created_at) VALUES(23,'bc','b',NOW());
-- Session 2 receives ERROR 1213 (Deadlock found)

Using INSERT … ON DUPLICATE KEY UPDATE avoids the problem because the statement acquires a row lock regardless of whether the primary key already exists.

Example 3 – Mixed Range and Point Locks

Session A locks a specific row (e.g., id=9) with SELECT … FOR UPDATE. Session B locks a range that includes that row (e.g., WHERE id<20 FOR UPDATE). The two sessions wait on each other's locks, forming a circular wait.

Example 4 & 5 – Classic Deadlock Visualisation

Example 6 – Delete on a Unique Index

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 statement that may acquire a next‑key lock on the deleted row
DELETE FROM dltask WHERE a=? AND b=? AND c=?;

When a row matching a unique index is deleted, InnoDB may acquire a next‑key lock (record + gap) on the deleted record, or a gap lock if the record does not exist. Concurrent deletes on the same unique key can therefore intersect and cause deadlocks.

InnoDB Locking Mechanics

InnoDB uses three internal lock categories:

Transaction locks (row or table locks).

Mutexes (protect internal shared variables).

RWLocks (latches) that guard page reads/writes.

Each 16 KB page is protected by an S‑lock for reads and an X‑lock for writes. The lock acquisition process is:

Locate the page containing the target record based on the query condition.

Acquire an X‑lock (RWLock) on that page.

Within the page, apply the appropriate transaction lock:

If the record exists and is not deleted → Record lock (X lock, no gap).

If the record exists but is marked deleted → Next‑Key lock (X lock on record + gap).

If no matching record → Gap lock (locks the gap before the first greater record).

Deadlock Prevention Strategy

Page locks are short‑lived, while transaction locks are held for the duration of the transaction. InnoDB prevents deadlocks by allowing a transaction that already holds a row lock to wait for a page lock, but not the reverse. If a session holding a page lock needs a row lock that is blocked, it releases the page lock, waits for the row lock, then reacquires the page lock to re‑verify the record state.

Root Causes of Delete Deadlocks on Unique Indexes

Delete statements use equality conditions on a unique index.

At least three concurrent delete transactions target the same record.

The record is guaranteed to exist.

Isolation level is REPEATABLE READ and innodb_locks_unsafe_for_binlog is disabled.

InnoDB storage engine is used (MyISAM has no row locks).

Under these conditions InnoDB may acquire intersecting next‑key locks, leading to circular waits.

References

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

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

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