MySQL Bugs: #120901: SELECT ... LOCK IN SHARE MODE returns different row sets depending on index choice under RC/RU

Bug #120901	SELECT ... LOCK IN SHARE MODE returns different row sets depending on index choice under RC/RU
Submitted:	13 Jul 8:53	Modified:	14 Jul 13:12
Reporter:	Aaditya Dubey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.4.9, 8.4.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Possible InnoDB issue in MySQL 8.4.9 under both READ COMMITTED and READ UNCOMMITTED.

In this test case, after initialization, the table has three indexes:

PRIMARY(c0)
c0(c0)
c1(c1)

The statement at [2-2] is:

SELECT DISTINCTROW t0.c0 FROM t0 WHERE TRUE LOCK IN SHARE MODE;

When no index hint is specified, EXPLAIN shows that MySQL chooses the c0 index. The result is the same as explicitly using FORCE INDEX(c0), and only 6 rows are returned.

However, when the same locking read is executed with FORCE INDEX(c1) or FORCE INDEX(PRIMARY), it returns 9 rows.

I think the index choice should not change the logical result of SELECT ... LOCK IN SHARE MODE. In particular, scanning the c0 index and scanning the c1 index should not produce different row sets for the same locking read. I expected the result to be the 9-row result returned by FORCE INDEX(c1) and FORCE INDEX(PRIMARY), but the actual default result is the 6-row result produced by the c0 index path.

The issue is reproducible under both READ COMMITTED and READ UNCOMMITTED.

Actual result with the default index choice, or with FORCE INDEX(c0):

0000000000000001.16039
0000000000000001.32728
0000000000000001.68135
0000000000000001.69376
0000000000000001.93502
000000000900788833

Expected result, observed with FORCE INDEX(c1) or FORCE INDEX(PRIMARY):

0000000000000001.16039
0000000000000001.32728
0000000000000001.69376
000000000900788833
0000000000000001.38422
0000000000000001.25715
0000000000000001.68135
0000000000000001.93502
0000000000000001.32932

The rows missing from the c0 index path are:

0000000000000001.25715
0000000000000001.32932
0000000000000001.38422

This suggests that the result of SELECT ... LOCK IN SHARE MODE under READ COMMITTED / READ UNCOMMITTED depends on the chosen index path.

How to repeat:
Create and initialize the table:

CREATE TABLE t0(
c0 DOUBLE ZEROFILL UNIQUE PRIMARY KEY NOT NULL STORAGE MEMORY COLUMN_FORMAT DEFAULT COMMENT 'asdf',
c1 BIGINT ZEROFILL UNIQUE COMMENT 'asdf' COLUMN_FORMAT FIXED
);

INSERT IGNORE INTO t0(c0, c1) VALUES(1.32728, 61);
INSERT IGNORE INTO t0(c0, c1) VALUES(1.69376, 73), (1.32728, 31), (1.16039, 13);

The table has three indexes after initialization:

PRIMARY(c0)
c0(c0)
c1(c1)

The following test is reproducible under both READ COMMITTED and READ UNCOMMITTED.

For READ COMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

For READ UNCOMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Use two sessions and execute the following transaction statements according to the input schedule.

Transaction 1:

[1-0] BEGIN;
[1-1] UPDATE IGNORE t0 SET c1=NULL WHERE TRUE;
[1-2] INSERT INTO t0(c0, c1) VALUES(1.68135, 15);
[1-3] INSERT INTO t0(c0, c1) VALUES(1.25715, 10), (1.38422, 6), (1.32932, 97);
[1-4] INSERT IGNORE INTO t0(c0, c1) VALUES(1.93502, 35);
[1-5] COMMIT;

Transaction 2:

[2-0] BEGIN;
[2-1] INSERT INTO t0(c0, c1) VALUES(9.00788833E8, 1);
[2-2] SELECT DISTINCTROW t0.c0 FROM t0 WHERE TRUE LOCK IN SHARE MODE;
[2-3] COMMIT;

Input schedule:

[1-0, 1-1, 1-2, 2-0, 2-1, 2-2, 1-3, 1-4, 1-5, 2-3]

