ichirin2501's diary

いっちりーん。

挿入と参照ロックに疲れ果てた俺たちは

www.slideshare.net

出来事


はじめに

　これは先日の社内勉強会で発表したもので、MySQLで特定の問題を解決したいときのノウハウ話です。特定の問題とは、アプリを書いてると「データがなかったINSERTしたい、あるなら排他ロックしつつ取得したい」という要望があったりします。例えば、あるユーザーアクションで初期値もパラメーターで渡されるケースで、データがないならそのままINSERT、既にデータがあるなら取得して状態に依存して更新処理を行いたい場合などです。見かけのロジックは単純に見えますが、MySQLでこれを実現しようとするといくつか罠があります。よくある間違いとその理由、無難な解決方法とバッドノウハウを紹介した資料となっています。ただ、拙いトークでカバーするつもりだったので資料内の情報は断片的です。その辺を少しでもカバーするために補足します。

ギャップロック(7Pの補足)

　ギャップロックが怖い？怖くないよ！、ファントムリードから守ってくれる友達だよ。ギャップロックとはトランザクション分離レベルがREPEATABLE-READで実レコードが存在しないインデックスの隙間をロックするものです。資料内ではSELECT-FOR-UPDATEで空打ちロックすると、検索が走るインデックスの隙間であるギャップがロックされてINSERTがブロックされるが、最初からINSERTだったらブロックしないという紹介をしています。INSERTはギャップロックを取得しないと勘違いされそうなので、それについての補足です。
　実はINSERTもギャップロックを取得しています。では、何故INSERT同士ではブロックしないのか？これはINSERTにはLOCK_INSERT_INTENTIONというフラグが付加されており、それがあると一意制約で重複したりしなければ同じギャップのINSERTでもブロックしません。InnoDBは基本的に共有・排他ロック + フラグの形でブロックする|しないを判断しており、単純な共有・排他ロックで全てが説明できるわけではありません。先のSELECT-FOR-UPDATEによるギャップロックは排他ロックになりますが、同じように別トランザクションがギャップロックしてもブロックされないのはフラグが関係しているからです。

バッドノウハウがバッドになるとき

以下のような状態のときは気をつける必要があります。

  1. AUTO-INCREMENTなカラムがあるとき
  2. 一意性を担保してるカラムに外部キー制約が設定されているとき

　前者はINSERTではなくUPDATE時にもMySQLが保持してる値はincrementされるのが理由です。UPDATE時の頻度によりますがAUTO-INCREMENTなカラムなのに実データ上はそこそこ歯抜けになります。また、UPDATEが高頻度だとひたすら増え続けるので32bit使い切ってしまって泣くみたいなケースも考えられます。
　後者はそこそこ奇妙です。INSERT時に外部キー制約されている値の行が共有ロックになる話は有名ですが、ON DUPLICATE KEY UPDATE構文のUPDATE時にも条件次第で外部キー制約されている値の行が共有ロックされるのはあまり知られていません。設定されている外部キー制約のカラム全てが対象ではなく、内部処理でPRIMARY-KEY（テーブル定義によってはUNIQUE-KEY）を使ってデータ有無を調べますが、この探索に利用したカラム（だいたいのケースはpkeyです）に外部キー制約が設定されていると、通常のINSERT同様、外部キーの値の行に共有ロックを取られます。結果として外部キー制約による共有ロックが絡んでデッドロック・パフォーマンスが落ちるというケースも十分考えれます...完全にバッド。外部キー制約は他にも気をつける必要があり、一部こちらでも紹介しています。

興味があれば読んでみてください。

邪悪な写真について

初任給狩り....うっ頭が

名前募集中です

「データがなかったINSERTしたい、あるなら排他ロックしつつ取得したい」
長すぎる。

もう「しゅっしゅー問題」という認識で広まって欲しい。


最後に、
ロックと仲良く！