MySQL InnoDB locks on joined rows

mysql join transactions innodb locks
Miloš Rašić picture Miloš Rašić · Jul 12, 2011 · Viewed 7.6k times · Source

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

Barmar picture Barmar · Sep 3, 2012

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.

Related questions

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More
How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Read More
INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse …

Read More