At [2-2], the SELECT ... LOCK IN SHARE MODE statement waits because Transaction 1 has not committed yet. While [2-2] is waiting, continue executing [1-3], [1-4], and [1-5]. After Transaction 1 commits, [2-2] resumes.

The following query, using the default optimizer-selected index c0, returns only 6 rows:

SELECT DISTINCTROW t0.c0
FROM t0
WHERE TRUE
LOCK IN SHARE MODE;

The same 6-row result is also observed with:

SELECT DISTINCTROW t0.c0
FROM t0 FORCE INDEX(c0)
WHERE TRUE
LOCK IN SHARE MODE;

However, the following query returns 9 rows:

SELECT DISTINCTROW t0.c0
FROM t0 FORCE INDEX(c1)
WHERE TRUE
LOCK IN SHARE MODE;

The following query also returns 9 rows:

SELECT DISTINCTROW t0.c0
FROM t0 FORCE INDEX(PRIMARY)
WHERE TRUE
LOCK IN SHARE MODE;

I also observed that EXPLAIN for the query without an index hint chooses the c0 index:

type: index
possible_keys: PRIMARY,c0,c1
key: c0
Extra: Using index

Therefore, the default execution path behaves the same as FORCE INDEX(c0), but differs from FORCE INDEX(c1) and FORCE INDEX(PRIMARY).

Hi Aaditya Dubey,

Thank you for the test case. Verified as described.

For tracking and attribution purposes, this issue was reported earlier as Bug #120799 on June 28, 2026.

Bug #120799 contains the same title, description, reproduction procedure, test case, observed result, and expected result. The issue was later confirmed by Percona and was submitted again here without knowledge of the earlier MySQL report.

Could the MySQL Verification Team please cross-reference Bug #120799 and Bug #120901 and review whether the earlier Bug #120799 should be marked as Verified and treated as the original report?

Hello, and thank you for the report.
This appears to be expected behavior for a locking read under READ COMMITTED and READ UNCOMMITTED.
Under READ COMMITTED, InnoDB locks index records encountered by a locking read, but does not lock the intervening gaps.
Concurrent transactions may therefore insert new records into those gaps, and phantom rows are permitted.
The manual documents this behavior in [Transaction Isolation Levels](https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html).

A locking read scans its chosen index incrementally. If the scan waits for a record lock, records inserted and committed later behind its current index position are not subsequently revisited, whereas records inserted ahead of that position may still be encountered.

In this test, the covering c0 secondary-index scan can pass the original c0 entries because Transaction 1’s earlier update changes c1, not c0 (so the is no implicit or explicit lock on the record in the c0 index).
It first waits on the uncommitted c0=1.68135 entry.
While it waits, Transaction 1 inserts 1.25715, 1.32932, and 1.38422, all of which sort before 1.68135
These are therefore behind the scan position when it resumes. The later 1.93502 value sorts after that position and is returned.

The PRIMARY path encounters Transaction 1’s clustered-record locks earlier, before those new keys would be scanned, and consequently observes them after the wait ends.
This happens because locks on the records of the PRIMARY index are taken whenever such a record is modified, no matter which columns is modified.
So even though c0 and PRIMARY have the same sort order, they differ in that PRIMARY contains all the columns, and if any of them, like c1, is modified by Transaction 1, it obtains X lock on it, which prevents Transaction 2 from looking at this record in PRIMARY index.
This then means that even though the scan order is the same for PRIMARY and c0, the moment at which such locking SELECT gets blocked is different:
the scan over PRIMARY will block on the first row which was modified at all, while the scan over c0 blocks only on a row which has new c0.

The c1 path has a different index order and blocking point.

READ UNCOMMITTED uses the READ COMMITTED behavior for locking selects, which is relevant here, so similar story.

We are not aware of a specification guaranteeing access-path-independent results for locking reads in the presence of concurrent gap insertions at these isolation levels. Preventing these phantoms requires an isolation/locking strategy that protects the scanned gaps, such as the next-key locking used by locking reads under REPEATABLE READ.
Unless there is a contrary documented guarantee that we have overlooked, we therefore intend to classify this report as “Not a Bug.”