SELECT文でのデッドロックに対しての対処方

質問

先日よりデッドロックが発生するようになり、確認の為にトレースログを
抽出するように設定かけたのですが、UPDATE文とSELECT文がぶつかり
デッドロックが発生している事がわかりました。

しかし、UPDATE文にはトランザクションをかけていますが
SELECT文には特にロックかけていない為、どうして起こっているのか
わかっていません。

SELECT文を読んでいる時にUPDATE文によって
レコードに変更があった為、デッドロックが発生したのでしょうか?

SELECT文にはUNLOCKをつけた方がいいのでしょうか？

プログラムはDELPHI
DBはSQLSERVER2000になります。

お忙しい所申し訳ありません。
宜しくお願い致します。

回答No.7

いけだ ひろし（@ike-2000） · ベストアンサー

#6です。
気になったの書き込みます。

参照側で考えていましたが、更新側のロック範囲は適正でしょうか？
不必要に広い範囲に排他ロックを掛けていないでしょうか。

例えば更新が select xx from yy where zz>aa for browse のようになっていると広範囲にロックが確保され要注意です。
また、更新がバッチであれば、途中コミットして排他時間を短くするなりの対策が考えられます。
とにかく参照処理も更新処理も出来るだけ範囲を小さくされることをお勧めします。

補足

度々ありがとうございます！！

更新している方は1件トランザクションかけた後
１レコードのみ更新をかけているのですが、
１テーブルずつ行っている仕様になってい為

例
トランザクション
HEAD更新　where Head_NO='111'
HEADsub更新 where Head_NO='111'
H_PAS更新 where Head_NO='111' and Head_SubNO='1'
・
・
コミット

上記のように行っている為
１テーブルに対しては絞っているはずなのですが、
トランザクションは長いイメージは正直あります・・・

回答No.6

いけだ ひろし（@ike-2000）

#5です。
失礼しました。ROWLOCKでは改善されないですね。
意図したのは
選択行(レコードセット)に対しては結果的にはロックしないが、該当行を読むときだけはロックする。
というものです。
調べた限りではSQL Serverでは出来ないようです。すみませんでした。
(HiRDBではwithout lock waitというオプションでこれが出来ます。)

今回のケースはselectの共有ロック期間が長いため、updateがタイムアウトすると言うことですね。
だとすると、まじめに選択範囲を分割して(TOP (n)で出来ると思うのですが検証できていません)ロック時間を短くするしかないようです。

※
ちなみにREAD_COMMITTED_SNAPSHOT データベースオプションをONにするとOracleのような動作になりデッドロックが回避されます。
ただし、パフォーマンスにはMSも疑問を持っているようです。

参考意見ばかりですみませんでした。

お礼

>調べた限りではSQL Serverでは出来ないようです。すみませんでした

http://support.microsoft.com/kb/323630/ja
ROWLOCK などのロック ヒントを使用しても、最初のロック プランが変更されるだけです。ロック ヒントでは、ロックのエスカレーションは防止できません。

すいません、確かに使えないようですね・・・

>まじめに選択範囲を分割して(TOP (n)で出来ると思うのですが検証できていません)ロック時間を短く>するしかないようです。

これを一度試してみます。
ありがとうございました。

補足

御回答ありがとうございます。

参考意見頂けるだけでも有り難いことなので、
感謝しています。
逆に何度も申し訳ないです・・・

＞図したのは
＞選択行(レコードセット)に対しては結果的にはロックしないが、該当行を読むときだけはロックする。
＞というものです。

すいません、質問なのですが、

＞該当行を読むときだけはロックする。
＞というものです。

イメージだとROWLOCK は行ロックのイメージだったのですが、

＞調べた限りではSQL Serverでは出来ないようです。すみませんでした

以前UPDATEで使用した際には
WITH (ROWLOCK)が使えたと思うのですが
UPDATEしか使用出来ないとかですか？？

回答No.5

いけだ ひろし（@ike-2000）

select文は
インデックス→データ
の順でアクセスしますが、update文は
データ→インデックス
の順で更新するため往々にしてデッドロックとなります。

必要なければselect文でのロック(標準では掛かります)をはずすことが対策です。
行単位のロック(ROWLOCK：1行の読み込みはロックする)に粒度を小さくすれば解消すると考えますが。

補足

御回答ありがとうございます。

>select文は
>インデックス→データ
>の順でアクセスしますが、update文は
>データ→インデックス
>の順で更新するため往々にしてデッドロックとなります。

有力な情報ありがとうございます。

＞行単位のロック(ROWLOCK：1行の読み込みはロックする)に粒度を小さくすれば解消すると考えますが。

SELECT文に　HEAD with (ROWLOCK)を追加し
行ってみたのですが、やはりデッドロックは発生致しました・・・

もしかしたら原因自体違うのかもしれないので

HEAD with (NOLOCK)にして検証してみます。

回答No.4

taka451213

こんにちは。

＞テーブルに(UNLOCK)を行いダーティーリードしてしまえば
＞ぶつからないかなと思うのですが、いかがでしょうか？？
ちょっと短絡的過ぎると思いますが・・・。

ブロッキングが多発する・・・。
（これは並列処理をやってたら普通に起こるので、気にしなくてもいいです）
デッドロックが検出される・・・。
（ここ！！）

ここをきちんとトレースして、原因を見つける事が先決です。
仕様的にダーティーリードでも全然OKというなら問題ないですが、
リソーススタベーションが原因の場合、他の箇所でも発生するかも知れません・・・。
そのときになっても、UNLOCKで回避しますか？？？

補足

御回答ありがとうございます。

