鍵は二度死ぬ
ということで、デッドロックトラブルの続き。
前回のSELECTとUPDATEがかち合う理屈はわかった。UPDATE文は対象が1レコードだけだが、SELECT文はテーブル内のそれなりに広範囲のレコードが対象になるので、ロックがかち合う可能性はあるだろ〜な〜とも思う。
でも、ログをよく見てたら、何箇所かUPDATE文同士でデッドロックが起こってるものもあったりしちゃうのであった。
……ちょっと待て。これは有り得ないでしょ
[有り得ない理由その1]
この二つのUPDATE文は、同一テーブル内とは言え全く違うレコードが対象になっており、ロック範囲がそもそも違う筈。というか運用ルール上、あるUPDATEのトランザクションでかかるロック範囲が、別のUPDATEのトランザクションのロック範囲と部分的にすら重複することも無い筈。
[有り得ない理由その2]
そもそも、UPDATEに使用される更新ロックは重ねがけされない為、デッドロックではなく、どちらかが待ち状態になる筈。
う〜ん……何だコレ……。
まあ、考えても時間と青春とサービス残業の無駄遣いなので、まずは変換デッドロックの際の対策を調べてみた。
【変換デッドロックが起きたら……】
１:データベースのパフォーマンスを見直す。場合によってはインデックスの貼り方などを調整してみる。
２：SELECT文に、ヒントロックをつけてロックタイプを強制的に更新ロックに変更する。
３：取り敢えず、デッドロックが出るのはしょうがないものとみなし、プログラム側でデッドロックをキャッチしたときに自動リトライをかける。
４：DBの設定でREAD_COMMITTED_SNAPSHOTをONにすることで、更新ロック中に別のリクエストが有った場合に、更新ロック直前のデータを見せるようにする。
対策はこんな感じなんだけど･･･う〜ん。
まず、２は論外。理由は、既にUPDATE文同士(つまり更新ロック同士）でデッドロックが起きている為、更新ロックでSELECT文をリクエストするようにしたとしても、同じような結果が見えてる。それに、仮にこれで解決したとしても、運用上で待ちが頻発する。
次に３だけど、これはデッドロックが発生した当日に対応済み。ただ、これは本当にどうしても極々稀に偶発的に発生してしまう場合向けの対策だよなぁ……。少なくともSQLServerのログには、大量のデッドロックの痕跡が出ており、これを放置するのは根本的な対策とは言いがたい。
４はパッと見効果有りそうだなぁと思う。設定にはDBを止める必要があるけれど、非運用時間帯があるので問題無いし。
ただ、これをOFFにしてるもっとでかいシステムもあるだろうし、何か根本的な原因は他に有りそうな……
ってことは、やっぱり１は避けて通れないか……。僕、出来ればデータベースには余りディープに関わりたくないんですが。ロクなことが無さそうなので……
まあ、まずはどんな感じにインデックスを貼ってるかを調べますか……。えーと、Management Studioを開いて、さてさてテーブルAのインデックスはっと……あれ、貼ってない。
……ん？
インデックスが無い！？
そう！クラスタ化インデックスすらない。
つまり主キーが無い。
ディープに関わるどころかイロハのイで大コケ。このテーブルを作ったのは誰だぁ！プロマネを呼べ！
主キーが無いと、WHERE句を使おうが何しようが、常にテーブル内の全行を走査することになるはず。だったら、ロック範囲も常にテーブル全体にかかるんだろーなぁ。ついでに、UPDATEにかかる時間も増大する為、ロック時間も長くなるよねぇ。これはマズい……
てことで、自前で作っておいたテスト環境でテーブル構成の変更テスト。どの列の組み合わせで一意になるかは把握しているので、その列を主キーにして、一通りの機能テスト。うん、重複データはエラーになるし、実際のデータ登録時に弾かれたりもしないな。
てことで、暫くこれで様子を見てもらうことにした。
あと、このシステムはテーブルの更新頻度は激しいけど、1回に更新するデータ量はそんなに多くなく、定期的に行われるSELECTもそこまで厳密な結果が帰る必要もない。なので、READ_COMMITTED_SNAPSHOTはONの方が良いんじゃないかなぁと思い、こちらも設定。
【READ_COMMITTED_SNAPSHOTの変更の仕方】
１：DBに接続しているシステムを全て終了する。
２：Management Studioで、対象のDBを右クリックし、オフラインにする
３：同じくManagement Studioでクエリアナライザを開き、下記のコマンドを入力する。
ALTER DATABASE DB名
SET READ_COMMITTED_SNAPSHOT ON
４：Management Studioで、対象のDBを右クリックし、オンラインにする
以上。多分これで、少なくともUPDATEは当初の予定通り、ロック範囲が重なることは無い筈。後は、SELECT文がスナップショットの方を参照してくれれば、今回のようなことにはならないと思う。うん。
ただ、一点気になるのは、ロック範囲が肥大化するとはいえ、UPDATE文同士はやっぱり「デッドロック」じゃなく「待ち」になるはずじゃないのかなぁってところ。更新ロックが一時的に「共有ロック」の特性を持つところに何かからくりがあるのかねぇ。もしくは、ヒープテーブル(主キーの無いテーブル)での動作に限っては、何か特別な動きをするのかしら。
まあ、それはおいおい調べて躓いて挫折しましょうかね。さてさて、今回の件は一丁上がりかな。
……と思ったら、二日後にやっぱりデッドロックが発生したorz
ただ、アプリケーションがエラーを出しているタイミングが若干違うなぁ……。まあ、同じようにログを追ってみますか……
……はぇっ！？←本当にこんな声が出た
え……あの……セ、SELECT文同士でデッドロックが起きてるんですが……しかも、今回は全てのログにおいて
え〜も〜やだ〜……何なのこれ……。
MicroSoftの陰謀だと信じたい……
