- PR -

SQLserverのデッドロック

1
投稿者投稿内容
OWL
会議室デビュー日: 2005/10/26
投稿数: 13
投稿日時: 2005-10-26 08:37
MS-SQLserverについて、ご指導いただければと思います。
デッドロックについてですが、お客様のサーバで、下記のようなエラーがでます。
「トランザクションがthread|comunication bufferリソースで他のプロセスとデッドロックの対象として選択されています。トランザクションを再実行してください」
そこで、このデータベースをバックアップして、私の開発環境で復元して同じことをしても、エラーにならずに通ってしまいます。分離レベルとかもテストしましたが問題はなさそうです。当面SQLの記述を変えることで回避しましたが、根本的な解決をするには、このデッドロックを解除しないといけないと思うのですが、何か良い方法をご指導いただけますと助かります。
ちなみにデッドロックしているのは、viewの特定のレコードで、更新なしのselectのみで、このようにエラーがでます。ただ、同じビューに対してネストしてselectを発行しております(これがいけないのでしょうか？)
ご指導よろしくお願いいたします
オメガ11
会議室デビュー日: 2005/10/26
投稿数: 3
投稿日時: 2005-10-26 10:13
始めまして。

役に立つか不明なんですけど、知ってる範囲でレスしてみます。
切り分けのために本番サーバとテスト環境でそれぞれ
以下の情報を確認してみてください。

・サーバに搭載されているプロセッサ(cpu)の数量
・SQL Serverが使用するプロセッサ数の設定
・該当の処理のクエリ処理プラン

たまーに、なんですが並列クエリを使用している環境で
プロセスが発行しているスレッド同士がかちあって
デッドロックを発生させる可能性があったりします。

あとは…トレースフラグの1204などを設定してみてデッドロック情報を
取得の上、状況を確認してみてください。(やってたらゴメンです)
OWL
会議室デビュー日: 2005/10/26
投稿数: 13
投稿日時: 2005-10-26 10:46
オメガ11様
ご回答ありがとうございます。
先程調べてみましたら、CPUの数は２個。
SQLserverが使用するのは、使用可能なすべてのプロセッサ となっていましたので、
２個を使っていると思われます。
また、クエリ処理のプランは５ になっておりました。

夜になりますと、お客様のサーバーは使われなくなりますので、今夜にでも、
この辺の設定を変えてテストしてみようと思います。(VPNでサーバに接続しています)

ありがとうございました。
matu_tak
会議室デビュー日: 2003/02/06
投稿数: 13
投稿日時: 2005-10-26 18:49
オメガ11 さんの書かれているように
並列クエリが原因でデッドロックを起こしているかもしれませんね。
その場合は、以下の KB（サポート技術情報）にありますように

[FIX] クエリを並行実行すると、クエリ自体で検出されないデッドロックが発生する
http://support.microsoft.com/kb/315662/

各クエリに OPTION (MAXDOP 1) ヒントを追加し、
クエリで使用するプロセッサを 1つに絞ることで対応可能になります。

また、サーバー全体として、並列プランの使用を禁止するという対処方法もあります。
これは、sp_configure で show advanced options を 1 に設定して、
max degree of parallelism を 1 に設定すれば OK です。


参考：
max degree of parallelism オプション (Books Online)
http://www.microsoft.com/japan/msdn/library/ja/adminsql/ad_config_7h9q.asp

エラーメッセージ（1205）：
「トランザクション (プロセス ID 64) が、thread | communication
buffer リソースでほかのプロセスとデッドロックしました。

念のため英語版のメッセージ
「Transaction (Process ID 64) was deadlocked on
thread | communication buffer resources
with another process and has been chosen as the deadlock victim. 」
OWL
会議室デビュー日: 2005/10/26
投稿数: 13
投稿日時: 2005-10-27 02:14
ためしに、使用可能なプロセッサの数を１個にしてみたら、デッドロックは発生しなくなりました。

返信頂いた皆様、本当にありがとうございました。

私はデータベースの開発経験が1年程度しかないのですが、皆さん流石ですね〜。
大変勉強になりました。
こばさん
大ベテラン
会議室デビュー日: 2004/03/17
投稿数: 147
投稿日時: 2005-11-16 19:45
　もう解決してしまったかもしれませんが、ReadOnly な SELECT 文には、FROM 句にWITH (NOLOCK) を 付与してやった方が良いようです。

　私のところも、ReadOnly な SELECT 句どおしでデッドロック起きてましたが、WITH (NOLOCK) を散りばめることで激減しました。

　クエリに使用する CPU 数を絞らずして効果がありました。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2005-11-16 22:34
引用:

ReadOnly な SELECT 文には、FROM 句にWITH (NOLOCK) を 付与してやった方が良いようです。

NOLOCK の意味分かってますか？ 不用意に NOLOCK を付けるのは危険ですよ。メリットもありますが、同時に大きなデメリット(リスク)もあるので、何の説明もなく「やった方が良いようです」なんていい加減は薦め方はやめたほうがいいです。

対象テーブルがシステム運用中に更新されることがなく(夜間更新だけどか)、参照専用のシステムならばともかく、他セッションからテーブルの更新が行われる可能性がある状況で NOLOCK を使うのは無謀です。

業務系では非コミットデータが読み取れてしまう可能性がある、なんて怖くて使えません。SQL Server 2005 で早くスナップショット分離レベルを試してみたいですね。
OWL
会議室デビュー日: 2005/10/26
投稿数: 13
投稿日時: 2005-12-07 21:34
みなさん、アドバイスどうもありがとうございます。
しばらく、忙しい時期が続いたため、書き込みがあったことに気づいてもいませんでした。NOLOCKは使っていません (業務系ですので、確かに怖いです。。。)
1

スキルアップ／キャリアアップ（JOB@IT）