livedoor
MySQL にはよく使われるストレージエンジンとして MyISAM と InnoDB がありますが、違いの一つとしてロックの挙動が挙げられます。MyISAM はテーブルロック、InnoDB は行ロックが掛かるというのは有名な話じゃないかと。
ただ、最近知ったのですが、InnoDB だとしても必ずしも行ロックになるわけではなく、テーブルロックになる場合もあるようですね。。このことについて手元の MySQL 5.1.26RC で簡単ですが検証してみます。サンプルとして使うテーブルはこちら。
サンプルデータとしてこのようなデータを用意しました。で、カラム c2 にはインデックスを張ります（カラム c1 はインデックス無し）。
これで準備は完了です。まずは primary key に対しての SQL でどういうロックが掛かるのか見てみましょう。まず、ある primary key に対して update します（コネクション１）。その後、別のコネクションで接続し、こちらでは別の primary key に対して update します（コネクション２）。このとき、コネクション１の処理は行ロックとなるため、コネクション２ではすぐに処理が行われます。
これは、インデックスを張った c2 でも全く同様でした。では、インデックスを張っていない c1 ではどうでしょうか？？
この場合には、ロック待ち時間の上限（ innodb_lock_wait_timeout で設定された秒数。デフォルトでは 50 秒）を超えて強制ロールバックされるか、もしくはコネクション１を明示的に commit or rollback するまで処理が待たされます。
これ、最初は「インデックスを張らないと行ロックにならないの？」とか思ったんですが、そういうわけではないようです。試しに c1 にユニーク制約を貼ってみたら行ロックされることを確認しました。
というわけで、InnoDB であってもユニーク制約 or インデックスが張られているカラムで検索した場合以外はテーブルロックになってしまうようです。これは注意しないと思わぬところでテーブルロックになってしまって大変なことになりそう！
・ユニーク制約 or インデックスが貼られているカラムで検索した場合、行ロック
・それ以外のカラムで検索した場合、テーブルロック
というわけで、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 を使う方は行ロック/テーブルロックの発生する条件に気を付けてください ﾉｼ
