TimberLandChapel.com Community Stage

複数のサーバーで１つのＤＢに処理を行っています。ＳＥＬＥＣＴ、ＩＮＳＥＲＴ、ＵＰＤＡＴＥ、ＤＥＬＥＴＥを実行しています。

バージョンアップに伴い、Oracle9iからSQL Server 2005に変えました。

問い合わせの１つに１行しかないテーブルに対する処理があり

競合しないようにテーブルロックを使いました。

他のテーブルの問い合わせには行ロックを付けています。スナップショットの分離レベルは変えていません。

ところがＳＥＬＥＣＴ結果が返ってこない現象が発生しました。

この現象がどのSQL、どのサーバーのデッドロックなのかを調べたいと思います。

SQL SERVER Profilerでひっかけるイベントは何にするのがよいでしょうか？

あまりたくさんつけるとＤＢのスペックに影響を与えるのでしょうか？

よろしくお願いします。

お疲れ様です。TimberLandChapel です。

採番テーブルのロックの問題でしょうか？ TABOCK の共有との競合ですかね？

まぁ，ひとまずご質問の趣旨に。

• LOCK 関係から Deadlock chain，Deadlock， Locktimeout だけ追加
• Session はこの際省いてしまう

ぐらいでよいと思います。

あまりイベントを取りすぎると，後でフィルタするのが面倒になります。もちろん，トレースする行為自体がサーバーに負荷をかけますから，なんでもかんでも取り続けるというわけにはいきません。

老婆心ですが，最低限の状況を把握するトレースをとって，あとは机上デバッグをお勧めします。

お返事ありがとうございます。

やってみたところ、デッドロックのイベントは拾えませんでした。

例えば、１台目がテーブルにロックをかけたまま処理をずーっとやっていて

他のサーバーがそのテーブルのロックを取りに行った場合

後のサーバーが待ちになりますか？

これが発生するSQLとロックされているかどうかを拾いたい場合は

トランザクションイベントも取得したほうがよいですか？ 

お疲れ様です。TimberLandChapel です。遅くなってすいません。

デッドロックの場合は，SQL Server が片方のプロセスを落としてしまうので，永遠に待ち続けるということはないですね。

すると，この場合はタイムアウトしない待ち状態になっている疑いを次に考えます。

すると，Blocking を探すことになりますので，

• ブロック状態のプロセスを判断する閾地を設定して
• Errors and Warnings の Blocked Process Report をとる

とできます。

ちなみに閾地設定のスクリプトの参考

--詳細オプション on
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;
GO
--詳細オプション 終わったら戻す
sp_configure 'show advanced options', 0 ;
GO
RECONFIGURE ;
GO

ロックの互換性についてはこちらを。

http://msdn2.microsoft.com/ja-jp/library/ms186396.aspx

いかがでしょう？

遅くなりました。お返事ありがとうございます。

