jfluteの日記 このページをアンテナに追加

2012-08-31

MySQLでお手軽デッドロック

| 15:08 |

-- MEMBER_FOO は MEMBER の one-to-many の子テーブル
delete from MEMBER_FOO where MEMBER_ID = 3
insert into MEMBER_FOO ...(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」という
ことで、長めの共有ロックによる待ちが発生しません。
トランザクション継続中の検索レコードを他のトランザクションが
待たずに更新できます。でも、ちゃんとファジーは防ぐ。

そして、トランザクション継続中の検索にヒットするレコードが
登録されても、再度同じ検索を何度してもヒットしません。
(もちろん、登録処理の方は待つことなく処理を完遂できます)
おおぉ、ファントム・リードも防いでる!
「where FOO_ID > 100」で 104 登録。
確かに「Repeatable Read」と言いつつファントム・リードを
防がないのは、変だなぁという思いもあるので一理あるのかなと。

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

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

(抜粋)
「アプリケーション内に一意性チェックを実装する為に...」

ってあるので、確かにユニーク制約のカラムにランダム文字列を
登録する場合のリトライとかってやったことありますが、
リトライせずに登録が保証されるとか!?

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

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

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

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

どへー!?

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

B. XとYで、それぞれ違うレコードを対象にしても発生
 -> 【追記】検索条件カラムに複合ユニークキーが付いていれば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-mysql-example」のJUnitでのテストにて、
同じような処理で再現しています。
 -> DBFlute Example
/*
o 外側のスレッドはトランザクションなし AutoCommit モード
o threadFire の中のスレッドではそれぞれ独立したトランザクション
o resource.await() で全てのスレッドが一旦整列、再びよーいドン!

 【補足】
 DBFluteが提供しているJUnit拡張フレームワーク「UTFlute」では、
 マルチスレッドのテストが簡単に実行できるようにしています。
 cannonball()メソッドでマルチスレッドで一気にexecute()を同時実行。
*/
final Map<Integer, Integer> parameterMap
        = new HashMap<Integer, Integer>();
parameterMap.put(1, 3);
parameterMap.put(2, 7);
parameterMap.put(3, 9);
final Purchase source = purchaseBhv.selectByPKValueWithDeletedCheck(1L);
cannonball(car -> {
    int entryNumber = car.getEntryNumber();
    Integer memberId = parameterMap.get(entryNumber);

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

    Purchase inserted = source.clone();
    inserted.setMemberId(memberId);
    long currentTime = currentTimestamp().getTime();
    long randomMillis = currentTime + (entryNumber * 10000);
    inserted.setPurchaseDatetime(toTimestamp(randomMillis));

    purchaseBhv.insert(inserted);
    purchaseBhv.delete(inserted); // to revert
}, new CannonballOption()
            .threadCount(parameterMap.size()).commitTx()
            .expectExceptionAny("Deadlock found"));
しかしながら、これがまた不安定な動きするんです。
{ 3, 7, 9 } だと発生するのですが、
{ 3, 6, 7 } だと発生しない。
 -> 【追記】補足をブログ最後に追記

ただ、{ 3, 6, 7 } でも、ReplaceSchemaで最初からデータを
削除して 0 件削除状態にしておけば、発生する。
(なぜか、その場で削除して同じ状態にしても発生しない...
ちゃんと事前にコミットされて反映されてるはずなのに)
 -> 【追記】補足をブログ最後に追記
そういえば参考のブログで検証されていたのは、主キー(PK)のカラムです。
どうやら、PKのカラムとそれ以外のカラムでは動きが違いそう。

もう一個テストがありまして、こんどは PK の方で、
存在しない未来の会員ID「99999」で空振り削除してみました。
...
final Member source
    = memberBhv.selectByPKValueWithDeletedCheck(3);
cannonball(car -> {
    int entryNumber = car.getEntryNumber();

    // empty delete (update, for update) locks new record
    // (if it deletes existing records, second waits)
    MemberCB cb = new MemberCB();
    cb.query().setMemberId_Equal(99999);
    cb.disableCheckCountBeforeQueryUpdate();
    memberBhv.queryDelete(cb);

    car.restart();

    Member inserted = source.clone();
    inserted.setMemberAccount(entryNumber + ":" + inserted.getMemberId());
    memberBhv.insert(inserted);
    markSet.add("success: " + entryNumber);
    insertedIdSet.add(inserted.getMemberId());
}, new CannonballOption().commitTx()
         .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件だったら更新処理しない、
っていう風にできるようにしました。
これでディベロッパーは何も気にせず実装できるかと。
まあ、実質的にMySQL専用プロパティと言えるでしょう。
 -> MySQLの取扱い | DBFlute

でも、なんだかせっかくの queryUpdate() がなぁ、
という感じです...(><
【追記】
MySQLメーリングリストでも話題になってしましたね。
http://www.mysql.gr.jp/mysqlml/mysql/msg/15005

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

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

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

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

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

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

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

少しずつ、もろもろ整理してみたいですね。
トラックバック - http://d.hatena.ne.jp/jflute/20120831/1346393321