Understanding Next-Key Lock Deadlocks in MySQL: Analysis and Solutions

In the previous article we introduced the concept of Next‑Key Lock. This continuation explores a typical business scenario—"if exists then update, otherwise insert"—and shows how it can lead to deadlocks under the repeatable‑read isolation level.

Scenario reproduction

Two common solutions for ensuring uniqueness in concurrent environments are:

Using a distributed lock to synchronize the whole business flow.

Controlling concurrency at the database row‑lock level.

We focus on the second approach and analyze why it may cause deadlocks.

Pseudo‑code example

// Query configuration by businessCode
ConfigQueryDTO query = new ConfigQueryDTO();
query.setBusinessCode(businessCode);

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

// If configuration does not exist, insert; otherwise update
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);
}

The above pseudo‑code is translated into the following SQL statements:

-- 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;

Hands‑on experiment

We create a simple table with a unique index on business_code and simulate two concurrent sessions that both try to process business_code = 3 :

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);

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

-- Session B
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;
COMMIT;

We expected Session B to insert quickly while Session A would fail due to a primary‑key conflict, but the actual result was that Session B became blocked and Session A encountered a deadlock.

Analysis of the deadlock

MySQL’s lock unit is the Next‑Key Lock (row lock + gap lock). When SELECT ... FOR UPDATE finds no matching row, it acquires a gap lock on the interval surrounding the missing key. Both sessions acquire a gap lock on the interval (1, 5), which does not conflict with each other, allowing both SELECTs to succeed.

When Session B attempts the INSERT , it conflicts with Session A’s gap lock, causing it to wait. Conversely, Session A’s subsequent INSERT also conflicts with Session B’s gap lock, leading to a circular wait and a deadlock. InnoDB aborts Session A to break the cycle.

To avoid this, we can postpone the exclusive lock until after the existence check, or directly lock the row by primary key, eliminating the gap‑lock component.

Revised approach

// Query by businessCode
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 exclusive lock on the primary key only
    ConfigQueryDTO queryLock = new ConfigQueryDTO();
    queryLock.setId(config.getId);
    config = configDao.selectForUpdate(queryLock);
    configData.setConfigValue(config.getConfigValue + 1);
    configDao.update(configData);
}

Further experiments replace the unique index with a non‑unique index and show that even three SQL statements can produce a deadlock because the gap lock still spans a range that interferes with the other session’s operations.

Key take‑aways

Deadlocks often arise from UPDATE or DELETE statements that use non‑unique index conditions, which generate wide gap locks.

Prefer accessing rows by primary key when performing updates or deletes; this narrows the lock scope and prevents unnecessary gap locks.

Understanding the composition of Next‑Key Locks (gap lock + row lock) is essential for diagnosing and preventing lock‑related issues.

Even simple business logic can trigger deadlocks; thorough lock‑range analysis is required.

By applying these principles—postponing locks, using primary‑key based updates, and being aware of gap‑lock side effects—developers can significantly reduce the likelihood of MySQL deadlocks in high‑concurrency environments.