＞ちょっと短絡的過ぎると思いますが・・・。

自分でもそう思います・・・
とりあえず1回UNLOCKにしてみてデッドロック発生するかテストを
してみます。

同時に
＞リソーススタベーションが原因の
こちらももう少し調査をおこなってみます。

回答No.3

taka451213

こんにちは。

＞必要な個所ありましたら貼り付けますので申しつけ下さい。
必要な箇所は「全部」ですね・・・。
ログだけでなく、ハード的な環境やそれぞれの設定値、
アプリ側の作り、SQL、トランザクション管理・・・。
結局、全部見てみないとわからんです・・・ハイ。
今回提示されている並列処理では、デッドロックにはならないです・・・。
ブロッキングのみが発生し得る・・・かな。

＞解放をしないとデッドロックが発生するものなんでしょうか？？？
発生する可能性は十分にあります。
SQL Serverは、SELECTだけでもロックエスカレーションが発生しますし・・・。
いわゆるデッドロックは、お互いが「待ち合い」で発生しますが、この待ち合いは、トランザクションのロックレベルだけでなく、メモリー、プロセス、スレッド、その他すべてにおいて発生します。
リソーススタベーションに陥っている可能性もあります。

すごく極端な例ですが・・・、
並列処理で、たくさんのプロセスが実行された場合にブロッキングが発生した。
ロックタイムアウトになったので、ロールバックしようとした。
ロールバックするためのリソースがないので、誰かがリソースを開放するまで待つしかない・・・。
デッドロック・・・。

デッドロックの原因究明と解決は、全部見てみないとわからないです・・・。
少なくとも、現在提示されている内容だけでは、まったく判断できません。

補足

何度もありがとうございます。

少ない情報ですいません・・・
そして色々教えて頂きありがとうございます。

解放をしていないか箇所ないか、一度ソース確認してみます。

DBの方ですが、いっその事
SELECTかけている方は集計しているだけなので、
テーブルに(UNLOCK)を行いダーティーリードしてしまえば
ぶつからないかなと思うのですが、いかがでしょうか？？

回答No.2

taka451213

こんにちは。

これは1204のトレース結果ですかね？
SELECT→（S）→HEAD（プラスアルファ）
UPDATE→（X）→HEAD
このような関係では、どちらが早くてもデッドロックにはなりません。
遅い方が「待つ」だけです。

トレース結果が非常に断片的で、これだけではちょっとわかりませんが・・・、
問題は「スレッドリソース」か「ロックリソース」の不足にあるのでは？
前述したように単体テーブルに対する並列処理では、いわゆるトランザクションレベルのロックは発生し得ません。
リソースの開放など、アプリ側を見直す必要があるかも知れません・・・。
デッドロックチェインのログはないんですかね？

補足

御回答ありがとうございます。

＞トレース結果が非常に断片的で、これだけではちょっとわかりませんが・・・、
＞問題は「スレッドリソース」か「ロックリソース」の不足にあるのでは？

断片的ですいません。
大量に出ているもので・・・
必要な個所ありましたら貼り付けますので申しつけ下さい。

＞デッドロックチェインのログはないんですかね？

トレースフラグは1204,1205,3605をONにしています。
2111-16-55 09:27:49.25 spid1 End deadlock search 1475 ... a deadlock was found.
上記内容が出力されています。

＞リソースの開放など、アプリ側を見直す必要があるかも知れません・・・。
＞デッドロックチェインのログはないんですかね？

すいません、単純にDB詳しくない為質問なのですが、
今の現象はアプリ起動している最中にデッドロック発生しましたとWINDOUWSエラー表示がされます。
リソースの開放という事はソース内にFreeして解放していないというこでしょうか？？
解放をしないとデッドロックが発生するものなんでしょうか？？？

回答No.1

taka451213

こんばんは。

これだけではなんとも・・・ですね。

＞SELECT文には特にロックかけていない為
この場合は暗黙的に共有ロックですが。

＞SELECT文を読んでいる時にUPDATE文によって
＞レコードに変更があった為、デッドロックが発生したのでしょうか?
これだけではデッドロックになり得ません。
単純にUPDATE側が「待つ」状態です。
待ち切れなければタイムアウト・・・。

SELECTによる共有ロックとUPDATEによる排他ロックは共存できないので、UPDATE側を更新ロックに変更するか・・・。
根本的に１テーブルではデッドロックにはならないでしょ？
詳細が不明なので、具体的な解決策はわかりません・・・。

お礼

御回答ありがとうございます。

情報が足りずすいません。
補足です。

＞単純にUPDATE側が「待つ」状態です。
＞待ち切れなければタイムアウト・・・。

それがプログラムは主にSELECTをしている集計
の方でデッドロックが発生しています。

ログの見方を間違えているのかもしれませんが、下記がログ抜粋部分になります。
Input Buf: Language Event: SELECT H.AA,H.BB・・・・・・・・・
がありその後に
2100-22-15 09:27:49.25 spid1 Producer: Xid Slot: 1, EC = 0x8f22000, SPID: 1, ECID: 3, Blocking
という感じで9回ほどブロックされた後に
2111-18-88 09:27:49.25 spid1 Input Buf: Language Event: UPDATE HEAD SET NO = '', CODE = '・・・・
とあります。

＞根本的に１テーブルではデッドロックにはならないでしょ？
＞詳細が不明なので、具体的な解決策はわかりません・・・。

UPDATEを行っているプログラムがHEADテーブルのみ更新
SELECTをかけていますプログラムがJOINTを行いHEADテーブルを含み４テーブル程
一度に呼び出しています。

すいません、宜しくお願い致します。