アラカン"BOKU"のITな日常

文系システムエンジニアの”BOKU”が勉強したこと、経験したこと、日々思うことを書いてます。

MariaDB(MySQL):更新処理中に、別スレッドのREAD(Select)がロック待ちになってしまう理由(備忘)

仕事で、ちょっと反省したことがあって、備忘を兼ねて書いておきます。

すごく特殊な話とかでは全然ありません。

あまりシビアな状況でMariDB(MySQL)を使う要件が最近なかったので、油断していた(笑)せいでしかないのですが。

f:id:arakan_no_boku:20181009220954j:plain

 

発生した問題はこんな感じ

 

具体的な状況は、こんな感じです。

MariaDBMySQL)をバックエンドに使うシステムです。

たまたま、アプリケーションで大量の更新処理を行う必要があったわけです。

2万件くらい。

CSVファイルからインプットデータを読み取り、加工しながら、テーブルを3つくらい更新する処理です。

アプリケーションの実行時間は約10分くらいでした。

それを実行してしばらくすると、近くで画面検索の作業していた人間がざわめき始めたんですね。

それからすぐ、「何かした!?」とクレームが来ました。

彼らによると。

急に処理が遅くなって、「ロック待ちでタイムアウトした」みたいなエラーで異常終了したぞ・・ということです。

調べてみると。

彼らが検索対象としている項目が、確かに一括処理で更新対象になっているテーブルと一致しています。

ログを見る限り、更新処理中のテーブルをREAD(select)して、その結果でテンポラリテーブルを生成するSQLが、ロック開放待ちのままタイムアウトしたみたいです。

結局。

これでは困る・・という話(当然ながら)で原因を調べて対処することになりました。

 

最初はWRITEロックを疑いました

 

プログラムを作ったのは自分ではありません。

なので、とりあえず、どんなSQLが生成されているのか・・から調べます。

最初の仮説はテーブルに「WRITEロック」がかけられているのではないか・・です。

MariaDBの「WRITEロック」は、自スレッドでは「READ・WRITE許可」で、他スレッドでは「READ・WRITE共不可」になりますから。 

更新を実行しているスレッドと異なるスレッドで、Select文が待たされている。

この現象にピッタリです。

詳しくは、こちらの記事がわかりやすいですので、リンクをのせときます。

sawara.me

でも。

結論から言えば、その仮説は違いました。

さすがに、そんなことはしていなかったです。

 

分離レベルの問題でした

 

結論から言えば、悪さをしていたのは、トランザクションの分離レベルでした。

トランザクションは1つの作業単位として扱う複数のSQLクエリの集まりです。

トランザクション内の複数の処理がすべて正常終了して始めて、コミットによって、DBに変更を反映させたり、失敗した時はロールバックによって、トランザクションの開始前の状態に戻したりできます。 

その時に、SELECTした時の値の整合性をどこまで保証するかの基準を決めるのが「分離レベル」です。

例えば。

Aトランザクションで更新中でコミットもしてないデータが、別のBトランザクションで読めてしまうのを許容する(ダーティリード)レベル。

それはないけど、Aトランザクションの途中で別のBトランザクションで値が更新・コミットした値があると、同じSELECTなのに、Aトランザクションの最初と最後で異なる値をとってきてしまうのは許容する(ファジーリード)レベルとか。

はたまた、値が変わったりしないのは保証するけど、途中で別のトランザクションで追加・コミットされた時に、トランザクションの最初と最後で同じSELECTで取得する件数が変わってしまう(ファントムリード)可能性は許容するレベルとか。

いやいや、そんなん一切許しません・・というレベルとか。

そういう違いをふまえて、MariaDBMySQL)には・・というかSQL規格には・・4つの分離レベルがあります。

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

このうち、READ UNCOMMITTEDとSERIALIZABLEは、緩い方と厳しい方・・どちらも極端すぎて、ほぼ使われません。

ファジーリード位はしゃあないな・・とする「READ COMMITTED」か、それより厳しいけどファントムリードのリスクはあるよ・・とする「REPEATABLE READ」のどちらかです。

本当は「そんなん許さない」ってのが一番いいのです。

ですが、そうすると、トランザクションの同時実行性に問題がでるので、ある程度は妥協して実用性とのバランスをとっている・・って感じですかね。 

このへんの話を深堀りするのが記事のテーマではないので、さらに興味のある方は以下の記事をどうぞ。

自分も読んで、とても、わかりやすいと思いましたので、おすすめします。

d.hatena.ne.jp

さて。

じゃあ、MariaDBMySQL)は、どの分離レベルなのかというと、デフォルトは「REPEATABLE READ」です。

実は、これはかなりデータの整合性を重視した厳しめの設定で、他のDBはだいたいもう少し緩い「READ COMMITTED」がデフォルトです。

今回は、この「厳しめ」の部分が裏目になったんですね。

問題は「クエリにマッチしなかったレコードの扱いです」でした。

クエリの検索対象範囲内には含まれているのだけれど、条件にはマッチしなかったレコードのロックの扱いは、分離レベルによって以下のようになっているのです。

  • READ COMMITTED : 解放する
  • REPEATABLE READ : 解放しない
  • SERIALIZABLE :解放しない

あーーー!!

てなもんです。

今回の現象は、「更新クエリの範囲内に含まれたテーブルのうち条件にはマッチしなかったレコードのロックが解放されなかったがために、ロック待ちがおきていた」ということだったのですね。

実際にトランザクションの分離レベルを「READ COMMITTED」にしてやると、再現しなくなりました。

やれやれ。

気づいてしまえば簡単な話です。

でも、普段意識してないと、うっかりハマる。

トラブルって、だいたい、こういうもんなんですよね。