MySQL Lock Types and Deadlock Analysis

1. MySQL Lock Types and Locking Analysis

Lock Type Introduction

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

Table‑level lock: low overhead, fast acquisition, no deadlocks, large granularity, highest lock‑conflict probability, lowest concurrency.

Row‑level lock: high overhead, slower acquisition, can cause deadlocks, smallest granularity, lowest lock‑conflict probability, highest concurrency.

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

Locking algorithms:

Next‑Key lock: locks the record (data) and the gap before the record.

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

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

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

2. Causes and Examples of Deadlocks

1. Causes

Deadlock (<DeadLock>) occurs when two or more sessions wait for each other’s resources, creating a state where none can proceed. Table‑level locks do not generate deadlocks; the focus is on InnoDB.

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

The solution is to enforce a consistent lock order for all sessions.

2. Example Scenarios

Case 1

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

Initial logic uses multiple select for update statements to update borrower balances.

When two users invest simultaneously, differing lock order quickly leads to a deadlock.

Improvement: lock all target borrowers in a single statement.

Select * from xxx where id in (xx,xx,xx) for update

MySQL automatically sorts the IN list, acquiring locks in ascending order.

Session examples illustrate lock waiting and deadlock:

Session1:
select * from t3 where id in (8,9) for update;
-- rows returned ...

Session2:
select * from t3 where id in (10,8,5) for update;
-- lock waiting ...

Session3:
select * from t3 where id=5 for update; -- lock waiting

Session4:
select * from t3 where id=10 for update; -- succeeds

Case 2

Typical upsert pattern: query by indexed field, insert if not exists, otherwise update.

Two sessions try to lock different primary‑key values (22 and 23) and then insert, causing a deadlock:

Session1:
select * from t3 where id=22 for update; -- empty
Session2:
select * from t3 where id=23 for update; -- empty

Session1:
insert into t3 values(22,'ac','a',now());
-- lock waiting ...

Session2:
insert into t3 values(23,'bc','b',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

When locking a non‑existent row, MySQL creates a gap lock; when locking an existing row, only a row lock is used.

Solution: use MySQL’s INSERT ... ON DUPLICATE KEY UPDATE syntax, which always acquires a row lock.

insert into t3(xx,xx) on duplicate key update `xx`='XX';

Case 3

Two sessions lock overlapping ranges with FOR UPDATE , leading to a deadlock when one session inserts a new row within the other's locked range.

Session1:
select * from t3 where id=9 for update; -- row locked

Session2:
select * from t3 where id<20 for update; -- lock waiting

Session1:
insert into t3 values(7,'ae','a',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Case 4

Two sessions each hold a lock on a single row and then request the other session’s row, forming a classic deadlock.

Case 5

Two single‑statement SQLs lock the same data but in opposite order, causing deadlock.

Case 6

Deadlock scenario involving 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';

Each transaction runs a single statement: delete from dltask where a=? and b=? and c=?; InnoDB’s handling of deleted records (marked as deleted but still present in the index) leads to next‑key locks on the gap before the deleted record, which can conflict with other transactions.

InnoDB Deadlock Prevention Strategy

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

Find the target record and, if it is valid, acquire an X lock without a gap (No Gap lock).

If the record is marked as deleted, acquire a next‑key lock (X lock on the record plus the preceding gap).

If no matching record is found, acquire a gap lock on the first non‑matching record to prevent new inserts.

Deadlock prevention rule: a transaction holding a row/table lock may wait for a page lock, but a transaction holding a page lock cannot wait for a transaction lock. If a row lock is blocked while a page lock is held, InnoDB releases the page lock, waits for the row lock, then reacquires the page lock. The relevant internal function is row0sel.c::row_search_for_mysql() , which implements this complex logic.

Analyzing the Root Causes of Deadlocks

After understanding the three locking strategies for DELETE, InnoDB’s deadlock‑prevention mechanisms, and the prerequisite conditions (unique‑index delete, ≥3 concurrent deletes, REPEATABLE READ, InnoDB engine), we can see why the earlier examples deadlock. Example: three concurrent DELETE statements on the same unique key can each acquire different next‑key locks, leading to a circular wait.

delete from dltask where a='a' and b='b' and c='c';

This demonstrates how lock ordering, gap locking, and InnoDB’s internal policies combine to produce deadlocks.

Author: Wang Xiao (tr1912) Source: blog.csdn.net/tr1912/article/details/81668423

END For more interview questions and technical articles, follow the links below.