MySQLでお手軽デッドロック

-- MEMBER_SEA は MEMBER の 1:n の子テーブル
delete from MEMBER_SEA where MEMBER_ID = 3
insert into MEMBER_SEA ...(MEMBER_ID は 3)

ん?

最初のdeleteが互いに「0件削除」だとデッドロック。別トランザクションのMEMBER_IDが別ID(例えば4)でもデッドロック。(とにかく両方のトランザクションでdeleteが0件であれば)

これは...

ネクスキーロックって?

MySQLInnoDBには「ネクスキーロック」という機構があります。

詳しくは、ぐぐってくれればOKですが、ひとまず参考になるオフィシャルサイトのページを。
 => ネクスト キー ロック:ファントムの問題を防ぐ

難しいこと書いてありますねぇ。
「ギャップ」って何!?
ここでいうインデックスレコードって!?

とにもかくにもMySQLは、トランザクション分離レベルがデフォルトで「Repeatable Read」です。

そもそもトランザクション分離レベルとは?

そもそもトランザクション分離レベルを簡単に説明すると、とあるトランザクション内の検索処理において、

Read Uncommitted : コミットされてないデータも読む
Read Committed : コミットされたデータを読む
Repeatable Read : 一度読んだデータは何度読んでも同じ
Serializable : もう一人しかそのデータを読めない

という感じになります。ほとんどのDBMSのデフォルトが「Read Committed」で、そもそも「Repeatable Read」をサポートしていないDBもある。

分離レベルの概念のとしての「Repeatable Read」は、「ファジー・リード(Fuzzy Read)」を防ぎます。一度読んだデータが途中で別のトランザクションに更新されても、再度読み込んだときにはその前に読んだデータが取得されます。

ただ、「ファントム・リード(Phantom Read)」は防ぎません。「where FOO_ID > 100」という検索条件で検索したときに、最初の検索では 101, 102, 103 と三件しか存在していなくても、別のトランザクションに新たに 104 登録されて再度検索すると、その 104 は取得されます。

ちなみに、ファジー防止のために共有ロックが長めに保持されます。

Read Committed : 検索が終わればすぐに共有ロック解放
Repeatable Read : トランザクションが終わるまで保持

共有ロックがかかっているレコードに対する更新処理は待たされます。
(じゃあ登録処理は待たされないのかな...!? 試したことない)

わりと簡単に待ちが発生するので、あんまり現場では好まれないので、Oracleなんかはそもそもこの分離レベルをサポートしてないし、どこいっても基本は「Read Committed」ですね。別にファジーもファントムも業務的にわりと許容できるしと。

MySQL の Repeatable Read さん

MySQLの「Repeatable Read」は、ちょっと実装方法に工夫(!?)を入れることで、ファントム・リードを防いでいます。

そもそもSNAPSHOT方式で実現した「Repeatable Read」ということで、長めの共有ロックによる待ちが発生しません。トランザクション継続中の検索レコードを他のトランザクションが待たずに更新できます。でも、ちゃんとファジーは防ぐ。

そして、トランザクション継続中の検索にヒットするレコードが登録されても、再度同じ検索を何度してもヒットしません。(もちろん、登録処理の方は待つことなく処理を完遂できます)

おおぉ、ファントム・リードも防いでる!

seaさんが「where FOO_ID > 100」した後に、landさんが 104 を登録してコミット、その後またseaさんが同じ条件で検索したとき 104 は検出されません。seaさんの2回目の検索時はDB上には104は登録されていますが、以前に「100よりも大きいもの」を検索しているので、2回目の検索もRepeatableになるのです。

確かに「Repeatable Read」と言いつつファントム・リードを防がないのは変だなぁという思いもあるので一理あるのかなと。

ただ、ちょっと不思議な動きをする部分があります。「where FOO_ID > 100」ではなく、「where FOO_ID > 100 for update」した場合、つまり、更新ロックを取得した上で範囲条件の場合、他のトランザクションで 104 を登録しようとすると、待たされます。更新ロックが登録をブロック!

オフィシャルサイトの説明は、恐らくこのことを言っているのかと。

(引用)
ネクスキーロックを使用すると、アプリケーションに一意性チェックを実装できます...」

ってあるので、確かにユニーク制約のカラムにランダム文字列を登録する場合のリトライとかってやったことありますが、これがリトライせずに登録が保証されるので、リトライ処理を省けるとか?
(でも、そのDBの挙動に依存したロジックをアプリに入れるのちょっと勇気いるかも?笑)

(引用)
ネクスト キー ロックによって、テーブル内に存在しない物を「ロック」する事ができます」

