MySQL InnoDB locks on joined rows

Question

Does "SELECT ... FOR UPDATE" lock joined rows in MySQL?

If so, is it possible to disable this behaviour?

There is nothing about this in the documentation. I've seen that Oracle supports "SELECT ... FOR UPDATE OF table_name" where table_name is the main table or one of the joined tables for which the affected rows will be locked, but I've never seen this mentioned in context with MySQL.

Answer 1

See this MySQL doc page. It says:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.

and:

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.

"scanned rows" refers to rows from any of the tables that are used in the join.