GROUPエンジニアブログです。InnoDBのinsertとlockの話こんにちは。ECナビでアプリケーションエンジニアをやっている駒崎です。今回はMySQLのInnoDBエンジンにおけるINSERTとロックの挙動について書きたいと思います。はじめにアプリケーションでレコードの重複チェックをしてからINSERTをする。テーブルにはUNIQUE制約をかけてデータ不整合が起きないようにしている。いう仕様はよくあるケースだと思います。いったケースでINSERTしたときにどのような仕組みが働いて重複データを防いでいるのだろう?アプリケーションで重複チェックをしてはいるけどMySQLではどんな挙動をしているんだろう?というのが気になったので調べました。前提知識この記事を読む前に、今回参考にさせていただいた素晴らしい資料をみることをおすすめします!少なくともギャップロック、レコードロック、ネクストキーロックあたりについてはさらっと一読していただくと良いと思います。(この辺りが理解出来ている方は華麗にスルーしていただけると良いと思います)クラスタインデックスについて知れる。いつもお世話になっております。STATUSのTRANSACTIONセクションを利用するため、以下の設定をONにします。今回は単独キーのユニークインデックス(かつ、セカンダリインデックス)の場合を調べます。以下のようなテーブルとデータを準備します。Tx2)が並行動作した場合を想定して、ロックの挙動を観察します。トランザクション(Tx1)でトランザクションを開始し、INSERTをします。STATUSのTRANSACTIONセクションを確認します。IXというテーブルロックがかかっています。これはInnoDBが暗黙的に行うインテンションロックです。IX)とは競合しないことがわかります。今回の記事とはあまり関係ないので詳しい説明はここでは割愛しますが、IX同士は競合しないことだけ覚えて進めていきます。(X)と表記しているところはそれぞれ共有ロック、排他ロックの意味で記載します。別のトランザクション(Tx2)でINSERTをしてみます。TRANSACTIONセクションを見てみると情報が大量に出てきます。トランザクション毎に情報を整理してみます。Tx1に関する情報を見てみます。IXの他に情報が増えています。gapというのはレコードロックです。最初に見えていませんでしたが、Tx1がINSERTをする際に挿入インテンションギャップロックをかけていました。挿入インテンションギャップロックは、それ同士では通常競合しませんが、同じ行に対する場合のみ競合する特殊なロックです。他のトランザクションで同じ行に対するINSERTがされたため、ukeyの追加する予定のレコード(u1=5)に対するインデックスレコードロック(X)を取得しています。1:というのはロックした行(この場合はインデックスレコード)のデータダンプです。Tx2に関する情報も見てみます。必要な部分のみ抜粋しています。IX同士は競合しないので取得できています。ukey上の挿入インテンションギャップロックでTx1と競合したため、ukey上のu1=5に対するネクストキーロック(S)に変化して待ちになっています。ここまででINSERTが重複した時にどのようなロック制御が発生するのかはなんとなく掴めたと思います。Tx1をロールバックしてみます。Tx1のロック情報はなくなりTx2のロック情報が変化しています。ukey上のネクストキーロック(S)を取得しているように見えますが、レコード情報がわかりません。ukey上のut=6とut=5の前のギャップロック(S)を取得しています。Tx2をロールバックします。今度はTx1でコミットした場合にどのようになるか確認します。先ほどまでと同じ手順でTx2が待機している状態まで進めます。2873がTx1です。Tx1をコミットします。Tx2は以下のようにエラーとなりました。STATUSのTRANSACTIONセクションを確認します。Tx2がukey上のu1=5のレコードに対してネクストキーロック(S)をかけています。INSERTしようとした行が既に存在する場合はネクストキーロック(S)を取得します。Tx2のトランザクションを終了するとロックを開放しました。今回はTx1が先行、Tx2が後行でやりましたが、INSERTの順番が逆になった場合はTx1が失敗するだけです。UPDATEをかけてからINSERTをする場合を見てみます。UPDATE後にINSERTの検証1つめのトランザクション(Tx1)でトランザクションを開始し、u1=5に対して排他ロックをします。STATUSのTRANSACTIONセクションを確認します。ukey上のu1=6の前のギャップロック(X)を取得しています。ユニークキーの場合はプライマリーキーに対するロック範囲と同じようになるのですね。INSERTしてみます。STATUSのTRANSACTIONセクションを確認します。追加行のギャップロックが増えています。次にTx2の処理を進めます。STATUSのTRANSACTIONセクションを確認します。ukey上の追加レコードに対するレコードロック(X)が増えています。ukey上のレコードロック(X)の取得待ちになっています。Tx1をコミットします。存在するレコードに対する排他ロックのため、ukey上のu1=5に対するレコードロックおよび、プライマリインデックスに対するレコードロックになっています。UPDATEで排他制御をするとなんとなく予想通りの動きをしてくれていると思います。トランザクションが並列で実行される場合、必ずしもこの順番で処理がされる保証はありません。UPDATEがほぼ同時に行われる場合も考えられます。UPDATEをした場合、以下の様な状態になります。ギャップロック同士は競合しないため、お互いに同じ範囲にギャップロック(X)を取得しています。その後、どちらのトランザクションがINSERTをした場合でも、挿入インテンションギャップロックがギャップロックと競合するため、INSERTは待ちになります。一方のトランザクションがINSERTをするとデッドロックが発生し、あとからINSERTしたトランザクションはロールバックされます。先にINSERTして残った方のトランザクションの状態は以下のようになるようです。u1=5のギャップロック及びukey上のu1=6に対する挿入インテンションギャップロックを取得しています。ここまで来ればデッドロックは起きません。UPDATEせずにINSERTINSERTで挿入インテンションギャップロックを先に取得したほうが勝ち。負けた方はネクストキーロック(S)の取得待ちになる。勝った方はインデックスレコードロック(X)を取得。勝った方がコミットすると、負けた方はDuplicateエラーとなりネクストキーロック(S)を取得。UPDATE後にINSERTこのケースでは先にINSERTしたほうが勝ち。並行するトランザクションが互いにギャップロックを取得した場合は、どのトランザクションも挿入インテンションギャップロックが取得できなくなる。この状態でどちらかがINSERTすると挿入インテンションロックの取得待ちとなり、後発のトランザクションがINSERTするとデッドロックが発生する。デッドロックが発生した場合、トランザクションの重みが軽い方がロールバックされる。重みが同じ場合は発生トリガーとなったトランザクション(つまり後からINSERTした方)がロールバックされる。UPDATEで排他ロックを取得した。思ったらいつのまにか負けていた。いうことが起こりうる。UPDATEはしないほうが良さそうな感じがします。しても厳密性を求めるのであればトランザクション分離レベルを変えるか、一工夫が必要でしょう。STATUSで見えなかったりしました。何か確認方法を間違えているのかもしれないのですが。追加で検証したいこと今回はだいぶシンプルなテーブルでの確認になってしまったのですが、本当は複合インデックスの場合やパーティションテーブルの場合の挙動などもまとめたかったです。いつのまにかボリュームが増えてきて力尽きましたorz次回があればその時にまとめようと思います。採用どんな会社か気になる方は、是非下記のサイトもご覧ください。