Mysql(InnoDB)のロックの変化について

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

最近、MysqlでUpdate を行った際、更新対象件数によって、書き込みロックが行ロックからテーブルロックに変化することがあったので

その原因と対処方法について書こうと思います。

環境は下記の通りです。

OS: CentOS 5.6, MacOSX 10.7.3
Mysql 5.1(InnoDB)

↓のテーブルに、1000件レコードが存在すると仮定して説明していきます。

CREATE TABLE `sample_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ロックが行ロックからテーブルロックに変化する現象が発生するのは、

以下のSQL文です。

UPDATE sample_table SET val = "hoge" WHERE id in (100件分のid); => 行ロック
UPDATE sample_table SET val = "hoge" WHERE id in (500件分のid); => テーブルロック

ロックの変化の原因を探っていると、EXPLAINの結果に違いがあることがわかりました。

対象件数が100件の場合にはtypeがrangeなのに対し

対象件数が500件の場合にはtypeがALLになり、インデックスが使われていません。

対象件数 100件
EXPLAIN SELECT * FROM sample_table WHERE id IN (100件分のid);
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | products         | range | PRIMARY       | PRIMARY | 4       | NULL |  500 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+

対象件数 500件
EXPLAIN SELECT * FROM sample_table WHERE id IN (500件分のid);
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | products         | ALL  | PRIMARY       | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+

このフルテーブルスキャンが原因でテーブルロックが発生していました。

ロックの変化についてMysqlリファレンスを調べたところ、下記のような記述がありました。

 MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意し
てください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% 
を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要な
シークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなり
ます）。 ただしこのクエリに、レコードの一部のみを取り出す LIMITが使用されている場
合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを
使用します。

上記を参考に、LIMITを付け加えることでインデックスが使用され、テーブルロックになることが防げました。しかし、速度が犠牲になる可能性があるため、使いどころは考える必要がありそうです。