この記事はMySQL Casual Advent Calendar 2013 3日目の記事です。
はじめに
以前にSELECT ... FOR UPDATEとロックの挙動 - walf443's blogの記事にTwitterで少し言及したんですが、それの補足というか、InnoDBのロックの範囲について僕はこう理解していますよという話です。
MySQLといえば、InnoDBをネットワークサーバとして使うためのフレームワークであり、SQLはInnoDBのインデックスにアクセスするためのDSLといっても過言ではないでしょう。
InnoDBのロックとはつまるところインデックス行のロックなので、InnoDBのロックの範囲を理解するためにInnoDBのインデックスについて少し前置きしておきます(だいぶ端折ったけど長くなった…)。
クラスタインデックスとセカンダリインデックス
すでにInnoDBのインデックスについてよく知ってるという人はここは飛ばして大丈夫です！
ちょっと予習しておきたい人は以下の記事を参考にしてください。
- 漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！
- MySQLでインデックスを使って高速化するならCovering Indexが使えそう - (ﾟ∀ﾟ)o彡 sasata299's blog
今回の説明に必要そうなところを要約すると
- 主キーがあるテーブルは主キーがクラスタインデックスになる
- セカンダリインデックスのリーフにはクラスタインデックスのキー値が保存される
- セカンダリインデックスからデータを参照するには保存されてるキー値からクラスタインデックスを参照する
こんなところ。
ロックの範囲の種類
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.htmlから引用すると、InnoDBには3種類のロックがあります。
Record lock: This is a lock on an index record.
Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
ざっくりいうと
- レコードロック: 単一インデックスレコードのロック
- ギャップロック: インデックスレコード間のギャップのロック、先頭のインデックスレコードの前や末尾のインデックスレコードの後のギャップのロック
- ネクストキーロック: レコードロックとそのレコードの直前のギャップロックの組み合わせ
という感じになります。
InnoDBでインデックスを走査するとき、遭遇したインデックスレコードをロックしていく。なのでロックする範囲を知るにはどのインデックスをどの値からどっちに(ASCかDESCか)どこまで走査するかが重要になる。
あとInnoDBのデフォルトの分離レベル(REPEATABLE-READ)ではロックの単位はネクストキーロックが基本で、あるインデックスに値 10、11、13、20 が含まれているときのネクストキーロックの区間は以下のような感じで、基本的にこの区間単位でロックすることになる。
(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)
ネクストキーロック
ところで、ネクストキーロックというとsh2さんのMySQL InnoDBのネクストキーロック おさらい - SH2の日記の記事が有名ですよね。この、ひとつ先のインデックスレコードまでロックするのもネクストキーロックと呼ぶし、レコードロックとその直前のギャップロックの組み合わせもネクストキーロックと書いてるし、議論するときにはどちらの意味で使ってるのか文脈読み取れる社会性が必要そうです(今回はレコードロックとその直前のギャップロックの組み合わせの意味で使います)。
ロックの範囲を確認する
長くなったけどこのぐらい押さえておけば大丈夫だと思います！
トランザクションがブロックする、ブロックしないというのは、つまるところロックの範囲が被ったか被ってないかなので、walf443さんの記事にあるのと同じ感じのテーブルでロックの範囲を確認してみます。
CREATE TABLE `mysqlcasual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) NOT NULL, `col2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_col1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `mysqlcasual`(`col1`) VALUES (2),(4),(6),(8),(10),(12),(14),(16); SELECT * FROM mysqlcasual; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | 2 | 0 | | 2 | 4 | 0 | | 3 | 6 | 0 | | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | +----+------+------+
ロックの範囲を確認するにはinnodb_lock_monitorを有効にするとSHOW ENGINE INNODB STATUSのTRANSACTIONSセクションに詳細なロック情報が出力されるようになります。
CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;
これでロックの範囲が確認できる、予定だったんですがちょっと思ってた通りの出力にならないんで簡単な例だけやりたいと思います！*1
BEGIN; SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;
これでTRANSACTIONSセクションを見ると
RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000002; asc ;; RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000003; asc ;;
わかるところだけ読むとlock mode Sってのが共有ロック(でネクストキーロック)、locks gap before recがギャップロックなのでロックの範囲はidx_col1上の
(2, 4] (4, 6)
の範囲でidx_col1しか読んでないのでカバリングインデックスになってるってやつです。
クエリを以下に変えてインデックスに含まれないカラムを加えると
BEGIN; SELECT id, col1, col2 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;
TRANSACTIONSセクションに以下のインデックスレコードも加わります。
RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028790 lock mode S locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 0000000fb273; asc s;; 2: len 7; hex dd000001ec011d; asc ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000000; asc ;;
locks rec but not gapってのがレコードロックです。
col2の値を取得するためにクラスタインデックスにアクセスしてしまってカバリングインデックスが効いてないのがわかります。
また、定義してない謎のフィールドがあるように見えますがInnoDBの行の構造についてはMySQL: Welcomeに書いてあるので気になる人はそっち見てください。
次は排他ロックの場合。
BEGIN; SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 FOR UPDATE;
更新するつもり(lock_mode X)のときはカバリングインデックスになっていてもクラスタインデックスもレコードロックします。
RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000002; asc ;; RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 0000000fb273; asc s;; 2: len 7; hex dd000001ec011d; asc ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000000; asc ;; RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000003; asc ;;
これらは分離レベルREPEATABLE-READのときの挙動で、READ-COMMITTEDにするとギャップロックが無効になります。
RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000002; asc ;; RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 0000000fb273; asc s;; 2: len 7; hex dd000001ec011d; asc ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000000; asc ;;
この状態だとギャップに対してINSERTできるのでファントムリードが起きる状態になります。
さいごに
innodb_lock_monitorは大量にログを吐くので使い終わったら消しておきましょう。
DROP TABLE innodb_lock_monitor;
明日(12/4)はdo_aki@殿堂入りさんです！
