ＳＥＬＥＣＴ文でタイムアウトが起こります。

質問

いつも勉強させて頂いております。どうぞよろしくお願いします。

以下の環境で開発を行っています。
SQLServer2005
ASP.NET C#

.NETのソースで
1つのクラスに2つのクエリ(2つともSELECT←複数テーブルを結合している)を呼び出す処理を書いています。(トランザクション処理は付けていません)2クエリとも検索対象テーブルは60万件程度のデータを保持しています。検索条件によって処理速度は違うのですが、1ユーザが最も遅い検索条件で検索をしてもタイムアウトにはなりません。しかし、2ユーザが同じタイミングで検索(どのような条件でも)処理を行うと、タイムアウトを起こしてしまいます。まれにデッドロックも起こります。

SQLServerではSelectのたびにlockがかかるのでSQL文中に「WITH(NOLOCK)」を記述するように書かれているサイトを見つけたのですが、他ユーザからの更新もあり得るテーブルなので、他の方法を探しております。
トランザクション分離レベルは規定値です。

是非、どなたかご教授ください。よろしくお願いします。

回答No.1

jamshid6 · ベストアンサー

２ユーザともタイムアウトになるのですか？
タイムアウトがハードウェアのリソースやコネクションの競合などではなくて、テーブルロックにより発生しているという結論はどうやって導き出しましたか？
READ COMITTEDのロックヒントなしのSELECT同士でブロッキングやデッドロックが発生するパターンが思い当たらないので。

NOLOCKをつけたらタイムアウトしなくなるか試してみるのがいいと思います。
で、本当にロックが原因で、ダーティリードは避けたいとなると、参照処理ということを踏まえてスナップショット分離レベルを使うくらいかと思います。

補足

＞２ユーザともタイムアウトになるのですか？
はい。ほぼ同じタイミング、かつ10秒程度、タイムアウトになります。
＞タイムアウトがハードウェアのリソースやコネクションの競合などではなくて、テーブルロックにより発生しているという結論はどうやって導き出しましたか？
私もjamshid6さんと同じように、ブロッキングやデッドロックが発生するとは思っていなくて、前述したようにSELECTでlockがかかるというサイトを見た、と言うだけです。多数のユーザがアクセスするシステムのため、２ユーザの処理だけでリソースのせいとは考えにくく、安易にそのサイトを受け入れたと言うところです。

＞NOLOCKをつけたらタイムアウトしなくなるか試してみるのがいいと思います。
そうですね、NOLOCKをつけて同じ現象が発生しなければ、ロックが原因だったと言え、対処法としてはスナップショット分離レベルに設定すればよいと言うことになりますね。

jamshid6さん、ありがとうございました。分離レベルでもちょっと分からないところがあるのですが、ロックが原因だと判明したら質問させていただきます。

回答No.2

tomo197608

デッドロックが原因であれば、
SELECTだけの方は
SET LOCK_TIMEOUT 10000 （ミリ秒）
等を利用して設定値を小さくしエラーにしてみたらどうですか？
逆にデッドロックが原因ではない場合は
設定値を大きくし、エラーが発生しないように待つ設定にしてみたら如何でしょうか？

経験上ですがSQLServerの場合ロックのエスカレーションのせいでいきなりデッドロックが発生する事があります。
エスカレーションのせいでテーブルロックにまで膨らんだりします。
（以前プレミアサポートに確認したら時はPKeyやindexを指定していればありえないと言っていましたが、発生してしまっていました。）

実行中にロックレベルやロックの状態を確認するとはっきりとするかもしれませんね。
※2008では設定でエスカレーションを外せるようにはなってるみたいですね。

更新を伴うSELECTだとしたら、処理の順番を統一しているか確認が必要かもしれません。
処理の入り口で同じテーブルにテーブルロックをかけるように命令すれば確実にデッドロックは発生しなくなると思います。
（必ず待ちが発生し処理を平行して行えなくなるデメリットは発生します。）

お礼

jamshid6さん、tomo197608さん
遅くなりましたが、アドバイスありがとうございました。