Bug #79551 "select * from (select * from u1 ) u2 for update" is not locking rows in u1
Submitted: 8 Dec 2015 3:55 Modified: 17 Dec 2015 15:38
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[8 Dec 2015 3:55] Su Dylan
Description:
Output:
=======
>> connection 0

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> drop table if exists u1;
create table u1(i int primary key, j int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table u1(i int primary key, j int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into u1 values(1,1);
Query OK, 1 row affected (0.00 sec)

>> connection 1
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from (select * from u1 ) as u2 for update;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+
1 row in set (0.00 sec)

>> Connection 2

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> delete from u1;
Query OK, 1 row affected (0.00 sec)

Problem:
========
"delete from u1" is expected to fail for lock timeout (1205).
I didn't find the related documentation for the locking strategy for "select .. for (subquery) for update". If I missed anything, thanks for directing me to the doc link.

How to repeat:
>> connection 0
drop table if exists u1;
create table u1(i int primary key, j int);
insert into u1 values(1,1);

>> connection 1
set autocommit = 0;
begin;
select * from (select * from u1 ) as u2 for update;

>> connection 2
set autocommit = 0;
begin;
delete from u1;

Suggested fix:
"delete from u1" is expected to fail for lock timeout (1205).
I didn't find the related documentation for the locking strategy for "select .. for (subquery) for update". If I missed anything, thanks for directing me to the doc link.
[17 Dec 2015 15:38] MySQL Verification Team
Hi,

Thank you for your report, but it is not a bug .....

Why is this "Not a Bug". 

It is actually quite simple. You are using a nested query in the FROM clause, or as we call it "derived tables". This query is the following one:

.....  FROM (select * from u1) as u2 ....

Nested queries in the FROM clause are almost always materialized. Materialization means that they are converted into the temporary table. Locks do not work on the temporary table, as temporary table belongs ONLY to the connection that issued the query. Hence, why to lock a table that only one user can use ????

Our documentation does not cover your case specifically. There is no chapter that explains why the query as the one you used does not lead to row locking. However, everything is almost fully explained in the following three chapters. They fully cover my explanation, as I expounded it above:

1. http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html
2. http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
3. 8.2.1.18.2 .... most of all chapter "Optimizing Subqueries with Subquery Materialization"

You can also read a general chapter on temporary tables, where you can find that temporary tables are visible only by the connection that produced them.

I hope that our extensive documentation on the matter covers your questions.