Description:
A SELECT statement which specifies FOR UPDATE at the outermost level, which also refers to a table inside of a subquery will not lock the rows of that table. This does not appear to be documented, or at least is not clear at https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html what "For index records the search encounters, locks the rows and any associated index entries" means. This behavior differs from the behavior of other major databases like Postgresql and Oracle.
How to repeat:
Using two consoles:
# mysql -u scott -p --prompt "MySQL / console A > " test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62245
Server version: 5.7.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL / console A > CREATE TABLE a (
-> id INTEGER NOT NULL,
-> x INTEGER,
-> y INTEGER,
-> PRIMARY KEY (id)
-> ) engine='InnoDB';
Query OK, 0 rows affected (0.04 sec)
MySQL / console A > CREATE TABLE b (
-> id INTEGER NOT NULL,
-> a_id INTEGER,
-> x INTEGER,
-> y INTEGER,
-> PRIMARY KEY (id),
-> FOREIGN KEY(a_id) REFERENCES a (id)
-> ) engine='InnoDB';
Query OK, 0 rows affected (0.04 sec)
MySQL / console A > INSERT INTO a (id, x, y) VALUES (1, 5, 5);
Query OK, 1 row affected (0.00 sec)
MySQL / console A > INSERT INTO b (id, a_id, x, y) VALUES (1, 1, 4, 4);
Query OK, 1 row affected (0.00 sec)
# first, demonstrate a plain JOIN of the two tables
MySQL / console A > BEGIN;
Query OK, 0 rows affected (0.00 sec)
MySQL / console A > SELECT a.*, b.* FROM a JOIN b ON a.id=b.a_id FOR UPDATE;
+----+------+------+----+------+------+------+
| id | x | y | id | a_id | x | y |
+----+------+------+----+------+------+------+
| 1 | 5 | 5 | 1 | 1 | 4 | 4 |
+----+------+------+----+------+------+------+
1 row in set (0.00 sec)
# now move to console B, demonstrate that an UPDATE on "a" hangs as expected:
# mysql -u scott -p --prompt "MySQL / console B > " test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62246
Server version: 5.7.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL / console B > BEGIN;
Query OK, 0 rows affected (0.00 sec)
# hangs as expected
MySQL / console B > UPDATE a SET x=10, y=10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
MySQL / console B > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
# back on console A, we emit ROLLBACK to try again
MySQL / console A > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
MySQL / console A > BEGIN;
Query OK, 0 rows affected (0.00 sec)
# now the same query using a subquery for "a"
MySQL / console A > SELECT aa.*, b.* FROM (SELECT * FROM a) AS aa JOIN b ON aa.id=b.a_id FOR UPDATE;
+----+------+------+----+------+------+------+
| id | x | y | id | a_id | x | y |
+----+------+------+----+------+------+------+
| 1 | 5 | 5 | 1 | 1 | 4 | 4 |
+----+------+------+----+------+------+------+
1 row in set (0.00 sec)
# on B, the query proceeds immediately, the row is not locked
MySQL / console B > BEGIN;
Query OK, 0 rows affected (0.00 sec)
MySQL / console B > UPDATE a SET x=10, y=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# the "b" row *is* however locked
MySQL / console B > UPDATE b SET x=10, y=10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
Suggested fix:
current workaround is to add an additional FOR UPDATE to the subquery but this should not be necessary. In particular, Oracle itself disallows this syntax.
