Mastering MySQL Deadlocks: Types, Logs, and Prevention Strategies

Hello everyone, I am Wolf King, a programmer who loves sports.

When using MySQL, deadlocks are a common and painful problem; this article introduces deadlocks, analyzes typical cases, and offers suggestions to avoid them.

Let’s get started!

What Is a Deadlock

A deadlock occurs in concurrent systems, including MySQL, when two or more transactions each hold a lock and request a lock held by another, forming a circular wait. The typical error message is Deadlock found when trying to get lock....

For example, transaction A holds lock X1 and requests X2, while transaction B holds X2 and requests X1, creating a deadlock.

The diagram shows four cars forming a loop, illustrating a deadlock.

Key elements for a MySQL deadlock are:

Two or more transactions

Each transaction holds a lock and requests a new one

Lock resources can be held by only one transaction or be incompatible

Transactions wait for each other’s locks, forming a cycle

InnoDB Lock Types

Understanding InnoDB lock types is essential for deadlock analysis.

InnoDB implements row‑level locks: shared lock (S) and exclusive lock (X).

Different transactions can acquire an S lock on the same row. If a transaction holds an X lock on a row, no other transaction can acquire S or X locks on that row, causing lock wait.

If transaction T1 holds an S lock on row r, another transaction T2 requesting an S lock is granted immediately, while a request for an X lock must wait.

T2’s S‑lock request is allowed; both T1 and T2 hold S locks. T2’s X‑lock request cannot be granted immediately.

If T1 holds an X lock, any request (S or X) from T2 must wait because X locks are incompatible with all other locks.

Gap Lock

A gap lock prevents inserts into a range. For an index with values 2, 4, 8, locking value 4 also locks the gaps (2, 4) and (4, 8). If the indexed column is a unique index, only the row lock is taken, not the gap.

If the index column is unique, only the row is locked. For a composite unique index, a partial WHERE clause still creates a gap lock.

Next‑Key Lock

A next‑key lock combines a row lock with the preceding gap lock. With index values 10, 11, 13, 20, the possible next‑key locks are (‑∞,10], (10,11], (11,13], (13,20], (20, +∞).

(‑∞,10],(10,11],(11,13],(13,20],(20, +∞)

In REPEATABLE READ isolation, InnoDB uses next‑key locks to prevent phantom reads.

Intention Locks

Intention locks (IS for shared, IX for exclusive) indicate a transaction’s intention to acquire row‑level locks, allowing coexistence of row and table locks.

Intention Shared (IS): transaction intends to set shared row locks. Intention Exclusive (IX): transaction intends to set exclusive row locks.

Insert Intention Lock

Before inserting a row, an insert intention lock (a type of gap lock) signals that the transaction will insert into a gap; if two transactions insert into different positions within the same gap, they do not block each other.

Lock Compatibility Matrix

The matrix shows which lock modes are compatible; rows are requested locks, columns are held locks.

Reading Deadlock Logs

Before analyzing cases, learn how to interpret the deadlock log.

Test environment: MySQL 5.7 with REPEATABLE READ isolation.

Table schema and data are shown in the following diagrams.

Test case diagram:

Running SHOW ENGINE INNODB STATUS reveals the latest deadlock log.

Log Analysis Example

***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

This transaction is reading via an index. Other possible states are shown in the diagram.

mysql tables in use 1

indicates one table is used. locked 1 shows a table lock (LOCK_IX for DML). LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) means the transaction is waiting for two lock structures, including an IX lock and a next‑key lock. 1 row lock(s) indicates the number of row or gap locks held. MySQL thread id 37 ... query id 1234 ... updating shows the thread executing the transaction. delete from student where stuno=5 is the SQL being executed; the full statement may not appear in the log.

***** (1) WAITING FOR THIS LOCK TO BE GRANTED:

Record lock on index idx_stuno of table student waiting for X lock (next‑key lock).

Transaction 2 holds a lock similar to the above:

***** (2) HOLDS THE LOCK(S): RECORD LOCKS ... lock_mode X shows transaction 2’s INSERT INTO student(stuno,score) VALUES(2,10) holding an X lock.

Transaction 2’s insert intention lock is waiting for a gap lock, which is not visible in the log, illustrating why DBA analysis can be difficult.

***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS ... lock_mode X locks gap before rec insert intention waiting

indicates transaction 2’s insert is waiting for an insert intention lock.

Classic Case Analyses

Case 1: Concurrent Inserts Causing Unique‑Key Conflict

Table structure and data are shown below.

Test steps diagram:

Log analysis:

Transaction T2 inserts (26,10) successfully, acquiring an exclusive row lock.

Transaction T1 attempts to insert (30,10); the unique index conflict forces a shared next‑key lock (gap lock) on the range ( ,10] and (10,20].

Transaction T2 then tries to insert (40,9), which falls into the gap locked by T1, so it must wait for T1’s S‑next‑key lock, showing lock_mode X locks gap before rec insert intention waiting.

Case 2: Update Followed by Insert Deadlock

Table structure (empty) diagram:

Test steps diagram:

Analysis: Both transactions update a non‑existent row, acquiring compatible gap locks, then each attempts an insert that requires an intention lock. When another session holds a gap lock, the insert intention lock cannot be granted, leading to deadlock.

How to Minimize Deadlocks

Design indexes with high‑cardinality columns first so SQL can locate fewer rows and reduce lock contention.

Order SQL statements to avoid long‑running updates/deletes holding locks early in a transaction.

Avoid large transactions; split them into smaller ones to lower the chance of lock conflicts.

Access tables and rows in a consistent order across transactions.

In high‑concurrency systems, avoid explicit locking (e.g., SELECT … FOR UPDATE) inside transactions unless necessary.

Prefer primary‑key or indexed lookups; range scans and non‑indexed queries (e.g., ORDER BY RAND()) can lock many rows.

Optimize SQL and schema to reduce resource usage, such as minimizing table joins and breaking complex queries into simpler statements.

That’s all for now; I will continue sharing my learning and hope we all succeed together!