Why SELECT … FOR UPDATE Still Reads the Primary Key: MySQL Index Scan Deep Dive

Preparation

We create a test table t7 with an integer primary key id and several indexed columns i1 ‑ i6. The table uses the InnoDB engine and UTF8MB4 charset. After populating the table with sample rows, we run the example query: SELECT i1, i2 FROM t7 LIMIT 1 FOR UPDATE; The query is executed on MySQL 8.4.6 (InnoDB).

本文基于 MySQL 8.4.6 源码，存储引擎为 InnoDB。

1. The Plan Looks Like a Covering Index Scan

Running EXPLAIN on the example query yields an execution plan that uses the secondary index idx_i1_i2_i3 with Using index in the Extra column, suggesting a covering index scan.

However, the actual transaction reads both the secondary index record and the corresponding primary‑key record, meaning a table‑row lookup (回表) occurs.

2. Indirect Proof via Performance Schema

By querying performance_schema.data_locks for locks on table t7, we observe two lock rows:

Record lock on the secondary index entry <i1 = 101, i2 = 102, i3 = 103, id = 1>.

Record lock on the primary‑key entry <id = 1>.

These locks confirm that the transaction accessed both the secondary and primary indexes.

3. Direct Proof by Inspecting InnoDB Source

The source code path that handles the query includes two critical functions:

ha_innobase::build_template()

row_search_mvcc()

In ha_innobase::build_template(), when the lock type is LOCK_X (as set by FOR UPDATE), the variable whole_row is forced to true. Consequently, m_prebuilt->need_to_access_clustered becomes true, indicating that a clustered (primary‑key) row must be fetched.

void ha_innobase::build_template(bool whole_row) {
    if (m_prebuilt->select_lock_type == LOCK_X) {
        whole_row = true;
    } else if (!whole_row) {
        ...
    }
    clust_index = m_prebuilt->table->first_index();
    index = whole_row ? clust_index : m_prebuilt->index;
    m_prebuilt->need_to_access_clustered = (index == clust_index);
    ...
}

Later, in row_search_mvcc(), the code checks the flag:

if (index != clust_index && prebuilt->need_to_access_clustered) {
    err = row_sel_get_clust_rec_for_mysql(...);
}

If the flag is true, the engine explicitly fetches the primary‑key record, confirming the table‑row lookup.

4. Why the Engine Must Read the Primary Key

The FOR UPDATE clause tells MySQL that the selected rows will be modified later, so the transaction must lock the actual rows. Locking only the secondary‑index entries would leave the primary rows unprotected, allowing other transactions to modify or delete them, which would break consistency between the secondary and primary indexes.

Therefore, even when the optimizer reports a covering index scan, InnoDB still performs a back‑lookup to lock and read the primary‑key row, ensuring isolation and data integrity.

5. Summary

When a SELECT … FOR UPDATE query appears to use a covering index, MySQL still accesses the primary‑key record to acquire exclusive locks and maintain transactional consistency. This behavior is verified both by examining lock information in performance_schema and by tracing the relevant InnoDB source code.