Understanding MySQL InnoDB Deadlocks: Causes, Lock Types & Prevention

All starts from the error log

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

Wow, a deadlock occurred.

Business scenario: consume Kafka messages and update comment records by ID sequentially.

It turns out like this

Two‑phase locking (Two‑Phase Locking, 2PL) is a concurrency control mechanism that guarantees transaction isolation by dividing lock usage into two phases:

Locking phase (Growing Phase) : a transaction can acquire locks but cannot release them.

Unlocking phase (Shrinking Phase) : a transaction can release locks but cannot acquire new ones.

Two‑phase boundary : after the first lock release, the transaction enters the unlocking phase and cannot acquire new locks.

In the deadlock example, the first transaction locks rows in order A → B → C, while the second transaction locks C → A → B, leading to a circular wait.

Let's reproduce it

Database version: MySQL 8.0.32.

1. Create a simple table:

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;

2. Insert sample data:

3. Open two SQL sessions and disable autocommit: set autocommit=0; 4. In session 1 execute:

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

5. In session 2 execute:

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

6. In session 1 execute: update test set content='孙策1' where id=33; 7. Show engine status to see the deadlock report:

show engine innodb status;

InnoDB lock classification

Locks can be classified by mode and granularity.

Shared lock (S) : also called read lock; multiple transactions can hold it simultaneously, but exclusive locks are blocked.

Exclusive lock (X) : also called write lock; only one transaction can hold it and no other lock (S or X) can be granted.

Intention locks (IS/IX) : table‑level locks that indicate a transaction intends to acquire row‑level S or X locks.

Granularity:

Global lock – whole instance.

Table lock – whole table.

Row lock – specific rows.

Row‑lock types:

Record lock : locks the index record itself.

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

Next‑key lock : combination of record lock and gap lock, used in REPEATABLE READ isolation.

Insert intention lock : a special gap lock used for concurrent inserts.

Compatibility matrix

Shared lock is compatible with other shared locks and gap locks, but conflicts with exclusive locks. Exclusive lock conflicts with all other lock types. Insert intention lock is compatible with shared locks but conflicts with exclusive and gap locks.

How locks are acquired in REPEATABLE READ

Locking unit

The basic unit is the next‑key lock (open‑closed interval).

Next‑key lock downgrade rules

Unique index equality query that finds a row → downgrade to record lock (locks only the row).

Unique index equality query that does not find a row → downgrade to gap lock (locks the gap).

Non‑unique index equality query → keep next‑key lock (locks matching rows and surrounding gaps).

Range query → keep next‑key lock (locks all scanned rows and gaps).

Common row‑lock deadlock scenarios

Cross‑order updates of different rows (A→B vs B→A).

Unique‑key conflict caused by concurrent inserts.

Index scan order mismatch between a range scan and a point operation.

Gap‑lock conflict when multiple transactions insert into the same gap.

Lock‑upgrade conflict between UPDATE and DELETE/UPDATE on the same row.

Implicit‑to‑explicit lock conversion during INSERT followed by DELETE/UPDATE.

How to avoid deadlocks

Maintain a consistent access order across all transactions.

Keep transactions short and commit quickly.

Use appropriate indexes to avoid full‑table scans.

Prefer a lower isolation level such as READ COMMITTED when possible.

Implement retry logic for SQL error 1213 (deadlock).

Deadlock monitoring

Key parameters:

innodb_deadlock_detect – enables the InnoDB deadlock detector (ON by default in MySQL 8.0). When a wait‑for graph shows a cycle, InnoDB rolls back the transaction with the smallest undo log.

innodb_lock_wait_timeout – timeout (seconds) for waiting on row locks (default 50 s). If deadlock detection is disabled, this timeout determines when a transaction is aborted.

Proper tuning of these parameters together with good transaction design reduces the likelihood of deadlocks.