Bug #107887 | INSERT stmt in ROLLBACK txn affects UPDATE stmt in COMMIT txn (READ COMMITTED) | ||
---|---|---|---|
Submitted: | 14 Jul 2022 19:09 | Modified: | 15 Jul 2022 13:15 |
Reporter: | Zuming Jiang | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 8.0.28 | OS: | Ubuntu (20.04) |
Assigned to: | CPU Architecture: | Any |
[14 Jul 2022 19:09]
Zuming Jiang
[14 Jul 2022 19:15]
Zuming Jiang
mysql_bk.sql
Attachment: mysql_bk.sql (application/octet-stream, text), 7.03 KiB.
[15 Jul 2022 12:24]
MySQL Verification Team
Hi Mr. Jiang, In your test case there is a command : BEGIN OPTIMISTIC We do not have such a command in our server. Next, SET GLOBAL is not valid for the connection that is executing it. You should also SET LOCAL. Last, but not least, we do not see the proof that the INSERT from the connection 1 affected the results. We do not see any of the rows in the final SELECT. We are waiting on your feedback.
[15 Jul 2022 12:42]
Zuming Jiang
Thanks for your reply. I made some mistakes in reporting test case 2. Here is the correct version: ** Test case 2 ** conn_0> /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached) conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb conn_0> START TRANSACTION; conn_0> update t_cqieb set wkey = 116 where t_cqieb.c_rejdnc not in ( select subq_0.c0 as c0 from (select ref_8.c_myvn4d as c0 from t_b9lvzc as ref_8 where ref_8.c_lszpl < ref_8.c_t41kdd) as subq_0 where subq_0.c0 < ( select ref_9.c_1xf8oc as c0 from (t_b9lvzc as ref_9 left outer join t_b9lvzc as ref_10 on (ref_9.c_t41kdd = ref_10.wkey )) where ref_9.c_t41kdd <> ref_10.pkey) order by c0 asc); conn_0> select * from t_cqieb where wkey = 116; conn_0> COMMIT; ** Execution result of UPDATE statement in conn_0 (Test case 2) ** Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 ** Output of SELECT statement in conn_0 (Test case 2) ** Empty set (0.00 sec) It is quite strange that you did not see any output from the last SELECT statement in test case 1. In my test, it could be stably reproduced if I follow the steps written in test case 1. Later I plan to write a C++ program, which uses MySQL C/C++ interface, to stably reproduce the problem. Perhaps you could wait some days and then I will post the C++ program source code here.
[15 Jul 2022 12:51]
MySQL Verification Team
Hi Mr. Jiang, There is no need to send you C/C++ program. For the start, you could reply to all of our questions. Most notably, we do not see any rows from that INSERT in the result set of the first test case. We have one more question. Why do you think that an empty result set, from the test case 2, is a proof that, after ROLLBACK in the connection 1, connection 2 is affected by the INSERT that preceded it ??? We are waiting on your answers to all of our questions ........ Please, do not send us partial answers.
[15 Jul 2022 13:06]
Zuming Jiang
For question 2 (Why do you think that an empty result set, from the test case 2, is a proof that, after ROLLBACK in the connection 1, connection 2 is affected by the INSERT that preceded it ???): Principally, the UPDATE statement and SELECT statement in test case 1 and test case 2 should do the same thing and return the same result, because ROLLBACK transactions should not affect the results of COMMIT transactions. However, in test case 1, the UPDATE statement in connection 1 changes some rows, and then the SELECT statement returns some results. In test case 2, the UPDATE statement changes nothing, and the SELECT statement returns empty. Therefore, the ROLLBCAK transaction does affect the result of COMMIT transaction, though it does not insert specific rows to the result set of the SELECT statement. For question 1 ( Most notably, we do not see any rows from that INSERT in the result set of the first test case.): The INSERT does not directly affect the results of the SELECT statement. However, it indirectly changes the behavior of the UPDATE statement, which changes values of some rows. Without the INSERT, the UPDATE will change nothing.
[15 Jul 2022 13:15]
MySQL Verification Team
Hi Mr. Jiang, Thank you for your bug report. Your bug is a duplicate of the already verified bug #104986. It is not yet known when will it be fixed. Duplicate.