MYSQL innoDB SELECT FOR UPDATE with LEFT JOIN

Question

Can somebody please tell me what happens when you LEFT JOIN tables for a SELECT FOR UPDATE using an innoDB storage engine.

Do all the rows from all the joined tables get locked, or is only the primary table rows?

For example, if I do..

SELECT userID, countryID FROM user LEFT JOIN address USING (userID) WHERE userID = 1 FOR UPDATE

Will the row in the address table also be locked? Or do I need to lock that separately?

score 8 · Accepted Answer · edited May 23 '17 at 12:21

8

SELECT FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. but If autocommit is enabled, the rows matching the specification are not locked.

MySQL InnoDB locks on joined rows

edited May 23 '17 at 12:21

Community

1
1

answered Jun 08 '15 at 16:07

Vatsalya

104
2

MYSQL innoDB SELECT FOR UPDATE with LEFT JOIN

1 Answers1