Understanding MySQL Lock Types and How to Resolve Common Deadlocks

Lock Types in MySQL

MySQL provides three lock granularities: table‑level locks (low overhead, fast acquisition, high contention, no deadlocks), row‑level locks (higher overhead, can deadlock, smallest granularity, lowest contention), and page‑level locks (overhead and acquisition time between table and row, can deadlock, moderate contention).

Lock Algorithms

In InnoDB, a next‑key lock combines a gap lock (locks the interval before a record) and a record lock (locks the record itself), so next‑key lock = gap lock + record lock.

Why Deadlocks Occur

A deadlock happens when two or more sessions acquire locks in different orders, causing each to wait for the other. The key to solving deadlocks is to enforce a consistent lock acquisition order across sessions.

Example 1: Random Allocation to Borrowers

Two users invest simultaneously, each splitting the amount into two random borrowers and updating balances with separate SELECT ... FOR UPDATE statements. Because the IN list is sorted, the lock order differs, leading to a deadlock.

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

Solution: lock all target borrowers in a single statement so the lock order is identical.

Example 2: Upsert Pattern

When checking for a row's existence with SELECT ... FOR UPDATE and then inserting if absent, concurrent sessions can deadlock.

select * from t3 where id=22 for update;

insert into t3 values(22,'ac','a',now());

MySQL returns

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

. Using the MySQL‑specific upsert syntax avoids the deadlock because the statement acquires only a row lock regardless of existence:

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

Example 3: Range Lock Conflict

One session locks a specific row with SELECT ... FOR UPDATE, while another session locks a range that includes that row, causing both to wait and deadlock.

select * from t3 where id=9 for update;

select * from t3 where id<20 for update;

The second session holds locks on ids 1‑8, the first session waits for id 9, and the second later waits for the lock held by the first, forming a deadlock.

Typical Deadlock Patterns (Images)

Two sessions each acquire a lock on one row and then request the lock held by the other, creating a classic deadlock.

Another illustration shows two single‑statement SQLs accessing the same rows in opposite order.

Example 6: Delete on a Unique Index

Table definition:

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

Isolation level: REPEATABLE READ. Each transaction runs a single DELETE FROM dltask WHERE a=? AND b=? AND c=? statement.

InnoDB lock strategies for a unique‑index lookup:

Record exists and is valid → X lock on the record (No‑Gap lock).

Record exists but is marked deleted → Next‑key lock (record + preceding gap).

No matching record → Gap lock before the first greater record.

InnoDB also employs a deadlock‑prevention policy: a transaction holding a row lock may wait for a page lock, but a transaction holding a page lock cannot wait for a row lock. If a row lock is needed while holding a page lock, the page lock is released, the row lock is acquired, and then the page lock is reacquired.

The relevant source code is row0sel.c::row_search_for_mysql().

Analyzing the Original Deadlock Cases

Given a single row in dltask (1, 'a', 'b', 'c', 'data'), three concurrent delete statements on the same unique key can deadlock under the following conditions:

Delete targets a unique‑index equality query.

At least three concurrent delete operations.

All transactions target the same existing row.

Isolation level is REPEATABLE READ and innodb_locks_unsafe_for_binlog is OFF.

Storage engine is InnoDB.

Execution flow diagrams (images) illustrate how the lock acquisition order differs between transactions, leading to a deadlock.

Detecting Deadlocks

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

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