Bug #95936 | insert into ... select does not work with repeatable read | ||
---|---|---|---|
Submitted: | 23 Jun 2019 20:54 | Modified: | 27 Jun 2019 13:16 |
Reporter: | Tianshi Wang | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7.25 | OS: | CentOS |
Assigned to: | CPU Architecture: | x86 | |
Tags: | insert into select, insert select, repeatable read, transaction level |
[23 Jun 2019 20:54]
Tianshi Wang
[27 Jun 2019 13:16]
MySQL Verification Team
Hi, Thanks for the test case, reproduced as described. bogdan mysql [localhost:5726] {msandbox} (test) > create table ta(id int); Query OK, 0 rows affected (0.01 sec) mysql [localhost:5726] {msandbox} (test) > create table tb(id int); Query OK, 0 rows affected (0.02 sec) mysql [localhost:5726] {msandbox} (test) > insert into ta values (1),(3); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [localhost:5726] {msandbox} (test) > set global tx_isolation=2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost:5726] {msandbox} (test) > show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead | +---------+------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:5726] {msandbox} (test) > set session tx_isolation=2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost:5726] {msandbox} (test) > show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead | +---------+------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:5726] {msandbox} (test) > begin; Query OK, 0 rows affected (0.00 sec) mysql [localhost:5726] {msandbox} (test) > select * from ta; +------+ | id | +------+ | 1 | | 3 | +------+ 2 rows in set (0.00 sec) mysql [localhost:5726] {msandbox} (test) > select * from ta; +------+ | id | +------+ | 1 | | 3 | +------+ 2 rows in set (0.00 sec) mysql [localhost:5726] {msandbox} (test) > mysql [localhost:5726] {msandbox} (test) > insert into tb select * from ta; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql [localhost:5726] {msandbox} (test) > select * from tb; +------+ | id | +------+ | 1 | | 3 | | 2 | +------+ 3 rows in set (0.00 sec) mysql [localhost:5726] {msandbox} (test) > ----- mysql [localhost:5726] {msandbox} ((none)) > use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql [localhost:5726] {msandbox} (test) > show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 sec) mysql [localhost:5726] {msandbox} (test) > insert into ta values (2); Query OK, 1 row affected (0.00 sec) mysql [localhost:5726] {msandbox} (test) >
[18 Oct 2019 15:41]
Bill Okara
this seems to be similar as the issue as described in https://forums.mysql.com/read.php?22,678692,678692#msg-678692 (Repeatable Read update sees new value from another session) That is, the UPDATE WHERE clause seemed to be seeing the lastest committed vs the isolated version of a record.