| 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: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.
Description: I used my fuzzer to test the MySQL server and found a transaction bug. In the isolation level of READ COMMITTED, the INSERT statement in the ROLLBACK transaction affects the result of the UPDATE statement in the COMMIT transaction. How to repeat: ** Setup the environment ** /usr/local/mysql/bin/mysqld_safe & /usr/local/mysql/bin/mysql -uroot mysql> create database testdb; mysql> \q /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached) Then, we use test case 1 and test case 2 to test MySQL separately under the environment we set up. They should return the same results but they did not. ** Test case 1 ** // setup two connection, conn_0 and conn_1 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> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level conn_1> mysql -h "127.0.0.1" -u root -P 4000 -D testdb conn_1> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level conn_0> START TRANSACTION; conn_1> START TRANSACTION; conn_1> insert into t_cqieb values (141, 210000, 41.72, 56, 76, null, 32.6, null, 11, 12, 74), (141, 211000, 41.73, 87, 84, null, 11.2, 79.63, null, 58, 4), (141, 212000, 41.73, 87, 84, null, 11.2, 79.63, null, 58, 4), (141, 213000, null, null, null, null, null, null, null, null, null); conn_1> ROLLBACK; 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 1) ** Query OK, 39 rows affected (0.00 sec) Rows matched: 39 Changed: 39 Warnings: 0 ** Output of SELECT statement in conn_0 (Test case 1) ** +------+-------+---------+----------+----------+----------+----------+--------------+--------------+----------+----------+ | wkey | pkey | c_jyr9r | c_abru9d | c_ljfbec | c_u91c6b | c_8ylmeb | c_rejdnc | c_zyinj | c_llfuod | c_lda_vb | +------+-------+---------+----------+----------+----------+----------+--------------+--------------+----------+----------+ | 116 | 15000 | 42.63 | 27 | 85 | 19 | NULL | NULL | 54.97 | 25 | 49 | | 116 | 16000 | 31.55 | 59 | 90 | 84 | NULL | NULL | 50.66 | 87 | 93 | | 116 | 17000 | 23.2 | 98 | 21 | 16 | NULL | NULL | 59.85 | 84 | 66 | | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | | 116 | 82000 | 44.51 | 20 | 22 | 59 | 90.67 | 63.3 | 43.95 | 100 | 75 | +------+-------+---------+----------+----------+----------+----------+--------------+--------------+----------+----------+ 39 rows in set (0.00 sec) ** 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> BEGIN OPTIMISTIC; 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 1) ** Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 ** Output of SELECT statement in conn_0 (Test case 1) ** Empty set (0.00 sec)