MySQL Bugs: #104985: Weird SELECT when a record is modified to the same value by two transactions

Bug #104985	Weird SELECT when a record is modified to the same value by two transactions
Submitted:	19 Sep 2021 16:11	Modified:	20 Sep 2021 3:47
Reporter:	Dai Dinary	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	repeatable read, transaction

Description:
Under REPEATABLE-READ isolation level, if two transactions concurrently modify the same row to the same value, the transaction that modifies later does not see the modified content.

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

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

Suggested fix:
I think it is so weird for session 1 to see the second row is still (1, 1) after the successful execution of an UPDATE with the "WHERE TRUE" predicate. 

So I think it will be better for s1 to see all records it updates regardless of whether the values before and after the UPDATE are the same.

Archive storage engine doesn't support transaction

Attachment: 104985.png (image/png, text), 376.11 KiB.

Thank you for the bug report. Archive Storage engine doesn't support transactions:

https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engine.html

Thanks for your reply. I'm so sorry for choosing the wrong category. Please see:

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

Hi Mr. Dinary,

It is obvious that you have chosen a wrong category.

If it is OK with you, we shall continue working on your new report, numbered # 104986.

We presume that you are using InnoDB SE. If that is not the case, please let us know.