MySQL Deadlock Case: Update of Non‑existent Record with X GAP Lock and Insert‑Intention Lock Conflict

Background – Deadlocks are a common and challenging issue for DBAs and developers. The presented case involves a deadlock triggered by updating a non‑existent row, which generates an X GAP lock, followed by an insert that requires an insert‑intention lock, leading to a lock conflict.

Business Logic – The application first attempts an UPDATE; if affected_rows is 0, it performs an INSERT, and if the INSERT fails, it retries the UPDATE. Under concurrent execution, two sessions may both see affected_rows = 0 and then try to INSERT the same record.

Environment – MySQL version 8.0.30, transaction isolation level REPEATABLE‑READ.

create table dl(
  id int auto_increment primary key,
  c1 int not null,
  c2 int not null,
  c3 int not null,
  unique key uc1(c1),
  unique key uc2(c2)
);

insert into dl(c1,c2,c3) values(2,0,2),(5,5,5);

Deadlock Log – The InnoDB status shows two transactions (sess1 and sess2) each holding X locks on one unique index and waiting for a GAP lock on the other index, resulting in a circular wait.

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1422661, ACTIVE 51 sec inserting
... (log details omitted for brevity) ...
*** (2) TRANSACTION:
TRANSACTION 1422664, ACTIVE 45 sec inserting
... (log details omitted for brevity) ...
*** WE ROLL BACK TRANSACTION (2)

Deadlock Analysis

sess1 updates first, gets an X GAP lock on the range (2,5) of column c2.

sess2 updates next, also gets an X GAP lock on the range (0,3) of c2. GAP‑X locks are compatible, so no wait yet.

sess1 attempts the INSERT; it needs an insert‑intention lock ( lock_mode X locks gap before rec insert intention waiting) which conflicts with sess2’s GAP‑X lock, so sess1 waits.

sess2 then attempts its INSERT and needs the same type of lock that sess1 holds, creating a circular wait and a deadlock.

How to Resolve – Replace the separate UPDATE + INSERT pattern with INSERT ... ON DUPLICATE KEY UPDATE so that a single statement acquires a consistent lock set, eliminating the lock order inversion.

Conclusion – Deadlocks arise when transactions lock rows in different orders. Analyzing the lock trace and understanding InnoDB’s next‑key, record, and GAP lock behavior helps identify and fix such issues. The article also reminds readers of basic InnoDB locking principles:

Principle 1: The basic lock unit is a next‑key lock.
Principle 2: Only objects accessed during the search are locked.
Optimization 1: Equality search on a unique index degrades next‑key lock to a row lock.
Optimization 2: Right‑most traversal on a unique index with a non‑matching last value degrades to a GAP lock.
Bug note: Range queries on a unique index may lock the first non‑matching value.

Under READ‑COMMITTED isolation, row locks acquired during a statement are released immediately after the statement finishes, without waiting for transaction commit.