Newsletters older than 6 months may have links that are out of date. Please use the Search to check for updated links.
Is row locking really necessary in a multi-table update, where some of the tables are only read during the update. Couldn't you speed up the updates, if the row locking would not be used?
Consider the example below:
....
mysql> create table table1(a int, b int) type = innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table table2(a int, b int) type = innodb;
Query OK, 0 rows affected (0.50 sec)
mysql> insert into table1 values (10, 20);
Query OK, 1 row affected (2.68 sec)
mysql> insert into table2 values (10, 20);
Query OK, 1 row affected (2.76 sec)
mysql> update table1, table2 set table1.b = 100 where table1.a =
table2.a;
Query OK, 1 row affected (48.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0
....
If we do not lock rows in table2, then the changes to table1 are based on an old consistent snapshot of table2. If that snapshot is very old, we may get really unexpected update results. So it is not good idea to set a trap to users by removing the row locks on table2.
When you are doing multi-table updates, you might run into deadlocks.
Deadlocks are a problem that needs to be taken into account, when using row locking and transactions. The link http://www.innodb.com/ibman.html#Cope_with_deadlocks contains some help in avoiding them.