Why Do MySQL Deadlocks Occur and How to Prevent Them?

1 Background

Online consumption services frequently reported SQL deadlock exceptions. Although automatic rollback and retry can ensure eventual correctness, unchecked deadlocks generate massive alarm logs, hide critical issues, and reduce throughput. By analyzing service logs, MySQL deadlock logs, and InnoDB lock mechanisms under REPEATABLE READ, the real problem was identified and the business logic was optimized.

2 Knowledge Reserve

Before presenting the case and solution, essential concepts are introduced.

Transaction

Transactions provide ACID guarantees, simplifying complex business logic. InnoDB implements these guarantees via logs.

ACID Implementation

Atomicity : All statements succeed or all fail, realized with UndoLog.

Consistency : Application ensures state transitions; not the database’s responsibility.

Isolation : Controls visibility of concurrent transactions, implemented with locks and MVCC.

Durability : Once committed, data is persisted, realized with RedoLog.

UndoLog stores previous row versions for rollback and MVCC; RedoLog records changes sequentially to allow fast recovery after a crash.

3 Locks

InnoDB lock mechanisms are examined, focusing on how different statements acquire locks under READ COMMITTED (RC) and REPEATABLE READ (RR) isolation levels.

Update & Delete Locking

Examples of lock behavior for various index types:

Clustered index (hit)

UPDATE students SET score = 100 WHERE id = 15;

Clustered index (miss)

UPDATE students SET score = 100 WHERE id = 16;

RC does not lock; RR adds GAP locks around the range.

Secondary unique index (hit)

UPDATE students SET score = 100 WHERE no = 'S0003';

Both RC and RR lock the secondary and clustered indexes.

Secondary unique index (miss)

UPDATE students SET score = 100 WHERE no = 'S0008';

RC does not lock; RR adds GAP lock on the secondary index.

Secondary non‑unique index (hit)

UPDATE students SET score = 100 WHERE name = 'Tom';

RC locks both secondary and clustered indexes; RR adds GAP lock on the clustered index.

Secondary non‑unique index (miss)

UPDATE students SET score = 100 WHERE name = 'John';

RC does not lock; RR adds GAP lock on the secondary index.

INSERT Locking

GAP locks prevent INSERT when they exist.

Unique‑key conflicts also block INSERT.

4 Online CASE

Analysis of service logs revealed deadlocks caused by two transactions performing delete‑then‑insert on the same table in interleaved order.

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

MySQL deadlock logs showed each transaction waiting for a gap lock held by the other, leading to a circular wait.

5 Solution Overview

Lower isolation level to RC to avoid gap locks (accepting non‑repeatable reads).

Configure InnoDB to disable gap locks under RR (may cause phantom reads).

Check existence before delete; only delete when a row exists.

Additional options:

Select FOR UPDATE (reduces concurrency).

Add a unique index and handle duplicate‑key errors.

Allow occasional duplicate data when business permits.

Avoid large transactions as they increase deadlock probability.

6 Detailed Scenario 3

Re‑creating the online scenario: rows 1 and 6 exist; two transactions try to delete‑then‑insert non‑existent rows 2 and 5 concurrently.

When existence checks are added before delete, both transactions acquire non‑overlapping gap locks and proceed without deadlock.

7 Deadlock Cases Sharing

Case 1

Case 2

Both transactions insert into a gap locked range (20, 30) causing a deadlock.

Case 3

Different lock acquisition orders (id 20→30 vs 30→20) lead to deadlock.

Case 4

REPLACE INTO and INSERT ON DUPLICATE UPDATE can be broken into multiple steps that acquire GAP locks, causing deadlocks under high concurrency (e.g., MySQL 5.7).