Analyzing INSERT/REPLACE‑Induced Deadlocks in MySQL InnoDB and Prevention Strategies

The author, a DBA and database‑technology enthusiast, presents a detailed analysis of MySQL InnoDB deadlocks triggered by INSERT and its variants ( REPLACE , INSERT ON DUPLICATE KEY UPDATE ) and discusses how to prevent them.

1. Preface

The article analyzes several deadlock scenarios caused by INSERT and its variants, showing how different lock types interact.

Scenario 1: INSERT unique‑key conflict

Scenario 2/3: REPLACE INTO unique‑key conflict (from production)

Scenario 4: INSERT primary‑key conflict (official example)

Understanding the locking behavior of INSERT helps grasp why deadlocks occur.

2. Brief Review of InnoDB Row Locks

Record Lock (RECORD LOCK)

Locks the index record itself.

Gap Lock (GAP LOCK)

Locks the gap between index records; used in REPEATABLE READ to prevent phantom reads (also appears in READ COMMITTED).

Next‑Key Lock

Combination of RECORD LOCK and GAP LOCK.

Insert Intention Lock

A special GAP lock created when an INSERT is blocked by a GAP lock on the next record.

Implicit Lock

Newly inserted rows have an implicit lock via the transaction ID; other transactions must first create a lock structure before waiting.

The key to deadlocks is the GAP lock.

INSERT Lock Types

When blocked by a GAP lock, an INSERT intention lock is generated.

On duplicate‑key conflict: Primary‑key conflict creates an S‑type record lock (RR and RC levels, still requests a GAP lock during INSERT). Unique‑key conflict creates an S‑type NEXT‑KEY lock (RR and RC levels).

Note: A normal INSERT does not generate a lock structure.

For INSERT … ON DUPLICATE KEY UPDATE and REPLACE , the lock ranges differ slightly.

Lock Type Differences

When a duplicate‑key conflict occurs, INSERT … ON DUPLICATE KEY UPDATE and REPLACE acquire X‑type record locks for primary‑key conflicts and X‑type NEXT‑KEY locks for unique‑key conflicts.

Lock Range Differences

INSERT and INSERT … ON DUPLICATE KEY UPDATE add NEXT‑KEY locks on the inserted row and the UPDATE row.

REPLACE adds NEXT‑KEY locks on the replaced row and its next row.

The official documentation mentions only the REPLACE row, but testing shows the next row also receives a NEXT‑KEY lock.

Scenario Analyses

Scenario 1 – INSERT Unique‑Key Conflict

Two sessions insert rows with the same unique key value (a=35). Session 2 is blocked by a GAP lock, generates a S‑type NEXT‑KEY lock, and waits for Session 1’s record lock, forming a circular wait.

Deadlock avoidance suggestions:

Insert rows in ascending primary/unique‑key order within a transaction.

Insert only one row per transaction and commit promptly.

Scenario 2 – REPLACE Unique‑Key Conflict

Three sessions execute REPLACE INTO with overlapping unique keys. REPLACE acquires X‑type NEXT‑KEY locks on the target row and its successor, leading to a deadlock similar to Scenario 1.

Recommendation: Prefer INSERT … ON DUPLICATE KEY UPDATE over REPLACE to reduce lock contention.

Scenario 3 – REPLACE with SELECT … FOR UPDATE

Session 1 holds a record lock via SELECT … FOR UPDATE ; Sessions 2 and 3 issue REPLACE statements that block each other on GAP locks, reproducing the deadlock pattern of Scenario 2.

Scenario 4 – INSERT Primary‑Key Conflict

Multiple sessions attempt to insert the same primary key (id=6). Record locks are taken, and later INSERT intention locks on the supremum gap cause a deadlock.

Summary and Recommendations

Even under READ‑COMMITTED isolation, high‑concurrency INSERT/REPLACE operations on unique keys can frequently cause deadlocks. Practical advice includes:

Prefer READ‑COMMITTED over REPEATABLE READ to lower deadlock probability.

Use INSERT … ON DUPLICATE KEY UPDATE instead of REPLACE for better safety and performance.

Process rows in a consistent order across concurrent transactions.

Keep transactions short; avoid large or long‑running transactions.

Consider optimistic locking for read‑heavy workloads.

Additional questions such as why GAP locks appear in READ‑COMMITTED or why primary‑key and unique‑key handling differ are left for further exploration.

References

[1] INSERT‑induced deadlocks: https://juejin.cn/post/7052880067298328589

Reading Recommendations

Various links to related technical articles and community resources are provided (omitted for brevity).

About SQLE

SQLE is an open‑source SQL audit tool for MySQL and other databases, supporting multi‑scenario review and native MySQL integration.

GitHub: https://github.com/actiontech/sqle

Documentation: https://actiontech.github.io/sqle-docs/

Website: https://opensource.actionsky.com/sqle/