Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads

Background: A customer observed unexpected lock‑wait alerts in production. The problematic case can be reproduced with a simple SQL script that creates a table with a secondary index on value and runs two concurrent sessions, each updating a different row but causing a lock block.

Cause analysis: The issue is not related to the datetime type of the secondary index; it also reproduces with an int index. Performance schema shows that session 2 waits for a lock on the index entry (10, 2) , which is held by session 1, indicating that the value < 6 condition is not filtered out at the InnoDB layer.

InnoDB processing flow: The server calls read_range_next() , which repeatedly invokes the InnoDB function row_search_mvcc() . Inside this function the following steps occur:

1. btr_pcur_open_with_no_init // locate the first record >= start_range or the next record
2. sel_set_rec_lock // lock the record; if it fails, wait
3. row_search_idx_cond_check // verify the index condition

The source code of row_search_idx_cond_check() is shown below:

ICP_RESULT row_search_idx_cond_check(
    byte *mysql_rec,
    row_prebuilt_t *prebuilt,
    const rec_t *rec,
    const ulint *offsets)
{
  ICP_RESULT result;
  ulint i;
  ut_ad(rec_offs_validate(rec, prebuilt->index, offsets));
  if (!prebuilt->idx_cond) {
    return (ICP_MATCH);
  }
  MONITOR_INC(MONITOR_ICP_ATTEMPTS);
  /* Convert needed fields to MySQL format */
  if (prebuilt->blob_heap != nullptr) {
    mem_heap_empty(prebuilt->blob_heap);
  }
  for (i = 0; i < prebuilt->idx_cond_n_cols; i++) {
    const mysql_row_templ_t *templ = &prebuilt->mysql_template[i];
    if (templ->is_virtual) {
      continue;
    }
    if (!row_sel_store_mysql_field(
            mysql_rec, prebuilt, rec, prebuilt->index, prebuilt->index, offsets,
            templ->icp_rec_field_no, templ, ULINT_UNDEFINED, nullptr,
            prebuilt->blob_heap)) {
      return (ICP_NO_MATCH);
    }
  }
  result = innobase_index_cond(prebuilt->m_mysql_handler);
  switch (result) {
    case ICP_MATCH:
      if (!prebuilt->need_to_access_clustered ||
          prebuilt->index->is_clustered()) {
        if (!row_sel_store_mysql_rec(mysql_rec, prebuilt, rec, nullptr, FALSE,
                                     prebuilt->index, prebuilt->index, offsets,
                                     false, nullptr, prebuilt->blob_heap)) {
          ut_ad(prebuilt->index->is_clustered());
          return (ICP_NO_MATCH);
        }
      }
      MONITOR_INC(MONITOR_ICP_MATCH);
      return (result);
    case ICP_NO_MATCH:
      MONITOR_INC(MONITOR_ICP_NO_MATCH);
      return (result);
    case ICP_OUT_OF_RANGE:
      MONITOR_INC(MONITOR_ICP_OUT_OF_RANGE);
      const auto record_buffer = row_sel_get_record_buffer(prebuilt);
      if (record_buffer) {
        record_buffer->set_out_of_range(true);
      }
      return (result);
  }
  ut_error;
  return (result);
}

After the InnoDB layer returns the record, the server layer checks whether the record falls inside end_range . If it does not, unlock_row() is called to release the lock acquired by sel_set_rec_lock :

if (compare_key(end_range) > 0) {
  /* The last read row does not fall in the range. Release the lock. */
  unlock_row();
  result = HA_ERR_END_OF_FILE;
}

Because end_range is not pushed down, session 2 must read both rows (5 and 10) from InnoDB; row 10 is locked by session 1, so the lock‑wait occurs before the server can filter out‑of‑range rows.

Summary of the root cause: the end_range condition is not pushed down to InnoDB. If it were, row_search_idx_cond_check would filter out the non‑matching record early, avoiding the extra lock.

Follow‑up question – primary‑key index behavior:

For SELECT ... FOR UPDATE on a primary key, the same blocking occurs because the read type is ROW_READ_WITH_LOCKS and the lock‑wait path is taken.

For UPDATE on a primary key, the read type is ROW_READ_TRY_SEMI_CONSISTENT ; InnoDB releases the lock early, so session 2 does not block.

Code analysis shows the difference in prebuilt->row_read_type and how semi‑consistent reads work. When a lock wait happens under ROW_READ_TRY_SEMI_CONSISTENT , the lock is released and the engine may later retry the read if the end‑range condition is satisfied.

Semi‑consistent read summary:

No conflict – read the latest version and lock it.

Conflict – read the latest committed version, let the server decide if the row satisfies the condition; if yes, lock and wait, otherwise release the lock.

Why semi‑consistent is not applied to secondary indexes: primary‑key records store DB_TRX_ID and DB_ROLL_PTR , allowing easy construction of the latest committed version. Secondary index records lack these columns; constructing a visible version requires a costly table‑lookup, so the optimization is not worthwhile.

Final conclusion: This is not a MySQL bug but a feature caused by missing index‑condition push‑down combined with semi‑consistent read behavior. It may cause extra locking but does not lead to data errors, and the design prefers safety over occasional unnecessary locks.