Bug #105030 Inconsistent SELECT View when modify a record added by another transaction

• Submitted: 24 Sep 2021 6:50
• Modified: 24 Sep 2021 12:37
• Reporter: Dai Dinary
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.26
• OS: Any
• CPU Architecture: Any
• Tags: repeatable read, transaction

[24 Sep 2021 6:50] Dai Dinary

Description:
If a transaction modifies an inserted record by a concurrent transaction, 
the visibility of this new record for the current transaction depends on the value of the inserted record.

This issue is similar to the issue reported in 

https://bugs.mysql.com/bug.php?id=104986

but the trigger conditions and behaviors of them are different.

How to repeat:
/* init */ drop table if exists t;
/* init */ create table t(a int, b int);
/* init */ insert into t values (0, 0), (1, 1);

/* s1 */ begin;
/* s1 */ select * from t; -- [(0, 0), (1, 1)]
/* s2 */ begin;
/* s2 */ insert into t values (10, 2);
/* s2 */ commit;
/* s1 */ select * from t; -- [(0, 0), (1, 1)]
/* s1 */ update t set a = 10 where true;
/* s1 */ select * from t;  -- [(10, 0), (10, 1)]
/* s1 */ commit;

-------------------------------------------------------------

/* init */ drop table if exists t;
/* init */ create table t(a int, b int);
/* init */ insert into t values (0, 0), (1, 1);

/* s1 */ begin;
/* s1 */ select * from t; -- [(0, 0), (1, 1)]
/* s2 */ begin;
/* s2 */ insert into t values (11, 2);
/* s2 */ commit;
/* s1 */ select * from t; -- [(0, 0), (1, 1)]
/* s1 */ update t set a = 10 where true;
/* s1 */ select * from t;  -- [(10, 0), (10, 1), (10, 2)]
/* s1 */ commit;

[24 Sep 2021 12:37] MySQL Verification Team

Hi Mr. Dinary,

Thank you for another of your interesting bug reports.

We have studied the causes of this behaviour and we must concluded that, in this case, this bug is indeed a duplicate of:

https://bugs.mysql.com/bug.php?id=104986

Having a duplicate will increase the priority of the original bug !!!!!

Thanks again ........