Analysis of MySQL DELETE Deadlock and Locking Mechanisms

1 Problem Background

The author encountered a deadlock caused by two identical DELETE statements on the same row and wonders why this happens.

2 MySQL Lock Review

The discussion is based on MySQL 5.7.21 with the InnoDB storage engine and the REPEATABLE READ isolation level.

SET GLOBAL innodb_status_output=ON;

SET GLOBAL innodb_status_output_locks=ON;

SHOW ENGINE INNODB STATUS;

3 DELETE Process

MySQL stores data in pages that contain a normal‑record list and a garbage list. During a DELETE , the record header’s delete_flag is set (the “delete mark”). After the transaction commits, the purge thread removes the record from the normal list and adds it to the garbage list.

Normal record : record lock.

Delete mark : next‑key lock (to prevent conflicts before purge).

Deleted record : gap lock (allows new inserts at the index position).

4 Cause Analysis

The table t_order_extra_item_15 has a composite unique index (order_id, extra_key) . A simplified deadlock log shows two transactions (A and B) executing the same DELETE :

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’s earlier request, forming a circular wait and causing a deadlock.

The analysis introduces a third transaction C that initially performs the DELETE , allowing the observed lock states.

5 Reproduction

A test table t_lock is created and populated:

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);
INSERT INTO t_lock VALUES (5,5,5);
INSERT INTO t_lock VALUES (10,10,10);

Three MySQL client sessions each run DELETE FROM t_lock WHERE uniq = 5; but the deadlock is not reproduced because the lock acquisition is atomic.

A Java program is used to force concurrency:

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(); // deadlock stack trace
        }
    }
}

The program produces a deadlock that matches the earlier analysis.

6 Further Questions

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 the problem limited to unique indexes?

Yes. Only unique indexes exhibit this behavior; primary and ordinary indexes do not. A comparison table shows lock types for normal records, delete‑mark records, and deleted records across different index types.

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 lock; InnoDB gives priority to the earlier waiter to avoid lock starvation.

6.4 Do newer MySQL versions still have this DELETE deadlock?

In MySQL 8.x the behavior is improved: when a transaction holds a record lock on a delete‑marked row, it acquires a gap lock instead of a next‑key lock, which prevents the deadlock.

7 Post‑mortem Summary

Possible solutions include:

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

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

Ignore the issue (does not affect consistency but can cause service instability).

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

The author emphasizes continuous learning and humility when facing such technical challenges.

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