「ギャップ」って存在しないレコードのことを指してるのかな!? わからないけど、とにかくすごいのか恐ろしいのかわからない...

でも、もうちょい怖がれます。更新ロック取得時のロック範囲について、すごく丁寧に検証されている方の記事があります。

// MySQL InnoDBネクスキーロック おさらい
http://d.hatena.ne.jp/sh2/20090112

(素晴らしい記事です!ありがとうございます)

すごい...

とりあえず言えることは、ユニークなキーに対する条件において、指定した条件値の「次」のレコードもロックするようですね。

FOO_ID が「25, 30, 35」とデータが飛んでいる場合に、「FOO_ID <= 30」とすると、25, 30 にロックがかかるのはいいとして、次のキーである 35 にもロックがかかってしまうと。というか、30 から 35 までにロックがかかる。つまり、31 の insert が待たされる。存在しないレコードにもロックがかかるってこのことか!?

なんのために?うーん...
とにかくちょっと広めのロックを取得していると。

そもそもSNAPSHOT方式って部分も、テーブル単位のSNAPSHOTで、検索してないのにコミットされたレコードが読めないという、予期せぬ「Repeatable Read」事件が発生するという面もあり... (この件は、別の記事でまた書きたいですね)

もろもろ実現するために、なにかと幅を広げて対応しているのかも。そのロジックはちょっと正確にはなかなか追跡できていません。

どうしたらデッドロックが起きたか?

さて、特徴を知ったところでここからが本題。「待つ」だけならまあまだいいよね、待つだけだから。そりゃあ場合によってはデッドロック発生しちゃうかもだけど、レアケースであればさ、クリティカル業務でなければさ。

でも、さらに怖がれる事象があるのです。
それは、更新ロックを取得するときのSQLの結果が 0 件のとき...

1. sea が更新もしくは削除処理の検索条件で 0 件 (空振り更新ロック)
2. land も全く同じく 0 件 (空振り更新ロック)
3. sea が同じテーブルを登録処理、待たされる (ギャップロックに待つ!?)
4. land が同じテーブルを登録処理、デッドロック (あららら...)
デッドロック発生で land が強制ロールバックで sea は処理続行

どへー!?

(ポイント)
A. 1の更新処理が一件以上ヒットしていれば、2でBが待つ
 => 待つだけでデッドロックしない

B. seaとlandで、それぞれ違うレコードを対象にしても発生
 => 【追記】検索条件カラムに複合ユニークキーが付いていればYes
(補足をブログ最後に追記)

C. 1と2が単なる for update の検索なら3は待たず4で待つ
 => 【追記】C に関しては、JUnitの方で試したらやっぱりデッドロックしました

ふーむー、見えないレコードにロックかけてるなら、なぜ一件以上ヒットしたときと同様に 2 で待たんのじゃ!? 見えないものへのロックって物理的なものへのロックと違うの!? わからんとしかいいようがないけど、とにかくこの実験が言えることは...

子テーブルの更新を「delete/insert」方式で実装すると、わりと簡単にデッドロックが発生しちゃうということ

※論理削除のupdate/insertでも同じ

アプリでは結構普通にやるんだけどこの実装...

DBFluteで言えば、queryDelete() や queryUpdate() してから、batchInsert()。いちいち事前selectして0件以上だったらdeleteってやらない。0件ならdeleteやupdateが単に空振りすればいいだけだから。でも、MySQLだと0件時のチェックしてあげないとデッドロックする。

親テーブルでの排他制御を先にやっていれば問題なさそうか!? 同じ親レコードであれば、排他制御を先にやることで更新で後が待つ。でも、親レコードが別でも発生しました... (ポイントB)

せめて同じID同士で発生するだけならまだしもって感じなのに、全然違う会員が同時に更新して子テーブルをdelete/insertしたらデッドロックして片方が落ちる可能性がある!?

とりあえずDBFluteでは、queryDelete/Update() にて、dfpropの設定次第で、内部的にselectCount()して0件時は実行しないってオプションを追加したけど、それもなんかなぁと。全ての queryDelete/Update() に事前検索入ってしまう。

まあ、0件削除って要は最初の登録時だから、やっぱりめったにあることじゃないし、登録時の処理と更新時の処理が分かれていれば、更新時の子テーブル削除処理では実質 0 件はないと。世の中、なんとかうまくいっちゃってるんでしょう。

ただ、実際に現場で見てしまいました。実運用で発生したのを...
(せっかくの大量の会員登録が来てもバシバシ落ちていく...)

JUnitのテストで再現

