CODEWITHSUNDEEP
mysqlsql-updateinnodbdatabase-locking
lxgr
lxgr

Reputation: 3797

Will an UPDATE ... WHERE transaction in MySQL lock rows in other tables referenced from subqueries?

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?

Upvotes: 3

Views: 1439

Answers (1)

lxgr
lxgr

Reputation: 3797

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.

Upvotes: 3

Related Questions