MySQL REPLACE INTO Deadlock: Case Study and Analysis

This article presents a detailed analysis of deadlock problems that can occur when using the REPLACE INTO statement in MySQL InnoDB tables. It is part of a series aimed at developers and DBAs who encounter lock conflicts during production.

1. Introduction

Deadlocks are a challenging technical issue that many DBAs and developers face. The author introduces a real‑world deadlock case that arose from a REPLACE INTO operation.

2. Background Knowledge

The official MySQL documentation states that REPLACE behaves like an INSERT when there is no unique‑key conflict, but acquires an exclusive next‑key lock when a conflict exists.

"REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next‑key lock is placed on the row to be replaced."

Example table definition used for the analysis:

create table t1(
  a int auto_increment primary key,
  b int,
  c int,
  unique key (b)
);

replace into t1(b,c) values (2,3);

3. Step‑by‑Step Execution Logic

Step 1 – Normal Insert Logic : The clustered index (auto‑increment column a ) is inserted first, followed by the secondary unique index b . For a normal INSERT a shared lock (LOCK_S) is taken on duplicate‑key checks, while REPLACE INTO or INSERT … ON DUPLICATE KEY UPDATE acquire an exclusive lock (LOCK_X).

Step 2 – Error Handling : When a duplicate key is detected, the previously inserted clustered index row is rolled back.

Step 3 – Conversion Operation : After the InnoDB layer reports a duplicate‑key error, the server scans the conflicting unique index and locks both the conflicting secondary index record and the corresponding clustered index record.

If the conflicting unique index is the last unique index, has no foreign‑key references and no delete trigger, the engine resolves the conflict with an UPDATE ROW; otherwise it uses DELETE ROW + INSERT ROW.

Step 4 – Record Update : Because the primary key a changes, the engine performs a delete‑plus‑insert on the clustered index and a similar delete‑plus‑insert on the secondary unique index.

4. Case Study

Test environment: InnoDB tables with REPEATABLE READ isolation.

create table ix(
  id int not null auto_increment,
  a int not null,
  b int not null,
  primary key(id),
  idxa(a)
) engine=innodb default charset=utf8;

insert into ix(a,b) values (1,1),(5,10),(15,12);

Three transactions are executed sequentially:

Transaction 1: replace into ix(a,b) values(5,8) – detects duplicate key on a=5 , acquires a next‑key lock and a GAP lock.

Transaction 2: replace into ix(a,b) values(8,10) – needs to insert a new a=8 row, but the GAP lock held by Transaction 1 on the range [5‑15] blocks it, causing it to wait.

Transaction 3: replace into ix(a,b) values(9,12) – also falls into the same GAP lock range and ends up waiting for both previous transactions.

The InnoDB engine status logs (excerpt) show the lock structures, transaction IDs, and the exact point where each transaction is waiting:

---TRANSACTION 1872, ACTIVE 46 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 1156, OS thread handle 0672, query id 114 localhost msandbox

Analysis of the logs reveals:

Transaction 2 holds an IX lock, a row lock on a=5 , and two GAP locks for the ranges 1‑5 and 5‑15.

Transaction 1 waits for the insert intention lock on a=8 because it conflicts with the GAP lock held by Transaction 2.

Transaction 3 also waits for the same GAP lock, creating a circular wait (deadlock).

InnoDB chooses to roll back Transaction 1 to break the deadlock.

5. Summary

The root cause is the exclusive GAP lock created by REPLACE INTO when a duplicate key is found. To avoid such deadlocks, the author recommends either:

Replace REPLACE INTO with a separate SELECT ‑check followed by INSERT , or

Reduce concurrency for the affected statements (e.g., serialize them) if occasional deadlocks are acceptable.

Further reading and reference links are provided for deeper understanding of InnoDB lock mechanisms and additional deadlock case studies.