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).
