none
ロックエスカレーション差異 RRS feed

  • 質問

  • こんにちは

    SQL Server 2008 ExpressとStandardでの機能差異?で質問させてください。

    挙動として、ストアードプロシージャを実行させる同一のVB.NET2008SP1で作成したプログラムを2つ同時に実行

    (ただし、検索キーは異なる)した時にExpressでは正常に処理を終了するのですが、

    Standardではデットロックとなりました。

     

    エラーの内容は以下の通りです

    SqlException.ErrorCode -2146232060

    SqlException.Message  トランザクション (プロセス ID 51) が、ロック 個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。

    ・ExpressはWindowsXP SP2 上に導入

    ・StandardはWindowsServer2003(64bit)上に導入

    ・どちらもDBの導入設定はデフォルト

    ・VB.NETのCPUはany

    ・ストアードプロシージャはSQLのみの以下の3つ

    1.テーブルAから取得するSPその1 SELECT文にはWITH (UPDLOCK)を付与

    2.Aから取得したデータをテーブルBにInsertするSPその2を基本として

    3.SP1その1とSPその2の間にSPその2を実行する前にテーブルAのデータがテーブルCに存在するかチェックするSPその3

    WITH (UPDLOCK)なし

    ・VB.NETで作成したプログラムは

    このSPを受け取ったパラメータをキーにして実行するプログラム

    ・対象テーブルA,B,Cはプライマリーキー設定はしているがIndex設定されていない。

    以上を踏まえて、

     

    1.ロック監視プロセス?はExpressとStandardで差異があるのでしょうか?

    2.Expressと同様に正常終了させたいのですが、その場合Standardへの設定が必要でしょうか?

    設定が必要な場合はどのような設定が必要でしょうか?

    3.SPその3にもWITH (UPDLOCK)を付与すべきでしょうか?

     

    いろいろ書きましたがよろしくお願いします。

     


    SRHSRH
    2010年3月20日 5:14

すべての返信

  • tenki28です。

    直接的な答えではないのですが、

    ExpressはCPUを1つしか使えず、Stdだと4つまでサポートしています。

    なので、複数プロセスで同時実行したときの挙動が違うこともあり得ると思います。

    それぞれのSQLの実行プランを比較すると差異がある場合も。

    まずはデッドロック時のロックの状態を調べたり、各SQLの実行時にどんな形でロックをしているのかを調べてみてはいかがでしょう?

    2010年3月22日 5:11
  • こんにちは tenki28さん

    書き込みありがとうございます。

    今回の質問は、いろいろありまして、実行プランなど見せてもらえず、困ったのでこのような処理系でSQL Serverに関する情報を求めたというのが始まりなんです。

    サポートCPU数による差異があるのかもしれませんが、

    ロックエスカレーションの挙動がCPU数に依存することは理解し難いように思えます。

     

    いろいろ、調べてみたところ

    行ロックが5000件を超えた時点、もしくは使用メモリ量の40%以上が対象セッションで利用された時に表ロックへエスカレーションされ、

    表ロックを監視しており、チェックされた時点でロールバックコストの低い方が終了させられるようですね。

     

    抜本的な解決にはならないとは思いますが、

    http://support.microsoft.com/kb/323630/ja

    にて示された設定を試すべく依頼し確認してみることにしました。

     

     

     

     

     


    SRHSRH
    2010年3月24日 5:20
  • tenki28です。

    ロックエスカレーションによってデッドロックが発生することはありますが、
    今回の問題がロックエスカレーションによって起きているという確証はありますか?

    今回問題になっている処理詳細・処理時間がどれくらいなのか分かりませんが、
    実はExpressの環境でたまたま動いているのかもしれないですよね。

    デッドロックがどのリソース(テーブル A/B/C)で競合しているのかを
    調べないといけないですね。

    2010年3月27日 8:33
  • tenki28さんに同意ですね。まず、ロックがどこで発生しているのかを調べると良いと感じます。

    今回の結果がロックエスカレーションによるものだったとして、対象の行数やメモリを制限して対策したとしても、
    「テーブルにインデックスを付けていない」とのことですので、テーブルスキャンによるロック解放待ちは発生しそうに見えます。

    CPUの数によってロックエスカレーションに差異があるかどうかは分かりませんが、
    過去には利用するCPUの数によってデッドロックが発生するような事例もありましたので、MAXDOP=1を使って検証してみるといいかもしれません。
    [FIX] クエリを並列実行すると、クエリ自体で検出されないデッドロックが発生する
    http://support.microsoft.com/kb/315662/ja
    それが関係あるか無いかを調べるだけでも意味はあると思います。

    また、質問1つ目のエディションによるロックの差異について、
    根拠はありませんがたぶん差異は無いと思います。
    (むしろ、エディションによってロックの仕組みに差異があったら困る気がします。)

    質問2つ目について、少し求められているものからは逸れているかもしれませんが、
    デッドロックを発生させずに正常終了させたいという点だけに注目して言えば、
    更新ロックを使うのではなく楽観的な同時実行制御の方法を選択するのはどうでしょう?(更新ロックが要件として必要というならダメですが。)

    質問3つ目については、おそらく試してみた方が早いと思います。

    2010年3月27日 16:19
  • tenki28です。

    大事なことを書き忘れてました。
    SQLServer 2008 であれば LockEscalationオプションでエスカレーションを禁止することができたはずです。

    と言いつつ、自分は使ったことがないのですが、
    エスカレーションが問題であるなら、とりあえずは対処できるかと。

    ただし、エスカレーションをするということは、それなりにメモリを食うSQLを実行しているということなので、
    個人的にはSQLを見直すことをお勧めします。

    2010年3月28日 17:14
  • tenki28さん

    yottun8さん

    ありがとうございます。

    調べたいですが、調べられない(させてくれない)のでここで質問させていただいたという背景があります。

    そこをご理解下さい。

    調べられるならば、こんな質問しませんよ・・・

    Indexに関しても、当然設計段階で依頼していたのを却下されるという不測の事態に遭遇し・・・

     

    愚痴になって申し訳ないですが、そんなこんなでみなさんの知恵をお借りしたかった次第です。

    とはいえ、いろいろとありがとう御座いました。



    SRHSRH
    2010年3月30日 2:20
  • tenki28です。

    どういった状況なのかはわかりませんが・・・。
    MSDNなどのサブスクリプションを購入していればSQL Server Std の環境を作ってみてはいかがでしょう?
    なければ http://msdn.microsoft.com/ja-jp/evalcenter/bb851668.aspx などから評価版をダウンロードして・・・
    評価版はEnterPriseですが問題はないと思います。
    どうせならOSも評価版などを使って本番環境と同等にしたいかな。
    (すでにしていたらごめんなさい)

    自分なら本番環境と同レベルのテスト環境を作って再現実験を行って、
    問題が再現しなければ環境が原因である可能性が高いを話して実環境での調査の権限をもらいますね。

    もし再現してしまったら地道に原因を探って直すと・・・。

    2010年3月31日 15:15
  • tenki28さん

    TechNetは購入していますので、環境構築は自宅では可能です。

    もちろん、ソースコードを持ち出す事はできませんので

    似たようなソースコード書いてみて試したいと思っています。

     

    重ねてありがとうございました

     

    とはいえ、次の仕事はDB2・・・

     



    SRHSRH
    2010年4月1日 10:53