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.

Fascinating

does the update statement lock the rows in the other tables? I think it should (maybe it depends on the isolation level), as the values in the update may depend on the values on the other table. Do you need to use for update? Maybe lock in share mode is what you are looking for? Anyway, I'm personally interested in this answer, but maybe the only way to tell is to test this. – Palmerpalmerston

I've done some research with the enhanced information provided by InnoDB plugin and by the number of rows my transactions are locking and the behaviour of my application I would say MySQL is locking all the joined rows in addition to the rows of the main table. I haven't been able to find a query clause or an InnoDB option that would change this, so it seems we are stuck with this default behaviour for now. – Buccinator

I found the answer in the MySQL Documentation – Grapevine

PLEASE CAN ANYONE REVEAL AN EXPLICIT ANSWER? – Reynaud

Short answer... yes. It is implied this in the MySQL docs when it uses the ... in "SELECT ... FOR UPDATE locks the rows and any associated index entries" – Amphithecium

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.

Kwangtung

Answer 2

SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows.

and then

If autocommit is enabled, the rows matching the specification are not locked.

Does this mySQL doc not provide the answers?

Melina

No, JOIN is not mentioned in the doc. – Buccinator