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: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 8.0.26 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[23 Nov 2021 8:53]
Dai Dinary
[23 Nov 2021 13:23]
MySQL Verification Team
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.
[23 Nov 2021 13:32]
Dai Dinary
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)
[23 Nov 2021 13:45]
Dai Dinary
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;
[23 Nov 2021 14:00]
MySQL Verification Team
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.
[29 Nov 2021 12:12]
Dai Dinary
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!
[29 Nov 2021 13:34]
MySQL Verification Team
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.