MySQL Bugs: #111806: there is an unexpected result appeared when i am testing about 'write skew'

Bug #111806	there is an unexpected result appeared when i am testing about 'write skew'
Submitted:	19 Jul 2023 6:41	Modified:	16 Aug 2023 8:50
Reporter:	zx w	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S4 (Feature request)
Version:	Server version: 8.0.17 MySQL Community	OS:	CentOS (CentOS Linux release 7.9.2009)
Assigned to:		CPU Architecture:	x86 (x86_64 x86_64 GNU/Linux)
Tags:	innodb engine, MVCC, repeatable read, snapshot, write skew

Description:
there is an unexpected result appeared when i am testing about 'write skew'.With the innodb engine and  repeatable read isolation,session 1 starts transaction 1 and then delete one data(just like (1,10)) from testing table,but transaction 1 doesn't commit.At the meantime,session 2 starts  transaction 2 and query the testing table,session 2 will ses the data(1,10).Then session 1 commits,session 2 inserts one data into the testing table.Then session 2 query the testing table and then it should see the data(1,10) because its snapshot.I don't know why this result appearing.

How to repeat:
1.make sure the parameters and the engine:
show variables like '%storage_engine%';
show variables like 'transaction_isolation';
show global variables like 'transaction_isolation';

2.create a table and insert one data:
create table a(id int primary key, value int);
insert into a values(1,10);
commit

3.begin test:
set autocommit=OFF;
set global autocommit=OFF;

session1:
start transaction;
delete a where id = 1;

session2:
start transaction;
select * from a;

session1:
commit;

session2:
insert into a values(1, 100);
select * from a;

4.the result is:
+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
+----+-------+

5.the expected result is:
id | value
----+-------
1 |    10
1 |   100
(2 rows)

Suggested fix:
I don't know why this result appearing.I just want to know whether it is a bug or some knowleges i don't know ,thank you !

Hi Mr. w,

Thank you for your bug report.

However, you are using an ancient release of 8.0.

Try repeating the results with 8.0.34 and if you manage to repeat them, file a new bug report.

Unsupported.

Hi,

Seems like your bug is a duplicate of many bugs that are reported, like:

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

or 

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

Also check the Note on this page:

 https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

Hence, this is a known bug.

thank you ,I have already known about it