Bug #118923 Abnormal read phenomena are observed under MySQL's Read Committed isolation level

Submitted: 2 Sep 2025 7:49 Modified: 3 Sep 2025 9:52
Reporter: Hongyu Yang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.4, 8.0.22, 9.4.0 OS:Any
Assigned to: CPU Architecture:Any

[2 Sep 2025 7:49] Hongyu Yang
Description:
Under MySQL's Read Committed isolation level, abnormal read phenomena are observed when modifying primary keys.

How to repeat:
--init

use test;
drop table if exists t;
create table t(a int, b int, primary key(a,b));
insert into t values (1,1), (1,2), (3,3);

-- execute txns

/* session 1 */  set transaction isolation level READ COMMITTED;

/* session 2 */  set transaction isolation level READ COMMITTED;

/* session 1 */  begin;

/* session 2 */  begin;

/* session 1 */  update t set a = 2 where b = 3;		-- ok

/* session 2 */  update t set a = 4 where b > 0;		-- block

/* session 1 */  commit;		-- ok

/* session 2 */  commit;		-- ok

/* session 2 */  select * from t;		-- [(4,1), (4,2), (2,3)]

Theoretically, after the transaction completes, the version read by MySQL should be [(4,1), (4,2), (4,3)], because Transaction 2 has modified all the data.

When we change only one condition in the above test case: remove the primary key, the result becomes correct, with the final output being [(4,1), (4,2), (4,3)].

This situation occurs only under the RC isolation level and when the primary key exists simultaneously. 

Under the RR isolation level, the output is correct.
[3 Sep 2025 9:52] MySQL Verification Team
Tanks for the test case

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 2 | 3 |
| 4 | 1 |
| 4 | 2 |
+---+---+
3 rows in set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.