How to Diagnose and Fix MySQL Deadlocks Using Locks and Indexes

Prerequisite Knowledge

MySQL uses two basic lock strategies:

Table locks – lock the whole table. Write operations need an exclusive (X) lock, reads need a shared (S) lock. Overhead is minimal.

Row locks (InnoDB only) – allow concurrent processing but add more overhead because they are implemented at the storage‑engine level.

Row‑lock modes

Intention locks: IS / IX

Shared lock: S

Exclusive lock: X

Auto‑increment lock: AI

Specialized locks: Next‑Key, Gap, Record, Insert‑Intention Gap

Lock types appear in the deadlock log as:

Record lock (LOCK_REC_NOT_GAP): X locks the record but not the gap

Gap lock (LOCK_GAP): X locks the gap before the record

Next‑key lock (LOCK_ORNIDARY): X lock on record + gap

Insert‑intention lock (LOCK_INSERT_INTENTION): X locks the gap for an insert

Row‑lock example

InnoDB stores the primary key and row data in a clustered B+‑tree index. Secondary indexes contain only the primary key, so a query using a secondary index must first fetch the primary key and then read the clustered row. update user set age = 10 where id = 49; Uses the primary key; only the record id=49 receives an X lock. update user set age = 10 where name = 'Tom'; Uses a secondary index on name='Tom'; MySQL first locks the secondary‑index entry, then locks the primary‑key record id=49.

Multi‑row update example: update user set age = 10 where id > 49; Execution steps:

Server reads the first matching row; InnoDB returns the row with a lock.

Server issues an UPDATE for that row.

Steps 1‑2 repeat until all matching rows are updated.

Preparation

Create table and initialise data

create table dead_lock_test (
    id int auto_increment primary key,
    v1 int not null,
    v2 int not null
);
insert into dead_lock_test (v1,v2) values (1,1);
insert into dead_lock_test (v1,v2) values (2,2);
insert into dead_lock_test (v1,v2) values (3,3);

The table has only a primary‑key index and uses InnoDB with the default REPEATABLE READ isolation level.

Enable lock monitoring

set GLOBAL innodb_status_output = ON;
set GLOBAL innodb_status_output_locks = ON;
-- To disable later:
set GLOBAL innodb_status_output_locks = OFF;

Reproducing the deadlock

Open two MySQL sessions and run the following statements.

# session1
start transaction;
insert into dead_lock_test (v1,v2) values (4,4);
delete from dead_lock_test where v1 = 4 and v2 = 4;
commit;

# session2
start transaction;
insert into dead_lock_test (v1,v2) values (5,5);
delete from dead_lock_test where v1 = 5 and v2 = 5;
commit;

Timeline (simplified):

Both sessions start a transaction.

Session 1 inserts (4,4) successfully.

Session 2 inserts (5,5) successfully.

Session 1 attempts to delete the row (v1=4,v2=4). Because the WHERE clause cannot use an index, InnoDB acquires a record lock on **every** row. It blocks on the record with primary key 5, which is locked by session 2.

Session 2 then attempts to delete (v1=5,v2=5). It also tries to lock all rows and blocks on the record with primary key 4, which is held by session 1. The two transactions wait for each other → deadlock.

Stage 1 – Transaction overview

Running show engine innodb status; shows two active transactions (ids 91322 and 91327) each holding an IX table lock.

Stage 2 – Session 1 blocked

Session 1’s DELETE acquires a record lock on every row. The lock log shows a record lock on the row with primary key 5 held by session 2, causing session 1 to wait.

Stage 3 – Session 2 deadlocks

Session 2’s DELETE also needs a lock on the row with primary key 4, which is held by session 1. MySQL aborts one transaction and returns:

[40001][1213] Deadlock found when trying to get lock; try restarting transaction

The detailed innodb status output confirms that each transaction holds locks on a subset of rows and is waiting for a lock held by the other, forming a classic circular wait.

Solution

Add a composite index

The deadlock occurs because the DELETE statements cannot use an index, forcing MySQL to lock every row. Adding a composite index on v1 and v2 narrows the lock scope to the matching rows only.

create index dead_lock_test_v1_v2_idx on dead_lock_test (v1, v2);

After the index is created, the DELETE statements use the index to locate the target rows directly, reducing the number of locked rows and eliminating the deadlock. The index is non‑unique because the business data may contain duplicate (v1,v2) pairs; a unique index could still cause overlapping locks under high concurrency.

References

Visualising MySQL lock types and principles https://zhuanlan.zhihu.com/p/149228460

MySQL lock series (II) – Lock interpretation https://keithlan.github.io/2017/06/05/innodb_locks_show_engine/