タイムアウト待ちを調べてみようと思います。(今日はお休みでした）

これだった場合の解決法ですが、

分離レベルの変更というのが考えられますか？

そうではなくて、プログラムやサーバーの他のアプリの設定変更が

解決方法になるのでしょうか？

SQL Serverのロック問題と調べると、よく分離レベルの話が出てきます。

これはデッドロックが起きる場合に変更するといいと思っています。

考え方が間違っていますか？

よろしくお願いします。

 実行しているプログラムの説明に間違いがありました。

プログラム内で実行されるSQLはタイムアウト =  0 にしてから実行され

誰かにロックされていた場合は、スリープ後リトライという方法をとっています。

３０回のリトライでも実行できない場合に、「ロック要求がタイムアウトしました」が出力されます。



 

お疲れ様です。TimberLandChapel です。

すいません。ちょっと海外に出てるので返信が遅れます。

さて，

やはりロックタイムアウトというか，ロック待ちで待ち続けているということのようですね。

直接の回答としては，あくまで一般的にはということですが

• 分離レベルを変更するということでもない
• サーバーの設定を変えるということでもない

じゃぁどうするか？

• アプリケーションで取得するロックのデザインを再検討する

というところでしょうか？

例えば，

今の段階で，

採番テーブルから新番号を取得して，

その番号を取得した処理がコミットされるまでロックをリリースをしない。

という様にデザインしているのであれば，

採番の要件が「一意性を確保さえすればよい」ということであれば，

番号を取得した時点でロックは開放してしまう。そして，もしその番号を取得した処理がロールバックされてしまったら，そのままその番号も捨ててしまう

というようにすればロック待ちを低減することもできるはずです。

しかし，まぁこれは取得する番号に穴があってはいけない完全な連番にする必要がある場合にはできないですね。

という様に，

「本当にロックを必要としているのはどこまでの処理なのか？」

という，業務要件としてのトランザクションの粒度を再検討するのが道だと思います。

いかがでしょう？

お忙しい中お返事ありがとうございます。

残念ながら、システムの変更はもうできないのです。実装中に気づけばよかったのですが。

ロックの単位の見直しは行いましたが、これ以上は小さくできないようです。 

ロック取得中の処理が、途中で割り込まれてロックを持ったまま待機。

そして、割り込んだ処理がロックにぶつかりタイムアウト。

悪循環です。

最初の処理は、なぜ、SQLの処理が終わっているのにロックをすぐ解放してくれないのでしょう？

SQLプロファイラの結果を見てそんな疑問が出てきました。

プログラムに手を入れないと回避できないのでしょうか？

Oracleの時はこんなことなかったのに・・・

＞ロック取得中の処理が、途中で割り込まれてロックを持ったまま待機。

＞そして、割り込んだ処理がロックにぶつかりタイムアウト。

 お疲れ様です。TimberLandChapel です。

うーん。この内容からですと，確かにデッドロックのように聞こえますが，

やはり，

ロックを取得したプロセスが何を待っているのか？

を，探り当てるのが先決ですね。

ロックを解放していない，というのは特別なロックヒントを与えない限り通常はあり得ないですから，

デベロッパーな私としては，やはり外科手術をしてしまう方が早い気がしてしまいます。

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

ロックが取れなくても一定時間リトライをするが、だんだん複数サーバーの処理タイミングが同じになってきて

誰も何もできなくなってしまうようです。

いよいよ構造見直しの声が上がってきました。

対策①タイムアウト=0の宣言をやめ、素直にロック待ちにさせてみる。→Oracle時代のNOWAITにしたがうまくいかないので。デッドロックしてしまうかな？

対策②ロックが取れなかった場合は、ロールバックをしてスリープ後やり直す。→全体的に処理が遅くなりそう？

対策③Oracleで実装し直す。→SQL Serverで何とかやりたいです。

問題になると思うのは３つのテーブルを更新するときに、

SQLを実行して初めてロックされていることがわかる処理構造だと思います。

一般的に複数アプリからこういった処理をする場合は

どうやって排他制御とはじかれた時のリトライを制御しているのでしょうか？

お疲れ様です。TimberLandChapel です。

 一連の処理をどうしても一連のロックとして取りあつかわなければならない場合は，

とくに，その一連の処理の入り口が完全に排他であるという場合は，

・SQL Server にはアプリケーションロックという方法もあります。

または，

・処理のゲートになるトークンをどこかのテーブルに用意して，そのトークンが取れない場合は処理しない

というようにすれば，ほかの実際に必要になるテーブルにロックをかけなくてすみます。

うーん。。。

しかし，やはり本当に完全に排他なのか？ という観点で疑ってみるのがいいんですけどね。

お世話になっております。

アドバイスありがとうございます。

排他制御は２台以上のサーバーが同時に１レコードを更新しないようにするために

必要だと思っています。

ゲートを置く方法で考えてみようかと思います。

その際もタイムアウト時間は０の方がよいのでしょうか？

更新に失敗した場合でも何とか処理は続行させてあげたいシステムなので

トークンがとれなかった時のチャレンジは

トークンを取得してからの処理時間を考慮してスリープさせようと考えています。

よろしくお願いします。

お疲れ様です。TimberLandChapel です。

もし可能であれば， 

トークンの取得待ちはタイムアウトを許容できる範囲で小さくしてあげた方がよいと思います。

というのも，

スリープを実装しておられるようなので，

ロックが取れなかったら，アプリケーション側できちんとリトライをしてあげるということができます。

なので，ロックが解放されるまで待ち続けるというのはあまりよろしくありません。

しかしながらリクエスト順を制御しなければならなかったりするとより一層複雑になってきてしまうんですけどね。。。

（こうなってくると，単純なトークン制でなくて，キューをつくらなくてはいけません）

 お世話になっております。お返事ありがとうございます。

SELECTの中のSELECTが別サーバーの処理とでデッドロックを

起こすことが発覚しました。

これを修正して、タイムアウトを少しとってみようと思います。

ありがとうございました。