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

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

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

この広告は、90日以上更新していないブログに表示しています。

2018-07-21

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

(注記) 去年書いて下書き状態になってた記事をそのまま公開した。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 さんが書いてるとおりの結論ではある。)

参考ページ

doc/innodb.md at master · ichirin2501/doc · GitHub :MySQL のロックの挙動がいろいろ書かれている。非常に参考になる。
漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点 : REPEATABLE READ で参照系が一貫性のある読み取りになる話。 (あるいは Locking Read は一貫性のある読み取りにはならない話。)
世界の何処かで MySQL（InnoDB）の REPEATABLE READ に嵌る人を1人でも減らすために - KAYAC engineers' blog : こちらも REPEATABLE READ における Locking Read が一貫性のある読み取りにはならない話。
kamipo TRADITIONALでは防げないINSERT IGNOREという名の化け物 | おそらくはそれさえも平凡な日々 :INSERT IGNORE は良くないという話と、INSERT or UPDATE の方法について。ちなみに『DELETE してINSERT』は、REPEATABLE READ で対象の行が存在しない場合 (DELETE で 1 件も削除されないとき) に、デッドロックが発生しうる*4 気がする。

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

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

ロックの種類

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

MySQL :: MySQL 5.7 Reference Manual :: 14.5.1 InnoDB Locking

インテンションロック

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

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

インデックスに対するロック

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

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

Insert Intention Locks

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

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