Bug #90693 SELECT..FOR UPDATE does not lock rows from derived tables (subqueries)
Submitted: 30 Apr 2018 14:49 Modified: 13 Jun 2018 16:08
Reporter: Mike Bayer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[30 Apr 2018 14:49] Mike Bayer
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.
[30 Apr 2018 15:16] Sinisa Milivojevic
Hi!

The behaviour that you describe corresponds to the SQL standards.

However, it is not described in our manual, so I am verifying it as a documentation bug.
[30 Apr 2018 16:08] Mike Bayer
so.... the claim is then that Postgresql and Oracle *don't* conform to the SQL standard?  or that the SQL standard is not specific here?   I can go try to look it up but if you can show specifics that would help.
[13 Jun 2018 16:06] Daniel Price
Posted by developer:
 
Another example:

CREATE TABLE t1 (
     c1 INT
     ) engine='InnoDB';
	 
CREATE TABLE t2 (
     c1 INT
     ) engine='InnoDB';

INSERT INTO t1 (c1) VALUES (1);
INSERT INTO t2 (c1) VALUES (1);

START TRANSACTION;
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

START TRANSACTION;
UPDATE t2 SET c1=2; 

For the subquery to lock, use: 

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
[13 Jun 2018 16:08] Daniel Price
Posted by developer:
 
The following page was updated:
https://dev.mysql.com/doc/refman/5.7/en/subqueries.html

Changes should appear online soon.

Thank you for the bug report.