タイトルのとおりですが、InnoDB は行ロックをかける事で有名ですが、行ロックをかけるには必ず INDEX が必要になります。
今回はその事について簡単にまとめようと思います。
行ロックがかからない例
まずは、行ロックがかからない例を書いてみます。
下記のような単純な test テーブルを用意します。
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
+----+--------+
コンソール A でトランザクションを開始して、number = 1 の行を FOR UPDATE で取得します。
コンソール A
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE number = 1 FOR UPDATE;
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
+----+--------+
1 row in set (0.00 sec)
次にコンソールBを立ち上げて、コンソールAがトランザクションを終了するまえに、number = 2 の行を FOR UPDATE で取得します。
コンソール B
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE number = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
取得できませんでした。
これは、コンソールA のクエリによってテーブルロックがかかっているため、コンソールBが影響を受けたわけです。
原因
InnoDB は INDEX 上のレコードをロックすることでレコードロックを行います。
その為、今回の用に INDEX が貼られていない場合、ロックをかけるべきレコードが特定できないので、テーブル自体にロックをかけてしまいます。
行ロックをかけてみる
というわけで、number カラムに INDEX を貼って、行ロックがかかるか試してます。
まずは number カラムにINDEX を貼ります。
mysql> CREATE INDEX number_index ON test (number);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
では、この状態で先ほどと同じ事をやってみます。
コンソール A
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE number = 1 FOR UPDATE;
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
+----+--------+
1 row in set (0.00 sec)
コンソールAがトランザクションを終了するまえに、number = 2 の行を FOR UPDATE で取得します。
コンソール B
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE number = 2 FOR UPDATE;
+----+--------+
| id | number |
+----+--------+
| 2 | 2 |
+----+--------+
1 row in set (0.00 sec)
今度は、コンソールBでも結果が返ってきました。
うまく行ロックができたようです。
まとめ
- InnoDB は INDEX 上のレコードをロックすることでレコードロックを行う
- INDEX を貼っていないとテーブルロックがかかる