DBFluteのExampleにて、同じような処理で再現しています。

/*
o cannonball()メソッドでマルチスレッド一気に実行
o cannonball内は独立トランザクション
o 外側スレッドはトランザクションなし(AutoCommit)
*/
Map<Integer, Integer> entryMemberIdMap
        = new HashMap<Integer, Integer>();
entryMemberIdMap.put(1, 3);
entryMemberIdMap.put(2, 7);
entryMemberIdMap.put(3, 9);
Purchase source = purchaseBhv.selectByPK(1L).get();
cannonball(car -> {
    // find memberId by entryNumber of current thread
    int entryNumber = car.getEntryNumber();
    Integer memberId = entryMemberIdMap.get(entryNumber);

    // empty delete (update, for update) locks new record
    // (if it deletes existing records, second threads waits here)
    purchaseBhv.queryDelete(cb -> {
        cb.query().setMemberId_Equal(memberId);
    });

    Purchase inserted = source.clone();
    inserted.setMemberId(memberId);
    long currentTime = currentTimestamp().getTime();
    long randomMillis = currentTime + (entryNumber * 10000);
    inserted.setPurchaseDatetime(toTimestamp(randomMillis));
    purchaseBhv.insert(inserted);
}, new CannonballOption().threadCount(parameterMap.size())
    .expectExceptionAny("Deadlock found"));

しかしながら、これがまた不安定な動きするんです。{ 3, 7, 9 } だと発生するのですが、{ 3, 6, 7 } だと発生しない。
 => 【追記】補足をブログ最後に追記

ただ、{ 3, 6, 7 } でも、ReplaceSchemaで最初からデータを削除して 0 件削除状態にしておけば、発生する。
(なぜか、その場で削除して同じ状態にしても発生しない...ちゃんと事前にコミットされて反映されてるはずなのに)
 => 【追記】補足をブログ最後に追記

そういえば参考のブログで検証されていたのは、主キー(PK)のカラムです。どうやら、PKのカラムとそれ以外のカラムでは動きが違いそう。

もう一個テストがありまして、こんどは PK の方で、存在しない未来の会員ID「99999」で空振り削除してみました。

...
Member source = memberBhv.selectByPK(3).get();
cannonball(car -> {
    int entryNumber = car.getEntryNumber();

    // empty delete (update, for update) locks new record
    // (if it deletes existing records, second waits)
    memberBhv.queryDelete(cb -> {
        cb.query().setMemberId_Equal(99999);
    });

    car.restart();

    Member inserted = source.clone();
    inserted.setMemberAccount(entryNumber + ":" + inserted.getMemberId());
    memberBhv.insert(inserted);
}, new CannonballOption().expectExceptionAny("Deadlock found"));

これもデッドロック

参考にしたブログのパターンとはちょっと違って、Identityで自動採番されるPKで、かつ、次のレコードが存在しない値を指定して空振りの更新ロック取得してみました。

これが、「-1」で空振りしてもデッドロックは発生しません。「99999」だと次のレコードが存在しないので、「ギャップまで」をロックして、普通にIDを自動採番するinsert ができなくなってしまうのでしょうか...!?

という感じで、とりあえず推測

とまあ色々試して情報集めた限りの「推測」ですが...

<ユニークインデックス (PKとか)>
「where PK_ID = ?」で更新ロック取得すると...
→ 次のレコードがあれば、次のレコード(まで)をロック
→ 次のレコードが無ければ、それ以上の値の全ての insert をロック

<普通のインデックス (FKとか)>
「where FK_ID = ?」で更新ロック取得して 0 件だと...
→ よくわからないけど状況により insert にロック。
→ (レコードが存在していた場合のロックの範囲は未検証)
→ 【追記】補足をブログの最後に追記

すいません...推測にもなってないですけど...要は実験結果ですね。実験もこれ以上細かくやるのはさすがにというところでここまで。

ただ、ユニークインデックスの方は、あまり問題になることはないかと。圧倒的に問題なのは普通のインデックス、FKカラムのときです。こういった悩ましい問題が MySQL にあるというところで...

子テーブルの更新における delete/insert 方式、MySQLではちょっと鬼門になりそうです。

その後の、追記たち

【追記】
PURCHASEテーブルのMEMBER_IDはFK制約が貼ってあるので、同時にNON-UNIQUEインデックスが付与されています。また、MEMBER_ID, PRODUCT_ID, PURCHASE_DATETIMEの三つからなる複合ユニークインデックスが貼られています。

