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: | |
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
[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.