Why Do MySQL Inserts Cause Deadlocks? A Deep Dive into a Real-World Case

1. Introduction

Deadlock is an interesting and challenging technical problem that most DBAs and developers encounter. This article is the first in a series of case analyses on deadlocks, aiming to help readers understand the root causes and mitigation strategies.

2. Case Analysis

2.1 Business Scenario

Three concurrent sessions initialize data. Session 1 inserts successfully and commits. Sessions 2 and 3 encounter a unique‑key conflict on insert, fall back to update, leading to a deadlock.

2.2 Environment Setup

MySQL 5.6.24 with transaction isolation level REPEATABLE READ.

create table ty (
  id int not null primary key auto_increment,
  c1 int not null default 0,
  c2 int not null default 0,
  c3 int not null default 0,
  unique key uc1(c1),
  unique key uc2(c2)
) engine=innodb;

insert into ty(c1,c2,c3) values
(1,3,4),
(6,6,10),
(9,9,14);

2.3 Test Case

To simplify log analysis, the three sessions insert different values for column c3 (1, 2, 3) although in production they would be identical.

2.4 Deadlock Log

2018-03-28 10:04:52 0x7f75bf2d9700
*** (1) TRANSACTION:
TRANSACTION 1870, ACTIVE 76 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct, heap size 1136, 2 row lock(s)
MySQL thread id 399265, OS thread handle 12, query id 9 localhost root updating
update ty set c3=5 where c1=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1870 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1871, ACTIVE 32 sec starting index read, thread declared inside InnoDB5000
mysql tables in use 1, locked 1
3 lock struct, heap size 1136, 2 row lock(s)
MySQL thread id 399937, OS thread handle 16, query id 3 localhost root updating
update ty set c3=5 where c1=4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

2.5 Analyze Deadlock Log

Transaction T2 (sess1) inserts successfully and holds a row lock on c1=4. Transactions T3 and T4 (sess2, sess3) encounter unique‑key conflicts, request S next‑key locks on the same index, and wait. After sess1 commits, both sess2 and sess3 acquire the S lock. When they each try to update c1=4, they need an X lock, which conflicts with the other's S lock, creating a circular wait and causing the deadlock.

2.6 Solution

The deadlock can be avoided by using INSERT ... ON DUPLICATE KEY UPDATE, which combines insert and update in a single atomic statement, preventing the lock‑order inversion that leads to the deadlock.

3. Summary

The root cause of the deadlock is inconsistent lock acquisition order across transactions. When designing high‑concurrency business logic, developers should ensure a consistent locking strategy and be aware that INSERT acquires locks differently from UPDATE, often requiring careful testing.