MySQL Bugs: #105670: Weird View When a Record is Deleted by Two Concurrent Transactions

Bug #105670	Weird View When a Record is Deleted by Two Concurrent Transactions
Submitted:	23 Nov 2021 8:53	Modified:	29 Nov 2021 12:12
Reporter:	Dai Dinary	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Under REPEATABLE-READ isolation level, if two transactions concurrently delete the same row, the transaction that deletes later can also see the deleted record. 

I do not assure whether this is a duplicate of 

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

Sorry to interrupt if it is...

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

/* s1 */ begin;
/* s1 */ select * from t; -- [(1, 1), (2, 2)]
/* s2 */ begin;
/* s2 */ delete from t where a = 1;
/* s2 */ commit;
/* s1 */ select * from t; -- [(1, 1), (2, 2)]
/* s1 */ delete from t where c1 = 1 or c1 = 2;
/* s1 */ select * from t;  -- [(1, 1)]
/* s1 */ commit;

Hi Mr. Dinary,

Thank you for your bug report.

No, this bug is not a duplicate of:

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

Actually, it is not a bug at all. Transaction `s1` is in the REPEATABLE-READ, which is why you do not see any changes made by transaction `s2`. That is expected behaviour.

We also do not see where are the columns `c1` and `c2` comming from ......

Not a bug.

Thanks for your reply.

It is understandable that s1 does not see the deletion made by s2, but s1 itself has also performed the delete operation:

/* s1 */ delete from t where c1 = 1 or c1 = 2;

and it finds that its delete did not succeed on the record:

(1, 1)

I'm so sorry for the stupid mistake... actually the reproduce procedure is:

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

/* s1 */ begin;
/* s1 */ select * from t; -- [(1, 1), (2, 2)]
/* s2 */ begin;
/* s2 */ delete from t where a = 1;
/* s2 */ commit;
/* s1 */ select * from t; -- [(1, 1), (2, 2)]
/* s1 */ delete from t where a = 1 or a = 2;
/* s1 */ select * from t;  -- [(1, 1)]
/* s1 */ commit;

Hi Mr. Dinary,

With your new test case, we can confirm that this report is a duplicate of the following bug:

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

Duplicate.

Thanks for your reply. 

By the way, I am curious about the trigger mechanism of this bug, 
could you please explain it briefly at the implementation level? 

Thank you very much!

Hi Mr. Dinary,

We wish that we could have explained it to you ......

It is a piece of code in the Runtime part of the server and not in InnoDB storage engine.

Simply, that is some optimisation that does not work properly in some concurrent scenario. If we knew the full mechanism on how is this problem triggered, we would have fixed it by now ..... This is a hard part to diagnose, since you have to do it in real-time debugging and you can not debug it with a step-by-step method.