Menu
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?
469
To query data from related tables, you often use the join clauses, either inner join or left join. In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update. In this syntax: First, specify the name of the table (t1) that you want to update in the UPDATE clause.
The UPDATE JOIN is a MySQL statement used to perform cross-table updates that means we can update one table using another table with the JOIN clause condition. This query update and alter the data where more than one tables are joined based on PRIMARY Key and FOREIGN Key and a specified join condition.
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement. Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.
And, it only applies to UPDATE statements that are associated with the current row in the cursor. So, the FOR UPDATE has no relationship with INSERT .
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
72
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
