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

• Submitted: 2 Sep 7:49
• Modified: 3 Sep 9:52
• Reporter: Hongyu Yang
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 8.4, 8.0.22, 9.4.0
• OS: Any
• CPU Architecture: Any

[2 Sep 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 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>