Description:
The InnoDB Locking manual (17.7.1) states:
> "Gap locking is not needed for statements that lock rows using a unique index to search for a unique row... For example, if the `id` column has a unique index, the following statement uses only an index-record lock for the row having `id` value 100... `SELECT * FROM child WHERE id = 100;`"
This is read as a blanket guarantee — "equality search on a unique index never needs a gap lock" — and is quoted as such elsewhere, including in this tracker's own resolutions (e.g. Bug #71735, Bug #107958).
In practice the guarantee holds only for single-value equality executed via `const`/`eq_ref`/`ref` access. It silently stops holding once an equality condition is expressed as `WHERE unique_col IN (v1, ..., vn)` with enough values that the optimizer compiles it into `range` access (visible via `EXPLAIN` as `type: range`) instead of independent point lookups. Once that happens, the scan inherits ordinary range-scan locking semantics: under REPEATABLE READ, `row_search_mvcc` (`storage/innobase/row/row0sel.cc`) unconditionally next-key-locks a leaf page's supremum pseudo-record whenever the scan's forward cursor crosses into a new page — even though every individual value in the list is an exact equality match, and even though no single value's lookup, done in isolation, would ever need this.
This is **not** a request to change the locking behavior — per this tracker's own resolutions (Bug #112106, Bug #96437, Bug #71735: "InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned"), the behavior is intentional. The gap is purely that the manual's stated guarantee gives no indication that:
1. it's a property of the chosen access type, not of the WHERE clause's logical shape;
2. a multi-value IN-list on a unique index can silently lose the guarantee once large enough;
3. when that happens, next-key locks (including on a page's supremum) land at page boundaries the caller can't predict from the query alone — only from `EXPLAIN`'s access type and, at runtime, from `performance_schema.data_locks`.
**Exact source locations** (confirmed by reading, not inferred), in MySQL 8.4.5 (the current GA release, `storage/innobase/row/row0sel.cc`, function `row_search_mvcc`):
- Line 4971 — the `if (page_rec_is_supremum(rec))` branch.
- Line 5032 — the `sel_set_rec_lock(..., LOCK_ORDINARY, ...)` call that takes the next-key lock on the supremum.
How to repeat:
```sql
-- ============================================================
-- Session A: setup + the locking statement (leave transaction open)
-- ============================================================
DROP DATABASE IF EXISTS innodb_locking_repro;
CREATE DATABASE innodb_locking_repro;
USE innodb_locking_repro;
CREATE TABLE repro (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
filler CHAR(10) NOT NULL DEFAULT 'x',
PRIMARY KEY (id)
) ENGINE=InnoDB;
-- 3,000 rows is enough to span multiple PK leaf pages with this row size.
SET SESSION cte_max_recursion_depth = 5000;
INSERT INTO repro (filler)
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 3000)
SELECT 'x' FROM seq;
-- Confirm the optimizer compiles the IN-list into range access, not
-- independent point lookups:
EXPLAIN SELECT * FROM repro WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,
42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,
68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,
94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,
115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,
134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,
153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,
172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,
191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,
210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,
229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,
248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,
267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,
286,287,288,289,290,291,292,293,294,295,296,297,298,299,300);
-- Expected: type=range, rows=300.
-- Open a REPEATABLE READ (default) transaction and lock all 300 rows.
-- Every value is an exact primary-key equality match.
START TRANSACTION;
SELECT id FROM repro WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,
42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,
68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,
94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,
115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,
134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,
153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,
172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,
191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,
210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,
229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,
248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,
267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,
286,287,288,289,290,291,292,293,294,295,296,297,298,299,300) FOR UPDATE;
-- Do NOT COMMIT yet -- leave this session open and switch to Session B.
-- ============================================================
-- Session B: run this while Session A's transaction above is still open
-- ============================================================
SELECT LOCK_MODE, LOCK_STATUS, LOCK_DATA, COUNT(*) AS cnt
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'innodb_locking_repro' AND OBJECT_NAME = 'repro'
GROUP BY LOCK_MODE, LOCK_STATUS, LOCK_DATA
ORDER BY LOCK_DATA;
-- Expected if the manual's stated guarantee held without qualification:
-- 300 rows of X,REC_NOT_GAP (one per matched id) + 1 IX (table-level intent lock).
-- Actual:
-- 300 rows of X,REC_NOT_GAP, PLUS one additional row:
-- LOCK_MODE=X, LOCK_DATA='supremum pseudo-record'
-- i.e. an ordinary next-key lock (record + gap) on a pseudo-record that
-- matches none of the 300 equality conditions in the query, taken purely
-- because the scan's forward cursor crossed a leaf-page boundary while
-- satisfying the IN-list.
-- Back in Session A: COMMIT or ROLLBACK to release the locks and clean up.
```
Suggested fix:
Add a note to the "gap locking is not needed..." paragraph in 17.7.1 (and 17.7.3, which repeats it) clarifying that the guarantee applies to single-row point lookups and doesn't automatically extend to `WHERE unique_col IN (...)` once `EXPLAIN` shows range access chosen for the list.
