USA - Toll Free: +1-866-221-0634
USA - From abroad: +1-408-701-9009
USA - Subscription Renewals: +1-866-830-4410
Latin America: +1 512 535 7751
UK: +44 845 399 1124
Ireland: +353 1 6919191
Germany: +49 89 420 95 98 95
France: +33 1 70 38 72 00
Sweden: +46 730 207 871
Benelux: +358 50 5710 528
Italy: +39 06-99268193
Israel: +358 50 5710 528
Spain & Portugal: + 34 933905461
Other EMEA countries: +353 1 6919191
Asia Pacific: +81 3 5918 7507
Learn about new MySQL releases, technical articles, events and more.
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.
