Why MySQL Deadlocks Happen and How to Prevent Them

1. From the error log to the deadlock

The log shows a warning and error from SqlExceptionHelper indicating a deadlock:

04:12~28.452 [traceId-] WARN  SqlExceptionHelper - SQL Error: 1213, SQLState: 40001
04:12~28.452 [traceId-] ERROR SqlExceptionHelper - Deadlock found when trying to get lock; try restarting transaction

Business scenario: a Kafka consumer updates comment records based on an article ID. Two servers receive the same message almost simultaneously, leading to opposite lock acquisition orders and a deadlock.

2. Two‑phase locking (2PL)

InnoDB uses the Two‑Phase Locking protocol to guarantee transaction isolation. The protocol has two phases:

Growing phase : a transaction may acquire locks but cannot release them.

Shrinking phase : a transaction may release locks but cannot acquire new ones.

The transition point occurs when the transaction releases its first lock.

Row locks are added only when needed and are released at transaction end.

3. Reproducing the deadlock

Database version: MySQL 8.0.32.

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `content` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=34;

Prepare data (omitted for brevity).

Open two SQL sessions and disable autocommit: set autocommit=0; Session 1:

begin;
update test set content='赵云1' where id=31;
update test set content='张飞1' where id=32;

Session 2:

begin;
update test set content='孙策2' where id=33;
update test set content='赵云2' where id=31;

Session 1 continues: update test set content='孙策1' where id=33; Checking the engine status shows a deadlock with two transactions holding conflicting locks on rows A, B, C, forming a circular wait.

4. InnoDB lock classifications

Locks are categorized by mode and granularity.

Shared lock (S) : also called a read lock; multiple transactions can hold it simultaneously, but no exclusive lock can be granted.

Exclusive lock (X) : also called a write lock; only one transaction can hold it, preventing any other lock.

Intention locks (IS/IX) : table‑level locks indicating that a transaction intends to acquire row‑level shared or exclusive locks.

Granularity :

Global lock – whole instance (used for backups).

Table lock – whole table (used before row‑level locks existed).

Row lock – fine‑grained lock on specific records.

Row‑level lock types:

Record lock : locks a specific index record.

Gap lock : locks the gap between index records, preventing phantom inserts.

Next‑key lock : a combination of record lock and the preceding gap lock (default under REPEATABLE READ).

Insert intention lock : a special gap lock used when inserting into a gap.

5. Compatibility matrix

Lock request compatibility (S, X, Gap, Insert‑Intention) is summarized as:

Request \ Held   S   X   Gap   Insert‑Intention
S                compatible   conflict   compatible   compatible
X                conflict   conflict   conflict   conflict
Insert‑Intention compatible   conflict   conflict   compatible
Gap               compatible   conflict   compatible   conflict

6. How InnoDB locks under REPEATABLE READ

Locking is performed with next‑key locks. Depending on the query pattern, the lock may be downgraded:

Scenario 1 – Unique index equality (row exists) : SELECT * FROM users WHERE id=5 FOR UPDATE; Downgrades to a record lock on id=5 only.

Scenario 2 – Unique index equality (row missing) : SELECT * FROM users WHERE id=7 FOR UPDATE; Locks the gap (5,10) with a gap lock.

Scenario 3 – Non‑unique index equality (rows exist) : SELECT * FROM users WHERE age=20 FOR UPDATE; Keeps the next‑key lock on all matching rows and surrounding gaps.

Scenario 4 – Range query on non‑unique index :

SELECT * FROM users WHERE age>=20 AND age<30 FOR UPDATE;

Locks all scanned rows and the gaps between them.

A summary table (converted from the original HTML) lists the downgrade results for each query type.

7. Common deadlock patterns

Cross‑update order – two transactions update rows in opposite order (A→B→C vs C→A→B), creating a circular wait.

-- Transaction A
UPDATE table1 SET ... WHERE id=1;   -- lock row 1
-- Transaction B
UPDATE table1 SET ... WHERE id=2;   -- lock row 2
-- Transaction A attempts row 2, blocks
-- Transaction B attempts row 1, blocks

Unique‑key conflict – concurrent INSERT … ON DUPLICATE KEY UPDATE on the same unique key acquires shared locks, then both try to upgrade to exclusive locks.

INSERT INTO table (uniq_col, ...) VALUES (100, ...);
INSERT INTO table (uniq_col, ...) VALUES (100, ...) ON DUPLICATE KEY UPDATE ...;

Index‑scan order mismatch – a range UPDATE scans rows in index order while another transaction directly updates a specific row, causing intersecting lock paths.

-- Transaction A (range)
SELECT * FROM table WHERE idx_col BETWEEN 10 AND 20 FOR UPDATE;
-- Transaction B (point)
DELETE FROM table WHERE idx_col=15;

Gap‑lock conflict during concurrent inserts – two sessions acquire gap locks on the same interval and then try to insert into that gap, each needing an insert‑intention lock that conflicts with the other's gap lock.

-- Assume rows with id=5 and id=10 exist
SELECT * FROM table WHERE id=7 FOR UPDATE;   -- gap lock (5,10)
SELECT * FROM table WHERE id=8 FOR UPDATE;   -- gap lock (5,10)
INSERT INTO table (id, ...) VALUES (7, ...); -- needs insert‑intention lock, blocked
INSERT INTO table (id, ...) VALUES (8, ...); -- blocked similarly

Lock upgrade conflict between UPDATE and DELETE – one transaction holds an X lock on a row, another transaction holds a shared lock while trying to delete or update the same row, leading to a deadlock.

Implicit‑to‑explicit lock conversion – an INSERT creates an implicit lock; a concurrent DELETE or SELECT … FOR UPDATE forces the implicit lock to become an explicit X lock, which can clash with other transactions.

8. How to avoid deadlocks

Maintain a consistent access order for tables and rows across all transactions.

Keep transactions short; commit or rollback as soon as possible.

Use appropriate indexes to avoid full‑table scans and to enforce a deterministic lock order.

Consider a lower isolation level (READ COMMITTED) if phantom reads are acceptable, as it reduces gap‑lock usage.

Implement retry logic for error 1213 (deadlock) at the application layer.

9. Deadlock monitoring parameters

Two key MySQL variables control deadlock detection and lock waiting:

innodb_deadlock_detect (ON by default). When enabled, InnoDB builds a wait‑for graph and rolls back the transaction with the smallest undo log when a cycle is found.

innodb_lock_wait_timeout (default 50 seconds). If deadlock detection is disabled, transactions wait this long before timing out, which reduces CPU overhead but may increase lock‑wait time.