MySQL Bugs: #96205: not in expect, two versions row datas in a some transaction

Bug #96205	not in expect, two versions row datas in a some transaction
Submitted:	15 Jul 2019 9:05	Modified:	15 Jul 2019 14:09
Reporter:	Ye Jinrong	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
version MySQL 5.7 or MySQL 8.0
transaction isolation level: repeatable read

table t1:
mysql> create table t1(
c1 int not null primary key,
c2 int not null,
c3 int not null,
key (c2))engine=innodb;

mysql> insert into t1 values(2,2,2);

session1:
mysql> begin; select * from t1 where c2=2;  -- session1, first read, only 1 row

|c1|c2 |c3 |
|2| 2| 2|

session2:
mysql> begin; select * from t1 where c2=2;   -- session1, first read, only 1 row

|c1|c2 |c3 |
|2| 2| 2|

session1:
mysql> update t1 set c1=30 where c2=2; commit;  -- session1, update primary key value and transaction commit

mysql> select * from t1 where c2=2;  --session1, get the new row

|c1|c2 |c3 |
|30| 2| 2|

session2:
mysql> select * from t1 where c2=2;  -- session2, read the row second time, get the old version

|c1|c2 |c3 |
|2| 2| 2|

mysql> select * from t1 where c2=2 for update; -- current read, see the new version row

|c1|c2 |c3 |
|30| 2| 2|

mysql> select * from t1 where c2=2;  -- consistent read, see the old version

|c1|c2 |c3 |
|2| 2| 2|

mysql> update t1 set c1=c1+1 where c2=2; -- session2, update primary key value and read
select * from t1 where c2=2;  -- we can see two rows, one is old version, and another is new version
|c1|c2 |c3 |
|2| 2| 2|
|31| 2| 2|

commit;

select * from t1 where c2=2; -- after commit, can see only 1 row
|c1|c2 |c3 |
|31| 2| 2|

How to repeat:
see above

Suggested fix:
in session2, only return 1 row(the new version) after update, but not 2 rows

Hi Mr. Jinrong,

Thank you for your bug report.

Before I can attempt to reproduce this scenario, would you be so kind to tell me which 5.7 and 8.0 releases did you test exactly, when you observed this behaviour.

Thanks in advance.

Hello Mr. Jinrong,

There is no need for you to report to us anything.

This is actually the expected behaviour, that is fully described in our Reference Manual. Look for example in our manual for 8.0, sub-chapter 15.7.4.

Not a bug.

Hi Sinisa Milivojevic,

MySQL version is 8.0.16 an under MacOS.

Do you mean it is the scenario of 'Phantom Rows', i do not think so.

After i update c1 as "update t1 set c1=c1+1", and read it as "select * from t1 where c2=2", i think it shoud only return the new version row, the old version row should not be retuened.

I has upload the test case in pdf file.

Hi Sinisa Milivojevic,

MySQL version is 8.0.16 an under MacOS.

Do you mean it is the scenario of 'Phantom Rows', i do not think so.

After i update c1 as "update t1 set c1=c1+1", and read it as "select * from t1 where c2=2", i think it shoud only return the new version row, the old version row should not be retuened.

I has upload the test case in pdf file.

test case, how to repeate

Attachment: RR-read-not-in-expect.pdf (application/pdf, text), 111.27 KiB.

This is documented behaviour.

Just read the chapter that I cited.