InnoDBのオプティマイザとロックの範囲の関係
MySQLのInnoDBのロックの挙動を色々調べていたのですが、レコードの数によってロックの範囲が変わる現象に頭を悩まされたので、メモがてら少しまとめてみます。
どこまでロックする?
以下のようなテーブルがあるとします。 id列に1〜6までの数値が入っています。
CREATE TABLE t ( `id` int unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO t VALUES (1),(2),(3),(4),(5),(6);
ではidが3より小さなレコードを取得するクエリを投げると、どのレコードをロックするでしょうか。
BEGIN; SELECT id FROM t WHERE id < 3 LOCK IN SHARE MODE;
1〜2のレコードでしょうか。それとも1〜3までロックするでしょうか。
実際にInnoDBロックモニタで確認してみると、実は1〜6全て(supremumレコード含む)に共有ロック(レコードのロック+ギャップロック)がかかっていることがわかります。
(cf. InnoDBロックモニタhttp://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-general-monitor.html
BEGIN; SELECT id FROM t WHERE id < 3 LOCK IN SHARE MODE; SHOW ENGINE INNODB STATUS\G RECORD LOCKS space id 0 page no 45 n bits 80 index `PRIMARY` of table `D`.`t` trx id 0 63305 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000001; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 80000000340110; asc 4 ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000002; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 8000000034011d; asc 4 ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000003; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 8000000034012a; asc 4 *;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000004; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 80000000340137; asc 4 7;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000005; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 80000000340144; asc 4 D;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000006; asc ;; 1: len 6; hex 00000000f748; asc H;; 2: len 7; hex 80000000340151; asc 4 Q;;
なぜ全レコードにロックが掛かってしまうか
感覚的には1,2,3のみロックが掛かるように思えるので、不思議な挙動です。 自分も小一時間悩んだんですが、クエリの実行計画を見ると理由が推測できました。
EXPLAIN SELECT id FROM t WHERE id < 3 LOCK IN SHARE MODE\G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: t > type: index > possible_keys: PRIMARY > key: PRIMARY > key_len: 4 > ref: NULL > rows: 4 > Extra: Using where; Using index
type = index なのでインデックスのフルスキャンです。 つまりInnoDBは全てのレコードをフルスキャンしMySQLサーバに渡し、MySQLサーバ側がWHERE句のフィルタ(id < 3)を処理します。 結果、InnoDBは全てのレコードにロックを掛けてしまったということです。
データ件数が多い場合には?
次にデータを1000件投入して同じことをやってみます。
データの投入には、ダミーデータを自動生成する拙作のdatagen_from_ddl(1)
参照 を使いました。
$ echo 'CREATE TABLE t ~~~;' | datagen_from_ddl -n 1000 | mysql -u root testdb
同様にSELECT文を実行すると、今度は予想通り1〜3までのレコードをロックしました。
BEGIN; SELECT id FROM t WHERE id < 3 LOCK IN SHARE MODE; SHOW ENGINE INNODB STATUS\G RECORD LOCKS space id 0 page no 7424 n bits 744 index `PRIMARY` of table `D`.`t` trx id 0 63312 lock mode S Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000001; asc ;; 1: len 6; hex 00000000f74f; asc O;; 2: len 7; hex 80000000340110; asc 4 ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000002; asc ;; 1: len 6; hex 00000000f74f; asc O;; 2: len 7; hex 8000000034011d; asc 4 ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 00000003; asc ;; 1: len 6; hex 00000000f74f; asc O;; 2: len 7; hex 8000000034012a; asc 4 *;;
EXPLAINを見てみます。
EXPLAIN SELECT id FROM t WHERE id < 3 LOCK IN SHARE MODE\G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: t > type: range > possible_keys: PRIMARY > key: PRIMARY > key_len: 4 > ref: NULL > rows: 2 > Extra: Using where; Using index
type = range なのでインデックスを用いた範囲検索です。 つまり、id < 3がfalseになるまでInnoDBはレコードを取得 & ロックをかけていき、最終的に3のレコードを読み終了します。 よってロックされるのは1〜3のレコードのみで済みました。
まとめ
結局の所、ロックされる範囲はInnoDBがどこまでレコードを読んだかが決め手になります。 つまりロックの挙動を見る時は、オプティマイザがどうクエリを処理するかも同時に確認する必要があるという話でした。