こんにちは。ECナビでアプリケーションエンジニアをやっている駒崎です。

今回はMySQLのInnoDBエンジンにおけるINSERTとロックの挙動について書きたいと思います。

はじめに

アプリケーションでレコードの重複チェックをしてからINSERTをする。
テーブルにはUNIQUE制約をかけてデータ不整合が起きないようにしている。
という仕様はよくあるケースだと思います。

こういったケースでINSERTしたときにどのような仕組みが働いて重複データを防いでいるのだろう？
アプリケーションで重複チェックをしてはいるけどMySQLではどんな挙動をしているんだろう？
というのが気になったので調べました。

調べること

INSERTした場合のロックの挙動
FOR UPDATE文で排他ロックをかけた場合のロックの挙動

前提知識

この記事を読む前に、今回参考にさせていただいた素晴らしい資料をみることをおすすめします！
少なくともギャップロック、レコードロック、ネクストキーロックあたりについてはさらっと一読していただくと良いと思います。
（この辺りが理解出来ている方は華麗にスルーしていただけると良いと思います）

クラスタインデックスについて知れる。いつもお世話になっております。

InnoDBにおけるロックの仕組みを解説している素敵な資料

SHOW ENGINE INNODB STATUSを使ってロック制御の確認方法がわかる
InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる

InnoDBのロック制御について詳しく調査されている

環境

MySQL InnoDB
version = 5.6.20
tx_isolation = REPEATABLE READ
query_cache_type = OFF

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを利用するため、以下の設定をONにします。 
innodb_status_output_locks = ON;

検証

今回は単独キーのユニークインデックス（かつ、セカンダリインデックス）の場合を調べます。

以下のようなテーブルとデータを準備します。


INSERTの検証

2つのトランザクション(Tx1, Tx2)が並行動作した場合を想定して、ロックの挙動を観察します。

トランザクション(Tx1)でトランザクションを開始し、INSERTをします。

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを確認します。

lock mode IXというテーブルロックがかかっています。
これはInnoDBが暗黙的に行うインテンションロックです。

真ん中あたりにある[lock type compatibility matrix] を見ると、IXは通常の共有ロック(S), 排他ロック(X)と競合し、インテンションロック(IS, IX)とは競合しないことがわかります。今回の記事とはあまり関係ないので詳しい説明はここでは割愛しますが、IX同士は競合しないことだけ覚えて進めていきます。

※以後、(S) (X)と表記しているところはそれぞれ共有ロック、排他ロックの意味で記載します。

確認を進めます。
別のトランザクション(Tx2)でINSERTをしてみます。
待ちになりました。
TRANSACTIONセクションを見てみると情報が大量に出てきます。
トランザクション毎に情報を整理してみます。

まずTx1に関する情報を見てみます。

IXの他に情報が増えています。locks rec but not gapというのはレコードロックです。
最初に見えていませんでしたが、Tx1がINSERTをする際に挿入インテンションギャップロックをかけていました。挿入インテンションギャップロックは、それ同士では通常競合しませんが、同じ行に対する場合のみ競合する特殊なロックです。他のトランザクションで同じ行に対するINSERTがされたため、ukeyの追加する予定のレコード(u1=5)に対するインデックスレコードロック(X)を取得しています。
一番下の0:, 1:というのはロックした行（この場合はインデックスレコード）のデータダンプです。

Tx2に関する情報も見てみます。

必要な部分のみ抜粋しています。IX同士は競合しないので取得できています。
ukey上の挿入インテンションギャップロックでTx1と競合したため、ukey上のu1=5に対するネクストキーロック(S)に変化して待ちになっています。

ここまででINSERTが重複した時にどのようなロック制御が発生するのかはなんとなく掴めたと思います。

Tx1をロールバックしてみます。Tx1のロック情報はなくなりTx2のロック情報が変化しています。


ukey上のネクストキーロック(S)を取得しているように見えますが、レコード情報がわかりません。また、ukey上のut=6とut=5の前のギャップロック(S)を取得しています。
一旦Tx2をロールバックします。

今度はTx1でコミットした場合にどのようになるか確認します。
先ほどまでと同じ手順でTx2が待機している状態まで進めます。
上のtrx id 2874がTx2、下のtrx id 2873がTx1です。

Tx1をコミットします。
Tx2は以下のようにエラーとなりました。当然ですね。
ERROR 1062 (23000): Duplicate entry '5' for key 'ukey'

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを確認します。

Tx2がukey上のu1=5のレコードに対してネクストキーロック(S)をかけています。
INSERTしようとした行が既に存在する場合はネクストキーロック(S)を取得します。
Tx2のトランザクションを終了するとロックを開放しました。

