MySQL Bugs: #115463: select wrong value after update statement when another session update the same r

Thank you for your help! If the status of the bug report you submitted changes, you will be notified. You may return here and check on the status or update your report at any time. That URL for your bug report is: http://bugs.mysql.com/115463.

Bug #115463	select wrong value after update statement when another session update the same r
Submitted:	29 Jun 2024 18:03	Modified:	1 Jul 2024 11:32
Reporter:	jian liang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	inndob

Description:
You will find select result is not the same as the one you just update. You cannot believe it but it's true in the following scenario.
The engine is innodb. The isolation level is the default 'repeatable read'. 

How to repeat:
Prepare test data:

```sql
CREATE TABLE ApiLog (id int AUTO_INCREMENT, addr varchar(20), PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
insert into ApiLog (id,addr) values (1,'aaa');
```

Reproduce steps:

1. start session 1 and do select

```sql
start transaction;
select addr from ApiLog where id=1; -- show original value "aaa"
```

NOTE: if not run this select statement here, the bug cannot reproduce.

2. another session change value of the same row

```sql
update ApiLog set addr='bbb' where id=1;
```

3. back to session 1, do update and select:

```sql
-- NOTE: update using the same value as step 2
update ApiLog set addr='bbb' where id=1;  
select addr from ApiLog where id=1; 
-- expect 'bbb', actual still 'aaa'!
```

Expected:
select result should be the same as the one used in update statement.

Actual:
wrong select result after update.

I find the issue first on maraidb 10.3, then verified mysql 8.0.20 is bad version as well. 
I report the same issue on mariadb: https://jira.mariadb.org/browse/MDEV-34496

Same issue happen to me.

Hi Mr. Liang,

Thank you for your bug report.

However, this is not a bug.

That is how MVCC of the Repeatable-Read is designed to work. You will find all the explanations in our Reference Manual.

Not a bug.

Can you find the relevant description to this issue in the reference manual?
I consider it is a bug because the select statement is just followed the update statement in the same session, and even no other session affect it.

Hi Mr. LIang,

The release that you are using is very old.

Current release of 8.0 is 8.0.38.

We ran your test case and we were not able to repeat it.

We got a lock wait timeout.

We suppose that you ran your test case with autocommit set to on.

Even in that scenario, we did not manage to repeat your behaviour with 8.0.38.

Can't repeat.