Why Do MySQL Inserts Trigger Deadlocks? A Deep Dive into InnoDB Lock Mechanics

Scenario

Application uses a two‑step upsert: UPDATE then, if affected_rows is 0, INSERT . If the INSERT fails it retries the UPDATE. Under high concurrency this pattern can cause deadlocks.

Environment

MySQL 5.6.24, 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);

Deadlock Log

2018-03-27 17:59:23.0x7f75bf39d700
*** (1) TRANSACTION:
TRANSACTION 1863, ACTIVE 76 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct, heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
insert into ty (c1,c2,c3) values(3,4,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB5000
mysql tables in use 1, locked 1
3 lock struct, heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update
insert into ty (c1,c2,c3) values(3,4,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec
*** (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 1864 lock S waiting
*** WE ROLL BACK TRANSACTION (2)

Lock acquisition sequence for INSERT

Phase 1 – Uniqueness check : InnoDB acquires LOCK_S + LOCK_ORDINARY on the unique index.

Phase 2 – Gap reservation : After the uniqueness lock succeeds, InnoDB obtains LOCK_INSERT_INTENTION to reserve the gap for the new row.

When the row is inserted, InnoDB holds LOCK_X + LOCK_REC_NOT_GAP on the record.

If a unique‑key conflict occurs, InnoDB adds an S next‑key lock on the conflicting index entry, even under REPEATABLE READ, which can block other sessions.

Lock compatibility matrix

INSERT‑INSERT do not conflict. GAP and Next‑Key locks block INSERT. GAP and Record locks are compatible. Record‑Record and Record‑Next‑Key locks conflict. Existing INSERT locks do not block new locks. Existing GAP locks block INSERT_INTENTION locks.

Step‑by‑step deadlock formation

Session 1 runs UPDATE; affected rows = 0 → InnoDB acquires a GAP lock on the interval (3,6) in index uc1.

Session 2 runs the same UPDATE and acquires an identical GAP lock; GAP‑GAP locks are compatible.

Session 1 proceeds to INSERT (c1=3,c2=4,c3=2). It requests an INSERT‑INTENTION lock on index uc2. The GAP lock held by Session 2 conflicts with this request, so Session 1 waits.

Session 2 reaches its INSERT and requests its own INSERT‑INTENTION lock, which conflicts with Session 1’s GAP lock. Both sessions now wait for each other’s GAP lock → circular wait → deadlock.

index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting

Solution

The two‑step pattern is prone to deadlocks. Replace it with a single idempotent upsert:

INSERT INTO ty (c1,c2,c3) VALUES (3,4,2)
ON DUPLICATE KEY UPDATE c3 = VALUES(c3);

This statement either inserts a new row or updates the existing one, eliminating the need for a separate UPDATE and avoiding the GAP‑INSERT‑INTENTION conflict.

Key takeaways

Understanding InnoDB lock types (S, X, GAP, INSERT‑INTENTION, REC_NOT_GAP) and their compatibility is essential for diagnosing deadlocks.

Deadlock logs alone may not reveal the full SQL execution path; mapping application logic to SQL statements is necessary.

Using idempotent upsert patterns (INSERT … ON DUPLICATE KEY UPDATE) reduces lock contention and improves concurrency.