今回はTx1が先行、Tx2が後行でやりましたが、INSERTの順番が逆になった場合はTx1が失敗するだけです。

では次に存在しないレコードに対するFOR UPDATEをかけてからINSERTをする場合を見てみます。

FOR UPDATE後にINSERTの検証

1つめのトランザクション(Tx1)でトランザクションを開始し、u1=5に対して排他ロックをします。

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを確認します。

ukey上のu1=6の前のギャップロック(X)を取得しています。
ユニークキーの場合はプライマリーキーに対するロック範囲と同じようになるのですね。
INSERTしてみます。

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを確認します。

追加行のギャップロックが増えています。
次にTx2の処理を進めます。

SHOW ENGINE INNODB STATUSのTRANSACTIONセクションを確認します。
Tx1の情報です。

ukey上の追加レコードに対するレコードロック(X)が増えています。

Tx2の情報です。

ukey上のレコードロック(X)の取得待ちになっています。

Tx1をコミットします。Tx2の情報です。

存在するレコードに対する排他ロックのため、ukey上のu1=5に対するレコードロックおよび、プライマリインデックスに対するレコードロックになっています。
FOR UPDATEで排他制御をするとなんとなく予想通りの動きをしてくれていると思います。

しかし、トランザクションが並列で実行される場合、必ずしもこの順番で処理がされる保証はありません。
例えば、両方のトランザクションでFOR UPDATEがほぼ同時に行われる場合も考えられます。
発行するコマンドは割愛してしまいますが、Tx1, Tx2のそれぞれでFOR UPDATEをした場合、以下の様な状態になります。

ギャップロック同士は競合しないため、お互いに同じ範囲にギャップロック(X)を取得しています。
その後、どちらのトランザクションがINSERTをした場合でも、挿入インテンションギャップロックがギャップロックと競合するため、INSERTは待ちになります。
もう一方のトランザクションがINSERTをするとデッドロックが発生し、あとからINSERTしたトランザクションはロールバックされます。

先にINSERTして残った方のトランザクションの状態は以下のようになるようです。

ukey上のu1=6, u1=5のギャップロック及びukey上のu1=6に対する挿入インテンションギャップロックを取得しています。ここまで来ればデッドロックは起きません。

つまり・・・どういうことなの？

FOR UPDATEせずにINSERT
  • INSERTで挿入インテンションギャップロックを先に取得したほうが勝ち。
  • 負けた方はネクストキーロック(S)の取得待ちになる。勝った方はインデックスレコードロック(X)を取得。
  • 勝った方がコミットすると、負けた方はDuplicateエラーとなりネクストキーロック(S)を取得。

FOR UPDATE後にINSERT
  • このケースでは先にINSERTしたほうが勝ち。
  • 並行するトランザクションが互いにギャップロックを取得した場合は、どのトランザクションも挿入インテンションギャップロックが取得できなくなる。
  • この状態でどちらかがINSERTすると挿入インテンションロックの取得待ちとなり、後発のトランザクションがINSERTするとデッドロックが発生する。
  • デッドロックが発生した場合、トランザクションの重みが軽い方がロールバックされる。重みが同じ場合は発生トリガーとなったトランザクション(つまり後からINSERTした方)がロールバックされる。
  • FOR UPDATEで排他ロックを取得した。と思ったらいつのまにか負けていた。何を言っているのかわからねーと思うが(ry ということが起こりうる。
同時実行されるトランザクションが多く、トランザクション分離レベルがREPEATABLE READの場合は空振りする可能性のあるFOR UPDATEはしないほうが良さそうな感じがします。
もし、どうしても厳密性を求めるのであればトランザクション分離レベルを変えるか、一工夫が必要でしょう。 

よくわからなかったこと

挿入インテンションギャップロックを取得しているはずが、SHOW ENGINE INNODB STATUSで見えなかったりしました。何か確認方法を間違えているのかもしれないのですが。

追加で検証したいこと

今回はだいぶシンプルなテーブルでの確認になってしまったのですが、本当は複合インデックスの場合やパーティションテーブルの場合の挙動などもまとめたかったです。いつのまにかボリュームが増えてきて力尽きましたorz

次回があればその時にまとめようと思います。

[PR]
VOYAGE GROUPではエンジニアを絶賛募集中です！
株式会社VOYAGE GROUP - 採用
どんな会社か気になる方は、是非下記のサイトもご覧ください。
VOYAGE CULTURE
株式会社VOYAGE GROUP アドテクユニット キャリア採用サイト