VOYAGE GROUP　エンジニアブログ

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

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

はじめに

アプリケーションでレコードの重複チェックをしてからINSERTをする。

テーブルにはUNIQUE制約をかけてデータ不整合が起きないようにしている。

という仕様はよくあるケースだと思います。

こういったケースでINSERTしたときにどのような仕組みが働いて重複データを防いでいるのだろう？

アプリケーションで重複チェックをしてはいるけどMySQLではどんな挙動をしているんだろう？

というのが気になったので調べました。

調べること

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

前提知識

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

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

知って得するInnoDBセカンダリインデックス活用術！ - 漢(オトコ)のコンピュータ道

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

MySQLのロックについて(PDF) - sh2さんの資料

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

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

MySQL/MariaDBとTransactdのInnoDBロック制御詳細　その1 - BizStationブログ

環境

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が暗黙的に行うインテンションロックです。

参考 http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

真ん中あたりにある[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 アドテクユニットキャリア採用サイト

こんにちわ。ECナビでアプリケーションエンジニアとして働いている@secret_hamuhamuです。

社内では、アカウント名から、"はむちゃん"とか"はむ"とか呼ばれています。

今回は、様々な角度からデータを分析したい話をします。
例えば意思決定を行う会議には、分析データを使用することが多々あると思います。
しかし、会議という限られた時間で、データに対する知識量も違う中で、分析データを活用することは難しいと思います。

こんな経験された方、いらっしゃるのではないでしょうか？
もしかしたらこの問題Tableauを使えば解決できるかもしれません。

Tableauってなんだ？

Tableauとは、GUIで直感的にデータ分析が行えるBIツールです。

公式ホームページ

結構、お値段しますが気になる方は一度トライアル版を利用してみてください。

Tableau Desktopを使ってみて所感をレポートしたいと思います。

"iPhoneでのツイート"というサンプルを使います。

こちらのサンプルは、ブラウザからそのまま操作できるのでTableauによるグラフレポートを体験できます。
さらにTableau Desktopを使うことで、より複雑な操作をすることができます。

引用

火曜日の12:30 GMT、それまで噂されていた iPhone5 ではなく iPhone4S の発売を Apple は発表しました。数時間の内に、Apple ファンは #iphone5 が販売されなかった落胆をツイートし、ハッシュタグ #iphone4S のついたツイートが急増しました。地図に表示されている円のひとつにマウスオーバーしてください。そこでつぶやかれたツイートを見ることができます。

iPhone4S発表後のハッシュタグ#iphone4sと#iphone5の各地域でのツイート数やツイート内容や時系列の推移が見れるようです。

読み込んでみると...

おぉ！ビジュアルがすごい。

質的データなのか量的データなのか自動で判別してくれてるみたいです。

ドラッグして地域を絞ったりすることも可能です。

会議などで、グラフを使う際はplotして1枚絵のものを使うより実際に動かしながら話したほうが話を聞く方は理解しやすいと思います。

※ サンプルなので読み込むだけで使えましたが、最初は自分でグラフを作成する必要があります。
　作成はドラッグ＆ドロップで、簡単にできてしまいます。

一つの角度だけでなく違う角度からもグラフを見たくなると思うので、違うグラフに変更してみます。
表示形式のから好きなグラフの種類を選んでクリックするだけ！

先ほどと同じデータですが、表示形式を変えてみました。

そのデータ積み上げグラフにしてみて！と言われもすぐにボタンポチポチで表示形式を変更できます。

左上に戻る/進むボタンがあって、さっき見してくれたグラフに戻して！と言われてもすぐ戻せます。

男性女性のようにカテゴリ分けしていれば、その場で比較することも可能です。

今回はサンプルを使いましたが、DBやエクセルなどからデータを読み込んでグラフを作れます。

ドライバーはこちら

所感

・ビジュアルが良くて直感的に操作できて楽しい

・データがあれば簡単にグラフが作成できる

・エンジニアだけでなく、デザイナーやディレクターも簡単に触って確認できるので意思決定しやすい

Tableau使ってみると

■ お約束
VOYAGE GROUPでは、一緒に働いてくださる仲間を募集しております。
株式会社VOYAGE GROUP アドテクユニットキャリア採用サイト
株式会社VOYAGE GROUP - 採用
どんな会社か気になる方は、是非下記のサイトもご覧ください。
VOYAGE CULTURE

VOYAGE GROUP　エンジニアブログ

Blog移行のお知らせ

MySQL InnoDBのinsertとlockの話

様々な角度からデータ分析ができるTableau

VOYAGE GROUP エンジニアブログ

Blog移行のお知らせ

MySQL InnoDBのinsertとlockの話

様々な角度からデータ分析ができるTableau

VOYAGE GROUP　エンジニアブログ