Why Identical DELETE Statements Can Deadlock in MySQL and How to Fix It

Problem Background

Two identical DELETE statements on the same row of a table with a unique index caused a deadlock in MySQL, leaving developers confused about why the same operation could block itself.

MySQL Lock Review

The analysis uses MySQL 5.7.21 with InnoDB and the REPEATABLE READ isolation level. To view lock information, enable InnoDB status monitoring:

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

And retrieve detailed engine status:

SHOW ENGINE INNODB STATUS;

DELETE Process

During a DELETE, MySQL first sets the delete_flag (the “delete mark”) on the record header. After the transaction commits, the purge thread removes the record from the normal chain and adds it to the garbage chain.

Locking rules differ by record state:

Normal record: record lock.

Delete‑marked record (delete_flag=1, not yet purged): temporary key lock.

Deleted record (purged): gap lock.

Root Cause Analysis

The deadlock occurs on table t_order_extra_item_15 with a composite unique index (order_id, extra_key). A simplified lock log shows:

Transaction A holds a record lock and waits for another record lock; Transaction B holds a record lock and waits for a temporary key lock, creating a circular wait.

The analysis identifies a third transaction (C) that also attempts the same DELETE, completing the deadlock cycle.

On‑site Reproduction

A test table t_lock was created:

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

Running three concurrent MySQL sessions each executing DELETE FROM t_lock WHERE uniq = 5; did not reproduce the deadlock because MySQL automatically upgrades the lock to a temporary key lock after the delete mark.

A Java program was used to simulate concurrency:

public class Main {</code>
<code>    private static final String URL = "jdbc:mysql://localhost:3306/db_test";</code>
<code>    private static final String USER = "root";</code>
<code>    private static final String PASSWORD = "123456";</code>
<code>    private static final String SQL = "DELETE FROM t_lock WHERE uniq = 5;";</code>
<code>    public static void main(String[] args) {</code>
<code>        for (int i = 0; i < 3; i++) { new Thread(Main::executeSQL).start(); }</code>
<code>    }</code>
<code>    public static void executeSQL() {</code>
<code>        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);</code>
<code>             Statement statement = connection.createStatement()) {</code>
<code>            connection.setAutoCommit(false);</code>
<code>            int rows = statement.executeUpdate(SQL);</code>
<code>            Thread.sleep(5000); // observe lock info</code>
<code>            connection.commit();</code>
<code>        } catch (Exception e) { e.printStackTrace(); }</code>
<code>    }</code>
<code>}

The program produced a deadlock identical to the production logs, confirming the analysis.

Problem Thinking

Can SELECT FOR UPDATE avoid the deadlock? No, because it follows the same lock rules based on record state.

Is the issue limited to unique indexes? Yes; primary and ordinary indexes behave differently.

Why does a record lock followed by a temporary key lock require waiting? The lock manager gives priority to the transaction that first requested the lock on that row, preventing lock starvation.

Do newer MySQL versions still exhibit this deadlock? In MySQL 8.x, the purge thread acquires a gap lock instead of a temporary key lock after a delete mark, effectively avoiding the deadlock.

Post‑mortem Summary

Key takeaways and mitigation options:

Upgrade MySQL to a newer version that changes the lock behavior.

Switch isolation level to READ COMMITTED (RC) – resolves the deadlock but may introduce dirty reads and phantom reads.

Do nothing – the issue does not affect data consistency but may cause service instability.

Introduce a distributed lock – low development cost and controllable impact; already 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