仮説仮説検証
てことで、昨日思い浮かんだ仮説を本来の業務中に時間サをとボって検証してみた。
【検証方法】
昨日の2つのVBSの中で、トランザクション分離レベルをSerializableにして、同時に起動してみた。
【結果】
現象再現した！＼(＾▽＾)／
いや〜、SQLServerのエラーログがデッドロックだらけですわ……。こりゃ見てて爽快……ってか、DBがデッドしそう怖い。
てことは、下記の理屈でデッドロックしてたってことですかね
- トランザクションAとトランザクションBが、同時に起動する。この二つのトランザクションが影響するロック範囲には、共通する範囲が含まれる。
- トランザクション分離レベルがSerializableなので、トランザクションAの参照処理中、トランザクションBの終了を待つ
- 同様に、トランザクション分離レベルがSerializableなので、トランザクションBの参照処理中、トランザクションAの終了を待つ
- はい、デッドロックの（以下略）
ところでこのデッドロックは……サイクルデッドロックに分類されるのかしらん？
現象再現したところでプログラマに確認してみたところ、ビンゴ。もう現象は出ていないけど、今後のことを考えてRead_Committedに変えてもらった。ふぅ、やれやれ、これでやっと解決かなぁ。
【今回のきょ〜くん】
１：データベースには主キーをちゃんとつけよう。
- 主キー（正確には、インデックス)をつけないと、テーブル全体が参照走査対象になる。
- その為、無駄なパフォーマンスが発生するだけでなく、ロック範囲が肥大化する
- その為、複数クライアントでデータ更新時にロック範囲が頻繁にかちあう。
２：トランザクション分離レベルは適切なものを使おう
- 低いトランザクション分離レベルではダーティリードだとか、ファントムリードだとか、イメージの悪いデータ不整合が起きそうな言葉ばっかりついてまわるが、逆に高い分離レベルは高い分離レベルで、DB設計段階からロック範囲を精密に考慮していかないと今回みたいにデッドロックが多発する。要は適材適所
- デフォルトのRead_Committedは、データ更新中は他のトランザクションにて読み込み処理の待ちが発生するが、READ_COMMITTED_SNAPSHOTをONにすることで読み込み待ちを回避できる。(余談：OracleのRead_Committedはもともと読み込み待ちは発生しないらしい）
３：トランザクション内には本当に必要な処理だけ入れよう
- トランザクション同士でロック範囲がかちあうと面倒なので、必要な処理だけを入れてロック範囲が必要最小限で済むようにする。また、処理が終わったらそのトランザクションはすぐコミットされるようにし、ロック時間が無駄に長くならないようにする。
- 無駄にトランザクション内で全表走査とかが発生すると、トランザクション分離レベルによってはデッドロックに陥る。
４：再現テスト環境は用意しておこう
【デッドロックが起きた時の大雑把な調査】
- デッドロックのログをとるようDBMSを設定する。
- ログから、問題となるSQL文とロックの種類を特定する
- ソースにて、そのSQL文が組み込まれているトランザクションを確認し、処理順序の確認とおおまかなロック範囲の想定をする。
- ロック範囲を想定する上で、主キーやインデックスがどのように貼られているかを再確認する
- ロック範囲がかちあっていると思ったら、トランザクション分離レベルを確認し、かち合うと問題になるレベルになっていないか確認する
- 以上のことから、テーブルを見直すか、処理を見直すか、トランザクション分離レベルを見直すかを再検討する
ってなところですかねぇ。デッドロック調査に関しては、Microsoftのページを見るともっと専門的なやり方が書いてありますが理解不能だったのでまずは上記みたいに当たりをつけるやり方でもいいかなぁと。多分、ロック範囲を厳密に確認する必要がある場合はその理解不能なやり方を理解しないといけなさそうですねぇ。でもそこまで理解しちゃうと戻ってこられなさそうですなぁ……どこからどこへかは分からないけど
