Bug #85557 Manual does not explain locks set by UPDATE with subquery referring other table

Description:
Please, try to find out in the manual (https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html or any other version/page) what locks are set in the table ta by the following kind of query:

update tb set c2 = 3 where ... and c1 in (select c1 from ta where ...);

with default (or any) transaction isolation level. 

It seems next-key S locks are set for each row read from ta, and these locks may prevent concurrent INSERTs into the ta table.

How to repeat:
Set up the following simplae InnoDB tables:

mysql> create table ta(id int primary key, c1 int);
Query OK, 0 rows affected (0.35 sec)

mysql> create table tb(id int primary key, c1 int, c2 int, key(c1));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into ta values(1,1),(3,3),(5,5);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into tb values(1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain update tb set c2 = 3 where c1 in (select c1 from ta);
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE             | tb    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | ta    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    3 |    33.33 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.03 sec)

So, it seems we have to read the entire ta table. Now, let's do this update and check the locks set:

mysql> set global innodb_status_output=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb set c2 = 3 where c1 in (select c1 from ta);
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2017-03-21 10:24:07 0x7ff54804a700 INNODB MONITOR OUTPUT
=====================================
...
------------
TRANSACTIONS
------------
Trx id counter 1302
Purge done for trx's n:o < 1301 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1301, ACTIVE 7 sec
4 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 2
MySQL thread id 4, OS thread handle 140691451979520, query id 18 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`tb` trx id 1301 lock mode IX
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`tb` trx id 1301 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000515; asc       ;;
 2: len 7; hex 310000013b0110; asc 1   ;  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000003; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000510; asc       ;;
 2: len 7; hex ae00000122011c; asc     "  ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000515; asc       ;;
 2: len 7; hex 310000013b0131; asc 1   ; 1;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000003; asc     ;;

TABLE LOCK table `test`.`ta` trx id 1301 lock mode IS
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test`.`ta` trx id 1301 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000050a; asc       ;;
 2: len 7; hex aa0000011e0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000050a; asc       ;;
 2: len 7; hex aa0000011e011c; asc        ;;
 3: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050a; asc       ;;
 2: len 7; hex aa0000011e0128; asc       (;;
 3: len 4; hex 80000005; asc     ;;

--------

As you can see, we have next-key S locks set for each row in the ta table. If we try to insert row (2,2) in another session, we are in trouble:

---TRANSACTION 1302, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 140691322926848, query id 24 localhost root update
insert into ta values (2,2)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test`.`ta` trx id 1302 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000050a; asc       ;;
 2: len 7; hex aa0000011e011c; asc        ;;
 3: len 4; hex 80000003; asc     ;;

------------------
TABLE LOCK table `test`.`ta` trx id 1302 lock mode IX
RECORD LOCKS space id 23 page no 3 n bits 72 index PRIMARY of table `test`.`ta` trx id 1302 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000050a; asc       ;;
 2: len 7; hex aa0000011e011c; asc        ;;
 3: len 4; hex 80000003; asc     ;;

So, it definitely makes sense to explain locking with subquery in WHERE clause in more details.

Suggested fix:
Document what exact locks are set in the table mentioned in subquery in the WHERE clause for UPDATE/DELETE.

See https://bugs.mysql.com/bug.php?id=84492 also. Not sure what it's a "duplicate" of.

Surely, when isolation level is read committed we do not have any locks on table we read from:

mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb set c2 = 3 where c1 in (select c1 from ta);
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2017-03-21 14:19:49 0x7ff540537700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 14316 srv_idle
srv_master_thread log flush and writes: 14323
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 0, rounds 39, OS waits 19
RW-excl spins 0, rounds 30, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 39.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1305
Purge done for trx's n:o < 1303 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1304, ACTIVE 6 sec
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 6, OS thread handle 140691322926848, query id 45 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`tb` trx id 1304 lock mode IX
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`tb` trx id 1304 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000518; asc       ;;
 2: len 7; hex 330000013c0110; asc 3   <  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000518; asc       ;;
 2: len 7; hex 330000013c0131; asc 3   < 1;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000003; asc     ;;

--------
...

Hi Valerii,

There is a very good reason why these locks need to be set, so this is not Storage Engine bug. However, I do agree  that it is not documented well.

Verified as 'Documentation" bug.

Posted by developer:
 
Bug #84492, which is referenced above, is a duplicate of https://bugs.mysql.com/bug.php?id=78412.

Posted by developer:
 
Bug #84492, which is referenced above, is a duplicate of Bug #78412.

Posted by developer:
 
The "Locks Set by Different SQL Statements in InnoDB" section now states:

"When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s."

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Thank you for the bug report.