これは インフラ勉強会 Advent Calendar 2018 19 日目の記事です。
書いているうちに日付が変わってしまいました。
同カレンダーの 17 日目に、
という記事を書きましたが、そもそも MySQL（InnoDB）のロックについて何も解説していないので、おそらく MySQL 初心者には全く意味の分からない記事になってしまったと思います（すみません）。
というわけで、すでに語りつくされている内容ですが、先の記事を理解するのに最低限必要になりそうなことを補足していきます。
※MySQL に慣れている人にはいまさら解説不要な内容です。
MySQL（InnoDB）とトランザクション分離レベル
細かいことは説明しませんが、MySQL を含む（トランザクション対応の）RDBMS では、
- トランザクション間のデータの整合性・一貫性確保
と、
- トランザクションの並列処理性能
のバランスを（RDBMS を利用するアプリケーションの目的に合致する形で）取るために、4 つのトランザクション分離レベルが選択できるようになっています。
- READ UNCOMMITTED ※PostgreSQL では READ COMMITTED 相当の動作になります
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
※下に行くほど「整合性・一貫性のレベルは高いが並列処理性能は低い」です。
Oracle、PostgreSQL など一般的な RDBMS ではデフォルトの分離レベルとして READ COMMITTED（コミットされたデータのみ読み取り可能）が採用されていますが、MySQL（InnoDB）では今のところ REPEATABLE READ（反復読み取り）が採用されています。
さらに、通常の REPEATABLE READ ではファントムリード（他のトランザクションが追加したり削除したデータが読み取られる現象）を防げないのですが、MySQL（InnoDB）では通常発生するファントムリードを防ぐ仕組みが備わっています。
ここが、MySQL のロックでハマりやすいポイントです。
MySQL（InnoDB）のロックの種類（主なもの）
テーブルレベルのロックと行レベルのロックがあります。
テーブルレベルのロック
- 14.2.3 InnoDB のロックモード（version 5.6 MySQL 5.6 リファレンスマニュアル）
のインテンションロックに書かれている通り、テーブルロックには
- インテンション共有ロック（IS）
-
SELECT ～ LOCK IN SHARE MODEで設定される
-
- インテンション排他ロック（IX）
-
SELECT ～ FOR UPDATEや更新系の SQL で設定される
-
があります。
「テーブルロック」というと「ロックすると他のトランザクションが（当該テーブルに）一切更新（または参照）できなくなる（待たされる）」というイメージがあるかもしれませんが、そういうわけではなく、先に示したリファレンスマニュアルのマトリクス表で「互換性がある」と示されている組み合わせであれば重複してロックを取ることができます。
結局のところ、これらのテーブルロックを取っただけでは他のトランザクションがブロックされることはほとんどなく（明示的にLOCK TABLES ～ WRITEなどを実行した場合を除いて）、基本的には「（誰かが）テーブル内の行をロック（しようと）していることを示す」ためのものです。
行レベルのロック
基本は 3 種類です。それぞれに共有（S）・排他（X）があります。
- レコードロック：（行に対するロックではなく）インデックス行に対するロック
- ギャップロック：インデックス行が存在しない部分（ギャップ）に対するロック
- インデックス行の間にあるギャップのロック
- 先頭のインデックス行の前にあるギャップのロック
- 末尾のインデックス行の後にあるギャップのロック
- ネクストキーロック：インデックス行に対するレコードロックと、その前にあるギャップに対するロックを組み合わせたもの
ギャップロックは、「インデックス行より前にあるギャップに対するロック」＋「一番大きなインデックス行より後のギャップに対するロック」です。
例えば、主キーidをもつテーブルに、
id=10id=20id=30id=40
の 4 行のレコードがあった場合、主キーidについて、
- 10 未満
- 10 より大きく 20 より小さい
- 20 より大きく 30 より小さい
- 30 より大きく 40 より小さい
と、
- 40 より大きい（～ +∞）
の 5 つのギャップがあります。
このギャップロックを（通常はインデックス行に対するレコードロックとの組み合わせで、ネクストキーロックとして）使うことにより、ファントムリードを防ぐ仕組みになっています。
さらに、ギャップにINSERTを行う場合の処理の並列性を確保するための、
- 挿入インテンションロック（通常、ギャップロック・レコードロックと組み合わせた排他ロックとして使う）
があります。
前述のテーブルで説明すると、例えば 1 つ目のトランザクションがid=15に挿入した場合でも、2 つ目のトランザクションにid=16など「同じギャップに含まれるが重複しないレコード」の挿入を許すものです。
※UPDATE・DELETEとの並列性を保証するものではありませんので、先にUPDATEで排他ロックされた範囲（ギャップ）にINSERTするとUPDATEがCOMMIT／ROLLBACKされるまでブロックされます。
通常、INSERTを行うときには、
- 先に挿入インテンション（排他）ロックを取って
- 続いて
INSERT対象のレコード（排他）ロックを取る
という 2 段階のロック操作を行うようです。
MySQL で（SQL を書くときに）気を付けること
SELECT ～ FOR UPDATEなどで明示的にロックを取ったり、UPDATEなどの更新系の処理を実行する際、以下のような SQL は極力避けるようにしましょう。
- インデックスを使わないもの
-
インデックスに実在しない行を指定するもの
- 例：前述のテーブルで
WHERE id BETWEEN 18 AND 23を指定する（10 超 30 以下の範囲にロックが掛かります）
- 例：前述のテーブルで
-
WHERE id > 10など範囲が「閉じていない」もの
同様に、以下のようなものもできるだけ減らしたほうが良さそうです。
- 広い範囲を指定するもの
- 使うインデックスがユニークではないもの
※どうしてもギャップロックでブロックしたくない／されたくない、というときにはトランザクション分離レベルを READ COMMITTED に下げて SQL を発行する方法もあります（巻き込みUPDATE・DELETEなどに注意。もっとも↓のような事情があるので REPEATABLE READ でも巻き込み事故には注意しないといけないですが）。
MySQL 8.0 では
降順インデックスが作成可能ですが、その場合はギャップロックが「インデックス行の後ろ（値が大きい方向）」に掛かるので、前述のテーブルの主キーを降順に変更した状態でSELECT ～ FOR UPDATEなどにWHERE id BETWEEN 18 AND 23を指定すると、10 以上 30 未満の範囲にロックが掛かります。
また、先の記事の通りdata_locks・data_lock_waits テーブルでロックの状況を確認することができますが、MySQL 8.0 でロック待ちを発生させていないロックの情報が表示されるようになったとはいえ、ロックのタイミングや表示条件などの影響か、ものによっては観察するのが難しいロックもあるようです。
明日、20 日目は tenn25 さんです。