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したい、あるなら排他ロックしつつ取得したい」
長すぎる。

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


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

「外部キー Night」に参加してきた

発表者として参加させていただきました。

発表資料はこちらです（自分でも忘れそうなのでブログにリンク貼っておく）

外部キー制約に伴うロックの小話


追記: 2015/08/22
ブログでも補足したほうが良いかな、と思ったので今更追記することにしました。

どういう資料？

外部キー制約で発生するロックのお話です。前提として、MySQL(5.5.28)のInnoDBストレージエンジン、トランザクション分離レベルはREPEATABLE-READ, READ-COMMITEDです。上記は検証環境ですが、MySQL5.1 ~ 5.7でも変化していない挙動のはずです。また、InnoDBのロックはインデックスレコードロックなので、インデックスに対する理解が必要不可欠です。発表時間も20分程度ということもあって、その辺りをある程度理解されてる方が対象の資料となっています。

3行でまとめると？

  1. INSERT時には自動的に共有ロックが取られるぞ！
  2. INSERTする前には外部キー制約の行を排他ロックしろよ！
  3. それでも避けられないパターンがあるぞ！覚悟しろ！

最後は何を言ってるんだ、という感じですが、テーブル定義に依存する話を紹介しています。残念ながら発覚後に対処するのは極めて難しいものと思われます。

いちりんちゃん的には外部キー制約どうなの？

MySQLなら避けます。資料内容以外でも、現在、MySQLだとパーティションが利用できなくなる、パフォーマンスも落ちる、など色々デメリットがあります。大抵の環境ではデメリットが勝るでしょう。ただ、開発環境では外部キー制約を利用して異常を発見し易くしたりするのはありだと考えてます。

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

なし

昨日今日は長野におりました。
この春、同期が大学卒業となり、同窓会第0次会？としてメンバー各位が招集されたようです。

楽しい時間でした。
月日が経過したのだと、実感した時間でした。

今、寂しさを感じています。


改めて、ご卒業おめでとうございます。

あけましておめでとうございます

　あけましておめでとうございます。

生きております。
記事を書くのも約3ヶ月振りです。

昨年の振り返りを途中まで書き出していたのですが、
だるくなったのでやめました。自分らしいです。

そして今年です。
冬コミを落とす（記事書く時間がなかった）結果になったので、
夏コミに向けて、日常生活をハックしつつ時間を作り、
その時間を夏コミの同人誌制作にあてていきます。

時間がなかった、というのは言い訳だよねーとは自分でも思っています。
例えば通勤時の電車で座れる時間帯の電車に乗り、
パソコン開いて記事書くとか、時間を作り出すことは可能でした。

ということで、今から夏コミに向けて動いていきます。
先に夏コミのネタを投下しておきますと、
・ファミコンエミュ作成
の予定しております。

キー配列を変更

お久しぶりです、生きてます。

もう一ヶ月経ちますが、先月から脱ニートして社会人になりました。
楽しい日々を送っています。
情弱キャラが板についてきましたよ（どやぁ


んで、キー配列を変更した話を少し。
仕事ではPerlでプログラミングしているんですけど、
Perlでプログラミングするのは好きじゃなかったです。

理由：$が打ちにくい。

変数を記述する度に手が止まるのがストレスだったんですね。
左手でShiftを小指で押しつつ中指で$を打つ手の状態は、
どうしてもタイピングが一時的に止まってしまう形でした。

そこでキー配列の変更ですよ！
自分にはそんな発想はなかったんですが、触発されたのがこれです。

「キーボードをいじろう！」 by @handlename
http://www.slideshare.net/handlename/yapcasia-ltthon

決してdvorakにしたわけではなくてｗ、数字と記号を入れ替えてみました。
入れ替えてみて、最近は違和感なくなってきたなぁと感じたので、その感想です。

1. Perlで$を打つときに一時的に止まらない
単純にShiftを押す必要がなくなり、打ちやすくなったからですね

2. for構文が打ちやすい
例えばこれ、Shiftを押すのが3回しかないです

for($i = 0; $i<1000; ++$i)
       ^      ^      ^

あと、プログラミングにおいては数字を打つより記号を打つことが多いわけですから、
単純にShiftを押す回数が減りますね。

思い切ってキー配列を変更して良かったです。
Perlプログラミングも快適、というお話でした。