Why Deleting a Single Row by Unique Index Can Still Cause MySQL Deadlocks

Background

The author, with over ten years of MySQL/InnoDB kernel development experience, previously wrote an extensive analysis of MySQL locking. A reader reported a deadlock case that contradicted the author's understanding, prompting a deeper investigation.

Deadlock Scenario

A table dltask is defined with a primary key id and a unique composite index on columns a, b, c. The isolation level is Repeatable Read (RR), and each transaction executes a single statement: DELETE FROM dltask WHERE a=? AND b=? AND c=?; Despite each transaction deleting at most one row, a deadlock occurs when multiple transactions run concurrently.

How to Read the InnoDB Deadlock Log

The log is generated by lock0lock.c::lock_deadlock_recursive(). It shows two transactions, each holding an intention lock and a row lock, and each waiting for a lock held by the other. Both waiting locks are next‑key X locks on the same unique index page.

Transaction 1 holds an X lock with no gap on a row and waits for a next‑key X lock; Transaction 2 holds a next‑key X lock and waits for the X lock with no gap held by Transaction 1, forming a circular wait.

Delete Locking Logic

Three cases exist for a unique‑index lookup:

Record found and valid → X lock (no‑gap).

Record found but marked deleted → next‑key X lock (locks the row and its preceding gap).

No matching record → gap lock on the first larger record.

In the deadlock, both transactions are waiting on next‑key X locks, which correspond to the second case (deleted‑record handling).

InnoDB Deadlock‑Prevention Strategy

Pages are locked with short‑term S/X RWLocks; row/table locks are longer‑term.

If a transaction holds a row lock, it may wait for a page lock, but a transaction holding a page lock cannot wait for a row lock.

When a row lock request would block, InnoDB releases the page lock, waits for the row lock, then reacquires the page lock and rechecks the record.

This re‑acquisition can cause the circular wait observed.

Root Cause Analysis

Assume three concurrent transactions each try to delete the same row. The first transaction acquires the X lock (no‑gap) on the existing row. The second transaction acquires a next‑key X lock (gap + row) and waits for the first's X lock. The third transaction acquires a next‑key X lock and waits for the second. Because of the lock‑prevention rule, the waiting chain creates a cycle, leading InnoDB to abort the smallest‑weight transaction.

Summary of Preconditions for This Deadlock

DELETE on a unique‑index equality condition.

At least three concurrent delete statements.

All transactions target the same existing row.

Isolation level is Repeatable Read and innodb_locks_unsafe_for_binlog is off.

Storage engine is InnoDB (MyISAM has no row locks).

Understanding InnoDB’s lock modes, the three‑case unique‑index locking strategy, and the deadlock‑prevention mechanism clarifies why such a seemingly impossible deadlock can occur.