| Bug #84492 | X lock on PRIMARY key for all records if a subquery is used in UPDATE statement | ||
|---|---|---|---|
| Submitted: | 12 Jan 2017 16:39 | Modified: | 13 Jan 2017 9:03 |
| Reporter: | Oleksandr Rybka | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.14, 5.7.17 | OS: | Oracle Linux (7.2) |
| Assigned to: | CPU Architecture: | Any | |
[13 Jan 2017 9:03]
MySQL Verification Team
Hello Oleksandr Rybka, Thank you for the report and test case. Imho this is a known issue and documented behavior after Bug #78412 Quoting from manual "A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semi-join or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery." - http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html As you can see, UPDATE with IN subquery is NOT optimized the same way as equivalent SELECT: -- with UPDATE, explain plan root@localhost [db1]> explain update t1 set description='new_description' where id in (select id from t2 where id in (5,6)); +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | UPDATE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where; Using index | +----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+--------------------------+ 2 rows in set (0.00 sec) -- With SELECT root@localhost [db1]> explain select * from t1 where id in (select id from t2 where id in (5,6)); +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) Related FR - Bug #72895 Thanks, Umesh
[15 May 2018 14:04]
Daniel Price
Duplicate of https://bugs.mysql.com/bug.php?id=78412 UPDATE with subquery is NOT optimized the same way as equivalent SELECT
Description: Hello, I have noticed that there is X lock on all records if a subquery is used in UPDATE statement together with IN condition. Example of the query: update t1 set description='new_description' where id in (select id from t2 where id in (5,6)); Here is a part of "show engine innodb status": ---TRANSACTION 10237902, ACTIVE 3 sec 4 lock struct(s), heap size 1136, 21 row lock(s), undo log entries 2 MySQL thread id 135, OS thread handle 139925325559552, query id 19777 localhost root cleaning up TABLE LOCK table `test`.`t1` trx id 10237902 lock mode IX RECORD LOCKS space id 3322 page no 3 n bits 112 index PRIMARY of table `test`.`t1` trx id 10237902 lock_mode X TABLE LOCK table `test`.`t2` trx id 10237902 lock mode IS RECORD LOCKS space id 3323 page no 3 n bits 80 index PRIMARY of table `test`.`t2` trx id 10237902 lock mode S locks rec but not gap -------- Expected result: add lock only on filtered by WHERE clause rows. Additional info: If I manually specify ids situation is different. It locks rows which matches WHERE condition. update t1 set description='new_description' where id in (5,6); ---TRANSACTION 10261308, ACTIVE 5 sec 2 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 1176, OS thread handle 139925326358272, query id 147515 localhost root cleaning up TABLE LOCK table `test`.`t1` trx id 10261308 lock mode IX RECORD LOCKS space id 3322 page no 3 n bits 128 index PRIMARY of table `test`.`t1` trx id 10261308 lock_mode X locks rec but not gap -------- How to repeat: mysql> create table t1 (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `description` varchar(120) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; mysql> insert into t1 (`description`) values ('d1'),('d2'),('d3'),('d4'),('d5'),('d6'),('d7'),('d8'),('d9'),('d10'); mysql> create table t2 (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `description` varchar(120) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; mysql> insert into t2 (`description`) values ('d1'),('d2'),('d3'),('d4'),('d5'),('d6'),('d7'),('d8'),('d9'),('d10'); Session1: mysql> set autocommit=0; mysql> begin; mysql> update t1 set description='new_description' where id in (select id from t2 where id in (5,6)); Session2: mysql> update t1 set description='new_description' where id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction