ひだまりソケットは壊れない

ソフトウェア開発に関する話を書きます。 最近は主に Android アプリ、Windows アプリ (UWP アプリ)、Java 関係です。

まじめなことを書くつもりでやっています。 適当なことは 「一角獣は夜に啼く」 に書いています。

MySQL (InnoDB) とトランザクション分離レベル・ロック

MySQL SQL データベース

(注記) 去年書いて下書き状態になってた記事をそのまま公開した。 MySQL 8.0 公開前に書いた内容なので MySQL 5.7 について言及しているが、多分 MySQL 8.0 でも基本的には変わらない気がする。

背景・目的

DB 上に指定の ID の行が存在していれば UPDATE して、存在していなければ INSERT したい、ということは web アプリケーションを書いているとよくあること。 SQL:2003 標準には MERGE が導入されていて、Oracle Database なんかだと MERGE を使用できるようだが、MySQL では使えない (バージョン 5.7 時点)。 (代わりに INSERT ... ON DUPLICATE KEY UPDATE がある。)

また、複数のテーブルで 1 つの集約 *1 を表現していて、集約の更新時に不整合が起きないようにロックをかけたいということも多い。

といったあたりで、MySQL (InnoDB) における参照や挿入、更新とトランザクション分離レベルやロックについて調べたのでまとめておく。 (初心者が調べてみたぐらいの内容なので、指摘やコメント等頂けると嬉しいです！)

調べた結果どうしたらいいかの個人的見解

いろいろ調べた結論としては、個人的には以下のような感じがいいのかなーと思っている。

  • 参照系ではトランザクション分離レベルを REPEATABLE READ にする。 → 参照だけであれば一貫性のある読み取りができる。
    • REPEATABLE READ で一貫性のある読み取りができるのは InnoDB の実装に依存した挙動で SQL 仕様としてはファントムリードが起こり得るので、実装依存にしてよいと判断するかどうかは状況次第だと思うけども、多くの場合はそうするのが開発コスト的には一番安い気がする。
  • 単一の集約に対する更新系 (新しい集約の新規作成や既存の集約の更新; INSERT or UPDATE) では、トランザクション分離レベルを READ COMMITTED にして *2、集約ルート (に対応するテーブルの行) の存在確認を SELECT FOR UPDATE で行う。
    • 存在すればそのままロックを保持して更新処理を行う。 → 集約ルートに対する排他ロックを獲得しているので、他のトランザクションによる更新と衝突しない。
    • 存在しなければ INSERT する。 Duplicate error になったら (すなわち同時に実行されていた他のトランザクションによる挿入が行われた場合は) トランザクション自体を再開してやり直すか、エラーということでそのまま処理を終了する。 → こちらも挿入に成功すれば排他ロックを獲得するので、そのまま処理を進めて良い。 Duplicate error になった場合には対象行の共有ロックを獲得してしまうため、やり直す場合にはトランザクション自体をやり直さなければならない *3。
      • トランザクション自体のやり直しをどう設計するのかは難しいけど、トランザクションの扱いをアプリケーションレイヤの責務にすることが多いと思うので、アプリケーションレイヤでやるのがいいのかも。 他の方法として、INSERT ON DUPLICATE KEY UPDATE を使うとかは有り。
    • 実装依存でも良ければ INSERT ... ON DUPLICATE KEY UPDATE で良い場面が多そう。
    • パフォーマンスを気にしなくてもいいならトランザクション分離レベルを SERIALIZABLE にするとかも有りかもしれない。
    • (だいたい songmu さんが書いてるとおりの結論ではある。)

参考ページ

MySQL (InnoDB) のロックについて

以下、自分が知らなくて調べた話をだらだら書いておく。

ロックの種類

MySQL 5.7 の英語ドキュメントにまとまっている。

インテンションロック

特殊なテーブルレベルのロックとしてインテンションロックがある。 これらはテーブルレベル・行レベルといった複数粒度のロックの共存のために存在するっぽい。

  • インテンション共有 (Intention shared; IS) ロックは、他のトランザクションによるテーブルレベルの排他ロックと衝突する。 (行レベルの共有ロックの前に獲得される。)
  • インテンション排他 (Intention exclusive; IX) ロックは、他のトランザクションによるテーブルレベルの共有ロックや排他ロックと衝突する。 (行レベルの排他ロックの前に獲得される。)
インデックスに対するロック

InnoDB では、インデックスに対するロックによって行レベルロックを実現している *5。 MySQL の公式ドキュメントやかみぽさんの解説がわかりやすい。

  • レコードロック (Record Locks) : インデックスレコードに対するロック。
  • ギャップロック (Gap Locks) : インデックスレコード間の隙間 (gap) に対するロック。 隙間は、複数のインデックス値にかかることもあるし、単一のインデックス値や空のところにさえもかかりうる *6。 ギャップロックは、他のトランザクションが同じ隙間のギャップロックを獲得することは防がない。 対象の隙間へのレコードの挿入のみが防がれる。 (つまり、共有ギャップロック (gap S-lock) も排他ギャップロック (gap X-lock) も効果は同じ。)
  • ネクストキーロック (Next-Key Locks) : レコードロックと、そのレコードの前の隙間に対するギャップロックの組み合わせ。
Insert Intention Locks

INSERT 操作時に、行の挿入前に獲得されるギャップロックの一種として、挿入インテンションロック (Insert Intention Locks) がある。

『MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary』 におけるデッドロックは、別のトランザクションが行を挿入して獲得した排他レコードロックと、挿入しようとしている行のための挿入インテンションロックの衝突が 2 箇所で起こってしまう、ということなのかな？ と思ったりしたけど、InnoDB monitor の出力を見た感じでは共有レコードロック待ちになってたので挿入インテンションロック待ちとは違ってそうだった。 挿入インテンションロックは純粋にギャップロックとのみ衝突するもので、レコードロックとは衝突しない模様。

ギャップロックとファントム行、あるいはデッドロック

ファントムリードを防ぐために

ギャップロックと挿入インテンションロックのデッドロック : mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する | ++頭道++
mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する | ++頭道++ → この人の解釈では DELETE により指定の ID に対するロックができる (存在しないレコードに対するロックという解釈をしている) となっているが、私の手元の MySQL 5.7.20 で試した限りでは、存在しない ID を指定しての DELETE でギャップロックが獲得されていた。

下書きはココで終わっていた

多分他にも書きたいことがありそうだったけどもはや何も覚えてないのでこのまま公開する。

*1:DDD でいうところの集約。 Aggregates。

*2:この理由は主にギャップロック無効化のため。 ギャップロック有効だとデッドロックしうる; REPEATABLE READ でもギャップロック無効化できるしそうする方がいいのかもしれない

*3:そうしなければ、複数トランザクションで同一行に対する duplicate error が起こった場合にトランザクションをやり直さないと排他ロックの獲得ができず、デッドロックになる

*4:複数のトランザクションが DELETE するとそれぞれのトランザクションがギャップロックを獲得し、その後の INSERT がそれぞれ待ち状態になってしまう。

*5:という理解であってるよね？

*6:空のインデックスでも全体に対するギャップロックがあり得る、ってことかな？