Why Identical DELETE Statements Can Deadlock in MySQL: A Deep Dive

1 Problem Background

Even seasoned developers can be puzzled when two identical DELETE statements cause a deadlock. The article starts with a humorous remark and then dives into the root cause.

2 MySQL Lock Review

A quick recap of MySQL lock categories is provided. The discussion is based on MySQL 5.7.21 with InnoDB storage engine and REPEATABLE READ isolation level.

This article uses MySQL 5.7.21, InnoDB, and REPEATABLE READ.

Enable InnoDB status monitoring to view lock information:

<code>SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;</code>

Show detailed engine status:

<code>SHOW ENGINE INNODB STATUS;</code>

3 DELETE Process

MySQL stores data in pages containing a normal‑record list and a garbage list. Each record has a header with a deleted_flag .

During a DELETE , the delete mark is set (the flag becomes 1).

After transaction commit, the purge thread physically removes the record from the normal list and adds it to the garbage list.

Locking rules for different record states:

Normal record: record lock

Delete mark: next‑key lock

Deleted record: gap lock

4 Cause Analysis

The deadlock occurs on table t_order_extra_item_15 which has a composite unique index (order_id, extra_key) . The simplified lock table shows the interaction between two transactions (A and B):

Transaction A

Transaction B

Statement

delete from t_order_extra_item_15 where (order_id=xxx and extra_key=xxx)

delete from t_order_extra_item_15 where (order_id=xxx and extra_key=xxx)

Held lock

record lock (X locks rec but not gap)

Waiting lock

record lock (X locks rec but not gap waiting)

next‑key lock (X waiting)

Transaction A waits for a record lock held by B, while B waits for a next‑key lock blocked by A, forming a circular wait.

Transaction B also needs a next‑key lock because the row it tries to delete has already been marked for deletion by another transaction, requiring it to reacquire the lock after the delete‑mark phase.

5 Reproducing the Issue

A test table is created to simulate the problem:

<code>CREATE TABLE `t_lock` (
  `id` int NOT NULL,
  `uniq` int NOT NULL,
  `idx` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`uniq`) USING BTREE,
  KEY `idx` (`idx`)
);
INSERT INTO t_lock VALUES (1,1,1), (5,5,5), (10,10,10);
</code>

Three MySQL client sessions each run:

<code>DELETE FROM t_lock WHERE uniq = 5;</code>

Even though the deadlock does not appear with manual execution, a Java program that launches three concurrent threads reproduces the deadlock:

<code>public class Main {
    private static final String URL = "jdbc:mysql://localhost:3306/db_test";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private static final String SQL = "DELETE FROM t_lock WHERE uniq = 5;";

    public static void main(String[] args) {
        for (int i = 0; i < 3; i++) {
            new Thread(Main::executeSQL).start();
        }
    }

    public static void executeSQL() {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement statement = connection.createStatement()) {
            System.out.println(LocalTime.now() + ":" + Thread.currentThread().getName());
            connection.setAutoCommit(false);
            int rows = statement.executeUpdate(SQL);
            Thread.sleep(5000);
            connection.commit();
            System.out.println(LocalTime.now() + ":" + Thread.currentThread().getName() + ":" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
</code>

The program throws a deadlock exception, matching the logs observed in production.

6 Further Thoughts

6.1 Can SELECT FOR UPDATE Avoid It?

No. SELECT FOR UPDATE uses the same locking logic as DELETE , so it cannot prevent the deadlock.

6.2 Is This Issue Limited to Unique Indexes?

Only unique indexes exhibit this behavior; primary and ordinary indexes do not. A comparison table shows lock types for different index kinds.

Primary Index

Unique Index

Ordinary Index

Normal record

record lock

record lock

next‑key lock

Delete mark

record lock

next‑key lock

next‑key lock

Deleted record

gap lock

gap lock

gap lock

6.3 Why Does a Transaction Wait for a Next‑Key Lock After Holding a Record Lock?

Because another transaction is already waiting for the same row; MySQL grants the lock to the earlier waiter to avoid starvation.

6.4 Do Newer MySQL Versions Still Have This DELETE Deadlock?

In MySQL 8.x the behavior changes: when a transaction already holds a record lock on a row marked for deletion, it acquires a gap lock instead of a next‑key lock, effectively preventing the deadlock.

7 Post‑mortem Summary

Possible solutions include:

Upgrade MySQL to a newer version (may involve risk and effort).

Change isolation level to READ COMMITTED (eliminates the deadlock but introduces dirty reads and phantom reads).

Ignore the issue (does not affect consistency but may cause service anomalies).

Introduce a distributed lock (low development cost and controllable impact; already adopted).

Continuous learning and respect for technology are essential.

Stay hungry. Stay foolish.

References:

InnoDB Locking: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

An InnoDB Deadlock Example: https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html