試しに、複合ユニークインデックスを外してみたら、test_insert_Deadlock_NextKeyLock_for_FK() のテストは、デッドロックが発生しなくなりました。ただ、同じ会員ID同士で実行させるとやはり発生しました。parameters = new Object[] { 3, 3 }; という感じで。(しかも、後続のqueryDelete()で10秒ほど待ちが発生して、特に例外にならず空振りdeleteは正常に終了する!?!?!? そのまま待ち続けてくれれば別にいいのに...)

つまり、どちらでも発生はするんだけど、複合ユニークインデックスが付いていると、違う会員IDでも発生する。

ここの複合ユニークインデックスのカラムたちの実際の値はちょっと複雑なので、ネクスキーロックがどこまでロックを広げているのかが特定しづらく、列挙する会員IDによっては発生したりしなかったりするのかもしれません。

とりあえず追記分を考慮した推測は...

子テーブルのFKが複合ユニークインデックス付いている場合は、違う会員同士の更新(delete/insert)でデッドロックになる可能性がある。

子テーブルのFKが単にFKによるインデックスだけなのであれば、一人の会員がブラウザ二つ起動して同時に更新(delete/insert)とかすればデッドロックになる可能性がある。それも親テーブルの排他制御を先に実行していれば問題ない。

もちろん、更新するのが会員だけとは限らないとか、業務によっては心配する場面は幾つもあるでしょうが。
...

【追記】
ちなみに、トランザクション分離レベルを、ReadCommittedに変更すると発生しませんでした。(transaction-isolation = READ-COMMITTED in my.cnf)
もう、RepeatableReadはやめて、常にReadCommittedにしちゃった方がいいかなぁ...
...

【追記】
DBFluteでは、DBFluteプロパティ(設定)のオプションで、queryUpdate() と queryDelete() の実処理の直前に、select count(*) して0件だったら更新処理しない、っていう風にできるようにしました。

DBFluteのオプション機能として、queryUpdate(), queryDelete()の内部で事前selectCount()して、0件であれば更新/削除処理しない、という振る舞いをできるようにしました。littleAdjustmentMap.dfprop で isQueryUpdateCountPreCheck = true とするとそうなります。

これでディベロッパーは何も気にせず実装できるかと。まあ、実質的にMySQL専用プロパティと言えるでしょう。
MySQLの取扱い | DBFlute

でも、なんだかせっかくの queryUpdate() がなぁ、という感じです...(><

MySQLでRepeatableReadで、delete/insert方式をよくやる場合は、この設定を検討すると良いでしょう。
cb単位で cb.enableQueryUpdateCountPreCheck(); とすることもできます。
...

【追記】
MySQLメーリングリストでも話題になってしましたね。
http://www.mysql.gr.jp/mysqlml/mysql/msg/15005

ファントムリードを防ごうとしている

...

【追記】(2014/09/15)
素晴らしい資料が公開されています!

// MySQLのロックについて
http://d.hatena.ne.jp/sh2/20140914

なるほど、インデックスのリーフに対するロックだから、発生したりしなかったりするんですね。でも、本当に理解するには、もうちょいじっくり読む必要がありそうです。。。

(ん!? ネクスキーロックとギャップロックという言葉の違い!?)
(レコードロック + ギャップロック = ネクスキーロック ???)

ただ、はっきりしたのが、ファントムリードを防ごうとしている、という点。なるほど。これはすっきりしました。

ただ、業務的にファントムリードを防ぎたいか?トランザクションの本をちゃんと読まないと答えは出なさそうですが、そんなに防ぎたいケースがあまり思い付かない。。。あったとしても、ピンポイントで別の方法でロックするなりで実現するかも。(残りチケットの取得処理とか...)

少しずつ、もろもろ整理してみたいですね。

また追記ですよ

【追記】(2021/11/20)
空振り更新後のinsertでデッドロックになってしまうので...
InsertOrUpdate的な処理で「更新後になければ登録」をすると発生してしまいます。
DBFluteの insertOrUpdate() は、ちょうどそういう処理をしています(><。

テスト環境ですが実業務で発生したようです。
ゆえに、UpdateOptionでprecheckInsertOrUpdateCount()というのを追加しました。
littleAdjustmentMap.dfprop で isInsertOrUpdateCountPreCheck=true にすると全体的に有効にすることもできます。

アプリ自身で似たようなことをやっている場合はフレームワークでも防ぎようがないので、気をつけないといけないですね。
...

【追記】(2023/07/12)
身の回りの現場では、わりとReadCommittedにするようになって、それで何も問題なくサービス運用しているので、ほとんどお手軽デッドロックで悩むことは個人的にはなくなりました(^^。