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

Problem Background

Two identical DELETE statements on the same row can cause a deadlock in InnoDB.

MySQL Lock Review

Discussion based on MySQL 5.7.21, InnoDB storage engine, REPEATABLE READ isolation.

Enable InnoDB lock monitoring:

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

Show detailed engine status:

SHOW ENGINE INNODB STATUS;

DELETE Process

InnoDB stores data in pages that contain a normal‑record list and a garbage list. Each record has a header with a deleted_flag. During a DELETE, the flag is set ("delete mark"); later the purge thread 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 record : next‑key lock (prevents conflicts before purge).

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

Root Cause Analysis

The deadlock occurs on table t_order_extra_item_15 with a unique index on (order_id, extra_key). Simplified lock information:

Transaction A holds a record lock (X, not gap) and waits for the same record lock.

Transaction B holds a record lock (X, not gap) and waits for a next‑key lock.

The circular wait between the two transactions leads to a deadlock.

Reproducing the Issue

Create a test table and insert sample rows:

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);

Run three concurrent transactions that each execute: DELETE FROM t_lock WHERE uniq = 5; Java program that launches three threads, disables autocommit, executes the DELETE, sleeps 5 seconds, then commits:

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()) {
            connection.setAutoCommit(false);
            int rows = statement.executeUpdate(SQL);
            Thread.sleep(5000);
            connection.commit();
            System.out.println("Rows: " + rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The program throws a deadlock exception, matching the lock sequence described above.

Further Questions

Can SELECT FOR UPDATE avoid the deadlock?

No. SELECT FOR UPDATE uses the same locking logic as DELETE; the lock type depends on the record state, so it does not prevent the deadlock.

Is the problem limited to unique indexes?

Yes. Only unique indexes exhibit this behavior. Primary‑key and ordinary indexes use record or gap locks that do not create the same deadlock pattern. The lock type matrix is:

Normal record : primary‑key – record lock; unique – record lock; ordinary – next‑key lock.

Delete‑mark : primary‑key – record lock; unique – next‑key lock; ordinary – next‑key lock.

Deleted record : all index types – gap lock.

Why does a transaction that already holds a record lock need to wait for a next‑key lock?

Another transaction is already queued for the same next‑key lock. MySQL grants the lock to the earlier waiter after the record lock is released to avoid lock starvation.

Does MySQL 8.x still suffer from this DELETE deadlock?

In MySQL 8.x the behavior changed: when a transaction already holds a record lock on a delete‑mark row, it acquires a gap lock instead of a next‑key lock, effectively avoiding the deadlock.

Mitigation Strategies

Upgrade MySQL version – may require testing and carries upgrade risk.

Change isolation level to READ COMMITTED – resolves the deadlock but introduces dirty‑read and phantom‑read anomalies.

Do nothing – data consistency remains, but occasional deadlocks may occur.

Introduce a distributed lock – relatively low development cost and has been adopted in practice.

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