0120-33-9096
03-5717-5033 (携帯電話用)
【電話受付時間】
平日 9:00-11:45、13:00-17:00
MySQL の新しいリリース、技術情報、イベントなどの情報が記載されています。
毎月発行される MySQL ニュースレターを購読しませんか?
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.

