Why Do MySQL Transactions Deadlock and How Can You Prevent It?

Background

In an online MetaQ service, frequent MySQL deadlock exceptions were observed. Although automatic rollback and retry kept eventual correctness, the flood of alerts hid critical issues and reduced throughput. By examining service logs, MySQL deadlock logs, and InnoDB lock behavior under REPEATABLE READ, the true problem was identified and the business logic was optimized.

Knowledge Prerequisites

Understanding transactions is essential. A transaction guarantees ACID properties, simplifying complex multi‑SQL operations. The ACID components are:

Atomicity – all statements succeed or all fail, implemented via UndoLog.

Consistency – the system moves from one correct state to another, enforced by application logic.

Isolation – controls visibility of concurrent transactions, implemented with locks and MVCC.

Durability – committed changes survive crashes, implemented via RedoLog. UndoLog stores old row versions for rollback and MVCC. RedoLog provides sequential, high‑performance writes; it buffers changes in the BufferPool and flushes them to disk asynchronously, ensuring durability even after a crash.

Lock Mechanism

InnoDB uses different lock strategies depending on the isolation level. Under REPEATABLE READ (RR), both row locks and gap locks are employed, while READ COMMITTED (RC) avoids gap locks. The following examples illustrate how UPDATE, DELETE and INSERT statements acquire locks on clustered and secondary indexes.

Update & Delete Locking

1) Clustered index hit: UPDATE students SET score = 100 WHERE id = 15; Both RC and RR acquire an exclusive (X) lock on the clustered index.

2) Clustered index miss: UPDATE students SET score = 100 WHERE id = 16; RC does not lock; RR adds a GAP lock around the missing key.

3) Secondary unique index hit: UPDATE students SET score = 100 WHERE no = 'S0003'; Both RC and RR lock the secondary and clustered indexes with X locks.

4) Secondary unique index miss: UPDATE students SET score = 100 WHERE no = 'S0008'; RC does not lock; RR adds a GAP lock on the secondary index.

5) Secondary non‑unique index hit: UPDATE students SET score = 100 WHERE name = 'Tom'; RC locks both secondary and clustered indexes with X locks; RR locks the secondary index with X and adds a GAP lock on the clustered index.

6) Secondary non‑unique index miss: UPDATE students SET score = 100 WHERE name = 'John'; RC does not lock; RR adds a GAP lock on the secondary index.

INSERT statements also acquire intention locks; if a GAP lock already exists on the target range, the INSERT blocks, leading to potential deadlocks.

Online CASE Study

Log analysis revealed two concurrent transactions performing a DELETE (affecting zero rows) followed by an INSERT on the same table, causing a deadlock:

delete from db.table where creativeid=102; -- rows affected: 0

delete from db.table where creativeid=103; -- rows affected: 0

insert into db.table (creativeid) values (102);

insert into db.table (creativeid) values (103);

The MySQL deadlock log showed that each transaction waited for the other's gap lock while trying to acquire an insert intention lock, creating a circular wait.

Solution Design

Several mitigation strategies were considered:

Lower isolation to RC to avoid gap locks (introduces non‑repeatable reads and phantom reads).

Disable gap locks in RR (also introduces phantom reads).

Check existence before DELETE; only delete when the row exists, which eliminates the deadlock pattern.

Accept occasional duplicate rows when strict uniqueness is not required.

Practical solutions implemented:

Use SELECT ... FOR UPDATE (reduces concurrency).

Add a unique index and handle duplicate‑key errors.

Adopt the “check‑then‑delete” approach as the primary fix.

Detailed Walkthrough of the Chosen Solution

Assume a table initially contains rows with IDs 1 and 6. Two transactions attempt to delete non‑existent rows 2 and 5 and then insert them, interleaving their operations.

When both transactions first try to acquire an insert intention lock on the gap (2,5), each blocks on the other's GAP lock, forming a deadlock.

By modifying the logic to perform a SELECT EXISTS(...) before the DELETE, only existing rows are deleted. In the revised scenario, the gap locks no longer overlap, allowing both transactions to acquire their intention locks and complete without deadlock.

Even when only one of the target rows exists, the transaction that finds the row deletes it, while the other proceeds to insert without waiting, again avoiding a circular wait.

Deadlock Scenario Gallery

Case 1

Case 2

Both transactions insert rows 25 and 26 while the table has no rows in the (20,30) range. RR places a GAP lock on that range, and each INSERT needs an intention lock, causing a deadlock.

Case 3

Different lock acquisition orders (id 20→30 vs. 30→20) create a circular wait.

Case 4

Statements like REPLACE INTO or INSERT ... ON DUPLICATE UPDATE are internally split into multiple steps; in MySQL 5.7 they may introduce GAP locks, leading to deadlocks under high concurrency.