MySQL InnoDB - During a locking read with joined tables, will the joined rows be locked even if no columns are SELECTed?

Question

I know this is very similar to other questions (e.g., this one), but I feel that I haven't found an answer that includes the specific question of how the SELECT part of the query affects the lock.

Specifically, I have a parent-child relationship between two tables, enforced via a foreign key on the child table that references the primary key of the parent. In a piece of software I'm working on, I want to take a "path" (parent key + child key) and return the child row only if it's a valid "path". So, the query will look something like:

SELECT Child.*
FROM Child
INNER JOIN Parent ON Parent.id = Child.parent_id
WHERE Parent.id = ? AND Child.id = ?
FOR UPDATE

I understand from the other question that I linked, that the Parent row would be locked if I did a SELECT *. But I'm not sure if that changes when I don't actually return any of the Parent row's columns.

The documentation says (emphasis mine):

For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution.

But I can kind of interpret that multiple ways. What does it mean to "qualify" for inclusion in the result set? Does the fact that I didn't include any of the columns in my SELECT "disqualify" it? Or does "qualify" only refer to JOIN...ON, WHERE, and HAVING clauses?

Thank you!

Answer 1

Locks affect examined rows, whether its columns are part of the result or not.

You may have the same question about a query that doesn't have a JOIN:

SELECT ... FROM MyTable WHERE A = 123 AND B = 456;

Suppose there's an index on A alone, and no index on B. The condition on A matches 100 rows, but filtering for the subset that also matches the condition on B, the ultimate result has only 3 rows. But all 100 rows matching the condition on A are locked. These are the examined rows. All of them are locked even though 97 of them aren't in the final result.

Likewise, the fact that expressions in your query reference the columns of Parent, that makes the respective rows examined rows.

---

Demo:

mysql> create table MyTable (id serial primary key, a int, key (a), b int);

mysql> insert into mytable set a = 123, b = 456;

mysql> insert into mytable set a = 124, b = 457;

mysql> start transaction;

mysql> select * from mytable where a = 123 and b = 456 for update;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |  123 |  456 |
+----+------+------+

Now according to your reading of the documentation, the row with a=123, b=457 should NOT be locked, right?

So in a second window, I try to read it with a locking read:

mysql> select * from mytable where a = 123 and b = 457 lock in share mode;
... waits for 50 seconds ...
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The rows locked by the locking read in the first window include all the examined rows, that is, all rows matched by the index on a, even though some of them are excluded by a non-indexed condition on b.