InnoDBで行ロック/テーブルロックになる条件

MySQL にはよく使われるストレージエンジンとして MyISAM と InnoDB がありますが、違いの一つとしてロックの挙動が挙げられます。MyISAM はテーブルロック、InnoDB は行ロックが掛かるというのは有名な話じゃないかと。

ただ、最近知ったのですが、InnoDB だとしても必ずしも行ロックになるわけではなく、テーブルロックになる場合もあるようですね。。このことについて手元の MySQL 5.1.26RC で簡単ですが検証してみます。サンプルとして使うテーブルはこちら。

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

サンプルデータとしてこのようなデータを用意しました。で、カラム c2 にはインデックスを張ります（カラム c1 はインデックス無し）。

+----+------+------+------+
| id | c1   | c2   | val  |
+----+------+------+------+
|  1 |   64 |  654 | NULL | 
|  2 |  234 |   53 | NULL | 
|  3 | 2435 |  324 | NULL | 
|  4 |    3 |   23 | NULL | 
|  5 |   45 | 3512 | NULL | 
|  6 |  324 |   14 | NULL | 
|  7 |   46 |  658 | NULL | 
|  8 |  654 |  658 | NULL | 
|  9 |   14 |  156 | NULL | 
+----+------+------+------+
9 rows in set (0.06 sec)

ALTER TABLE `lock_sample` ADD INDEX `c2` (`c2`);

これで準備は完了です。まずは primary key に対しての SQL でどういうロックが掛かるのか見てみましょう。まず、ある primary key に対して update します（コネクション１）。その後、別のコネクションで接続し、こちらでは別の primary key に対して update します（コネクション２）。このとき、コネクション１の処理は行ロックとなるため、コネクション２ではすぐに処理が行われます。

mysql1> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql1> UPDATE `lock_sample` SET val = "hoge" WHERE id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--- 別のコネクション

mysql2> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql2> UPDATE `lock_sample` SET val = "fuga" WHERE id = 4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

これは、インデックスを張った c2 でも全く同様でした。では、インデックスを張っていない c1 ではどうでしょうか？？

mysql1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql1> UPDATE `lock_sample` SET val = "foo" WHERE c1 = 45;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--- 別のコネクション

mysql2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> UPDATE `lock_sample` SET val = "bar" WHERE c1 = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded

この場合には、ロック待ち時間の上限（ innodb_lock_wait_timeout で設定された秒数。デフォルトでは 50 秒）を超えて強制ロールバックされるか、もしくはコネクション１を明示的に commit or rollback するまで処理が待たされます。

これ、最初は「インデックスを張らないと行ロックにならないの？」とか思ったんですが、そういうわけではないようです。試しに c1 にユニーク制約を貼ってみたら行ロックされることを確認しました。

ALTER TABLE `lock_sample` ADD UNIQUE (`c1`);

mysql1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql1> UPDATE `lock_sample` SET val = "foo" WHERE c1 = 45;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--- 別のコネクション

mysql2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql2> UPDATE `lock_sample` SET val = "bar" WHERE c1 = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

というわけで、InnoDB であってもユニーク制約 or インデックスが張られているカラムで検索した場合以外はテーブルロックになってしまうようです。これは注意しないと思わぬところでテーブルロックになってしまって大変なことになりそう！

・ユニーク制約 or インデックスが貼られているカラムで検索した場合、行ロック
・それ以外のカラムで検索した場合、テーブルロック

というわけで、InnoDB を使う方は行ロック/テーブルロックの発生する条件に気を付けてくださいﾉｼ

タグ：: mysql; InnoDB; 行ロック

(ﾟ∀ﾟ)o彡 sasata299's blog