Understanding Next-Key Lock Deadlocks in MySQL and How to Avoid Them

Introduction

In the previous article we introduced the concept of Next-Key Lock and its role in MySQL deadlocks. This article continues by analyzing a typical business scenario that can lead to deadlocks, focusing on the lock acquisition process of SELECT ... FOR UPDATE , INSERT and UPDATE statements under the repeatable‑read isolation level.

Scenario Reproduction

We consider the common "insert‑or‑update" pattern where a unique business_code must be maintained. Two usual solutions are:

Control the whole business flow with a distributed lock.

Control at the database row‑level using locks.

The article focuses on the second approach and shows how it can unintentionally cause deadlocks.

Pseudocode Example

// Query whether a configuration for the given businessCode exists
  ConfigQueryDTO query = new ConfigQueryDTO();
  query.setBusinessCode(businessCode);

  // Acquire exclusive row lock
  Config config = configDao.selectForUpdate(query);

  // Handle the case where the configuration is null
  ConfigDO configData = new ConfigDO();
  configData.setBusinessCode(businessCode);
  if (config == null) {
      configData.setConfigValue(initData);
      configDao.insert(configData);
  } else {
      configData.setConfigValue(config.getConfigValue + 1);
      configDao.update(configData);
  }

SQL Translation

-- When the row does not exist
BEGIN;
select * from config where business_code = businessCode for update;
insert into config (business_code,conf_value) values (businessCode,initData);
COMMIT;

-- When the row exists
BEGIN;
select * from config where business_code = businessCode for update;
update config set conf_value = confValue where business_code = businessCode;
COMMIT;

Step‑by‑Step Scenario

Use SELECT ... FOR UPDATE to check existence; if the row is absent, a gap lock is placed on the interval surrounding the missing key.

Execute INSERT ; the unique index causes a primary‑key conflict if another session inserts the same key.

Execute UPDATE ; the exclusive row lock guarantees that only one session updates at a time.

Hands‑On Experiment

Create a simple table:

CREATE TABLE `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_code` int(11),
  `conf_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_business_code` (`business_code`)
) ENGINE=InnoDB;

insert into config values(0,1,1),(2,5,1),(3,10,1);

Simulate two concurrent sessions handling business_code = 3 :

-- SessionA
BEGIN;
SELECT * FROM config WHERE business_code = 3 FOR UPDATE;
SELECT SLEEP(5) FROM config LIMIT 1; -- simulate delay
INSERT INTO config (business_code,conf_value) VALUES (3,1);
COMMIT;

-- SessionB
BEGIN;
SELECT * FROM config WHERE business_code = 3 FOR UPDATE;
INSERT INTO config (business_code,conf_value) VALUES (3,1);
SELECT SLEEP(10) FROM config LIMIT 1; -- simulate delay
COMMIT;

Expected outcome: SessionB should insert quickly while SessionA fails due to a primary‑key conflict. In practice, SessionB becomes blocked and SessionA encounters a deadlock.

Analysis of the Deadlock

The discrepancy arises from the way MySQL implements Next-Key Lock . The lock is actually a combination of a gap lock and a row lock on the index. When SELECT ... FOR UPDATE finds no matching row, it acquires a gap lock on the interval (1,5) (assuming existing keys 1 and 5). Both sessions acquire compatible gap locks, but when they later try to INSERT , each needs to insert into the same gap, causing a conflict and a circular wait that MySQL detects as a deadlock.

Key Takeaways

Even a SELECT ... FOR UPDATE on a non‑existent row creates a gap lock that can block concurrent inserts.

Deadlocks can occur with as few as three SQL statements when gap locks and row locks intersect.

To avoid such deadlocks, postpone the exclusive lock until after the existence check, or operate directly on the primary key instead of a non‑unique index.

Improved Approach

// Query by businessCode first (no lock)
ConfigQueryDTO query = new ConfigQueryDTO();
query.setBusinessCode(businessCode);
Config config = configDao.select(query);

ConfigDO configData = new ConfigDO();
configData.setBusinessCode(businessCode);
if (config == null) {
    configData.setConfigValue(initData);
    configDao.insert(configData);
} else {
    // Acquire lock only on the primary key
    ConfigQueryDTO lockQuery = new ConfigQueryDTO();
    lockQuery.setId(config.getId());
    Config lockedConfig = configDao.selectForUpdate(lockQuery);
    configData.setConfigValue(lockedConfig.getConfigValue + 1);
    configDao.update(configData);
}

Further Scenario Upgrade

Changing the unique index on business_code to a regular index reproduces a similar deadlock with only three statements. The analysis shows that the gap lock on the non‑unique index still interferes with the subsequent INSERT or UPDATE , confirming that the root cause is the lock range, not the number of statements.

Conclusion

When troubleshooting MySQL deadlocks, start from the business logic, drill down to the SQL execution, and examine the exact lock ranges (gap vs. row). Most deadlocks stem from UPDATE or DELETE statements on non‑unique indexes that generate wide gap locks. Using primary‑key based updates or moving the lock acquisition after the existence check dramatically reduces lock contention and eliminates many classic deadlock scenarios.