3

When issuing an UPDATE statement in MySQL (using InnoDB and the REPEATABLE READ isolation level) containing a subquery like this:

UPDATE bar INNER JOIN (SELECT i1, i2 FROM foo) inner 
   ON bar.b1 = inner.i1 
   SET bar.b2 = inner.i2)

Will the InnoDB engine lock only all affected rows from the bar table, or will all accessed rows in the foo table be (shared or exclusively) locked as well for the duration of the entire query?

Is there any difference for JOINs instead of subqueries?

lxgr
  • 3,719
  • 7
  • 31
  • 46
  • There seems to be no need for the subquery in _that_ example; perhaps your real query was more complex? – Rick James May 27 '16 at 17:05
  • Yes, this exact query was just an example. – lxgr May 28 '16 at 06:49
  • But can you avoid the subquery in the real query? – Rick James May 28 '16 at 07:06
  • Eventually I split the query into two and used a temporary table to avoid locking problems. The shared locks would however also have occured when avoiding the subquery with an UPDATE ... SET or UPDATE ... JOIN statement, as the first link in my answer explains. – lxgr May 28 '16 at 07:13

1 Answers1

3

It seems like InnoDB will indeed acquire shared locks for the duration of such queries. I've verified this using a SLEEP statement within the subquery and a simultaneous update from another session.

I was able to work around this using temporary tables instead of subqueries which did not show this behavior on my test case.

Community
  • 1
  • 1
lxgr
  • 3,719
  • 7
  • 31
  • 46