Why Does Inserting a Record that Triggers a Unique Index Conflict Add a Next‑Key Exclusive Lock on the Primary‑Key Supremum Record?

The article originates from a reader's question about why inserting a row that causes a unique‑index conflict results in a next‑key exclusive lock on the primary‑key supremum record. It reproduces the issue on MySQL 8.0.32, sets up a test table, and demonstrates the lock state after the conflicting INSERT .

1. Preparation

A test table t6 with an auto‑increment primary key and a unique secondary index uniq_i1 is created, and several rows are inserted. The transaction isolation level is set to REPEATABLE-READ in my.cnf .

CREATE TABLE `t6` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2. Problem Reproduction

An INSERT that tries to add a duplicate i1 value is executed inside an explicit transaction. After the statement, the performance_schema.data_locks view shows a next‑key shared lock on the conflicting unique‑index record and a next‑key exclusive lock on the primary‑key supremum record.

BEGIN;
INSERT INTO `t6`(i1) VALUES(1001);
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM `performance_schema`.`data_locks`;

3. Implicit‑Lock Background

When a transaction inserts a row, it may avoid taking a lock on the target position if no other transaction holds a lock there (implicit lock). However, if another transaction later scans the same position (e.g., for UPDATE/DELETE) or attempts a conflicting insert, the engine must create an implicit lock that later becomes explicit.

4. Process Analysis

4.1 Inserting into Primary and Unique Indexes

The core insertion path is row_insert_for_mysql_using_ins_graph() , which builds ins_node_t and que_fork_t objects, converts the MySQL row format, and calls row_ins_step() . The latter invokes row_ins() , which iterates over all indexes and calls row_ins_index_entry_step() for each.

static dberr_t row_insert_for_mysql_using_ins_graph(...){
  row_get_prebuilt_insert_row(prebuilt);
  row_mysql_convert_row_to_innobase(...);
  row_ins_step(thr);
  if (err != DB_SUCCESS) {
    row_mysql_handle_errors(...);
    return err;
  }
}

que_thr_t* row_ins_step(que_thr_t *thr){
  ...
  err = row_ins(node, thr);
  return thr;
}

static dberr_t row_ins(...){
  while (node->index != nullptr) {
    if (node->index->type != DICT_FTS) {
      err = row_ins_index_entry_step(node, thr);
      switch (err) {
        case DB_SUCCESS: break;
        case DB_DUPLICATE_KEY:
          thr_get_trx(thr)->error_state = DB_DUPLICATE_KEY;
          thr_get_trx(thr)->error_index = node->index;
          [[fallthrough]];
        default: return err;
      }
    }
    node->index = node->index->next();
    node->entry = UT_LIST_GET_NEXT(tuple_list, node->entry);
  }
  return DB_SUCCESS;
}

During the second iteration, the unique index uniq_i1 detects a duplicate key and returns DB_DUPLICATE_KEY , which propagates back to the caller.

4.2 Locking the Conflicting Unique‑Index Record

The function row_ins_sec_index_entry_low() locates the target position. If a matching record exists, it calls row_ins_scan_sec_index_for_duplicate() . Because the statement does not contain conflict‑resolution clauses ( REPLACE , INSERT … ON DUPLICATE KEY UPDATE ), allow_duplicates is false, and the engine acquires a shared lock ( LOCK_S ) with next‑key mode on the conflicting record.

static dberr_t row_ins_scan_sec_index_for_duplicate(...){
  if (allow_duplicates) {
    err = row_ins_set_rec_lock(LOCK_X, lock_type, ...);
  } else {
    if (is_supremum) {
      lock_type = LOCK_ORDINARY; // next‑key lock
    } else if (is_next) {
      lock_type = LOCK_GAP; // gap lock
    } else {
      lock_type = LOCK_ORDINARY; // next‑key lock
    }
    err = row_ins_set_rec_lock(LOCK_S, lock_type, ...);
  }
  return err;
}

4.3 Rollback Handling

When DB_DUPLICATE_KEY is returned, row_mysql_handle_errors() rolls back to the savepoint taken before the insertion, invoking trx_rollback_to_savepoint() . The undo routine removes the partially inserted primary‑key record, first converting its implicit lock to an explicit one via row_convert_impl_to_expl_if_needed() .

bool row_mysql_handle_errors(...){
  switch (err) {
    case DB_DUPLICATE_KEY:
      if (savept) trx_rollback_to_savepoint(trx, savept);
      break;
    ...
  }
}

4.4 Converting Implicit Locks on the Primary‑Key Record

The conversion checks the transaction’s isolation level and whether the lock already exists. For REPEATABLE‑READ, the implicit lock is turned into an explicit exclusive lock ( LOCK_X ) on the primary‑key record.

void row_convert_impl_to_expl_if_needed(btr_cur_t *cursor, undo_node_t *node){
  if (!node->partial && (node->trx == nullptr ||
      node->trx->isolation_level < trx_t::REPEATABLE_READ)) return;
  if (heap_no != PAGE_HEAP_NO_SUPREMUM &&
      !dict_index_is_spatial(index) &&
      !index->table->is_temporary() &&
      !index->table->is_intrinsic()) {
    lock_rec_convert_impl_to_expl(block, rec, index,
        Rec_offsets().compute(rec, index));
  }
}

4.5 Transferring the Primary‑Key Lock

Before the primary‑key record is deleted, its explicit lock is transferred to the next record (the supremum) using lock_update_delete() and lock_rec_inherit_to_gap() . The supremum receives a next‑key exclusive lock ( LOCK_X ).

void lock_update_delete(const buf_block_t *block, const rec_t *rec){
  ulint heap_no = rec_get_heap_no_new(rec);
  ulint next_heap_no = rec_get_heap_no_new(page + rec_get_next_offs(rec, true));
  lock_rec_inherit_to_gap(block, block, next_heap_no, heap_no);
}

static void lock_rec_inherit_to_gap(...){
  for (lock = lock_rec_get_first(...); lock != nullptr; lock = lock_rec_get_next(...)) {
    if (!lock->trx->skip_lock_inheritance &&
        !lock_rec_get_insert_intention(lock) &&
        !lock->index->table->skip_gap_locks()) {
      lock_rec_add_to_queue(LOCK_REC | LOCK_GAP | lock_get_mode(lock),
                            heir_block, heir_heap_no, lock->index, lock->trx);
    }
  }
}

5. Summary

Under REPEATABLE‑READ isolation, inserting a row that violates a unique index follows these steps:

Insert succeeds on the primary key.

Insertion fails on the unique index, returning DB_DUPLICATE_KEY .

A shared next‑key lock is placed on the conflicting unique‑index record (or an exclusive lock if the statement contains conflict‑resolution clauses).

The implicit lock on the primary‑key record is converted to an explicit lock.

The explicit lock is transferred to the supremum record, which receives a next‑key exclusive lock.

The partially inserted primary‑key record is undone.

In READ‑COMMITTED isolation, steps involving explicit‑to‑implicit lock conversion and lock transfer are omitted, so the supremum record is not locked.