Q&A
[HSH20]およびそれぞれで発行しているSQL文に関してのロックに関する情報を追記して下さい。
Webアプリを開発しておりますが、度々デッドロックが発生します。
毎度ではありませんのでタイミングによって発生するものと思われます。
原因を調べておりますが特定に至らず皆様に質問させて頂きます。
開発環境
IIS
ASP.NET
C#
SQLServer
SQLServer Profiler
デッドロック時の情報です。
左側はSELECTでテーブル[HSH20]を含む複数テーブルを結合して複数行を取得
右側はINSERT(DataAdapter)でテーブル[HSH20]に複数行を挿入
※こちらはProfilerにてSQLが表示されたため特定できました。
分離レベルとロック
分離レベル
Read Committed
SELECT文
指定無し(共有ロック？)
INSERT文
実際はDataAdapterが処理してINSERT or UPDATEを決めると思われるので
事前に更新対象データを検索して更新ロック[UPDLOCK]を付けてSELECTしています。
実施したこと
発生当時に使用されていたインデックス(Index Name)が
どちらもクラスタ化インデックス(主キーのみ)を使用していたので
SELECT文に合わせて非クラスタ化インデックス(IDX_HSH20_02)を作成しましたが
前述したProfilerの通り、解決には至りませんでした。
有識者の方にご教示頂きたいのですが、不足している情報もあるかと思いますので
その際は記載頂ければ必要な情報を提示致します。
どうぞよろしくお願い致します。
回答3件
0
select 側で[HSH20]にロックヒントでWITH (NOLOCK)を指定されてはどうですか。
以下参考（質問での状況には直接関係はないと思いますが）
SQLServer: with(nolock)ヒントでロックを確実に回避できるという認識は間違い
追記
ご存知かもしれませんが見直しの参考に
デッドロックの最小化
※「行のバージョン管理に基づく分離レベルの使用」が適用できるなら良さげですけど。
投稿2018/06/13 01:44
編集2018/06/13 03:13総合スコア25173
回答ありがとうございます。
NOLOCKを指定すれば共有ロックを掛けなくなるとは思いますが、他SQLや他テーブル参照時にも同様の事象が発生していることがわかったため、そこに全てNOLOCKを付けるのは最終手段だと考えております。
単にselectしているだけの局所的な話でなければ、全体の見直しですね。
現情報で、私からはこれ以上提供できる情報は無さそうです。
そうですが、ありがとうございました。
最後にご質問させて頂きたいのですが、ProfilerのOwnerModeとRequestModeの意味をお分かりでしたら教えて頂けないでしょうか？
先行（OwnerMode）と後続（RequestMode）と捉えていますが。
他の回答のコメントで
>インデックスが関連したデッドロックの可能性もあるような
と言われていますが、インデックスのみを作成する際という事でなければ、デッドロックにインデックスは関係ありません。
何でインデックスを作り変えているんだろうと思っていましたが、「キー範囲ロック」の範囲という言葉で、クラスター化インデックスが関係していると思われているのではありませんか？
（何か関係した文献がありましたか？）
インデックス間デッドロックについてのサイトを見つけまして、サイト内のProfilerの画像も本件と似ていることからそう思いましたが、まったく確証はありません。
http://memorandom-nishi.hatenablog.jp/entry/2017/07/02/155841
解決策のところでは実証検証されていはいませんよね。
アクセスプランがそうなっているだけで、インデックスを変えたらロックが変わるほうが恐ろしいです。
私もsazi様と同様の考えを持っておりましたが、MSサポートでもインデックスがデッドロックを発生させる原因の一つになっているとの記載を見つけました。
「デッドロックの解決方法の推奨」項目あたりです。
https://support.microsoft.com/ja-jp/help/832524/sql-server-technical-bulletin-how-to-resolve-a-deadlock
その例は、更新がぶつかった際に、複数のインデックスがあり競合してデッドロックが発生していて、片方を削除することでデッドロックを回避という内容ですよね。
今回のインデックスを増やすという対応では、リンク先と同じ状況だとデッドロックが発生する機会を増やしていることになりませんか？
また、少なくともキー範囲ロックには関係がないように見えます。
先述した内容は、本件に関係ある・なしに「インデックスがデッドロックを発生させる原因の一つ」になり得るという意味で返信させて頂きました。
また、インデックスを増やしたのはこのサイトを見つける前です。
sazi様から頂いたREAD_COMMITTED_SNAPSHOTを試してみようと思いますが
READ_COMMITTEDと比較してプログラム上で考慮すべき点などありますか？
インデックスがデッドロックに関係するというのは理解しました。
考慮すべき点については、
効果を検証した上で、全体的なトランザクション分離レベルを確認して問題ないかどうかを検討する。
というのが思いつくところです。
回答ありがとうございます。環境を構築次第、検証したいと思います。
0
こんにちは。
以前、複数テーブルを結合した場合に排他がかからないっていう人が質問を
どこかで投げてたような気がします。
場所は忘れましたが・・・
初歩的確認ですが
１．デッドロックがかかっているのは、基幹テーブルですか？
それともそのまわりの結合しているテーブルでしょうか？
２．排他ロックかけていますでしょうか？
３．結合せずに基幹テーブルだけの場合はどうなりますか？
投稿2018/06/13 01:14
総合スコア177
回答ありがとうございます。
１．デッドロックは基幹テーブルですが、Profilerにもある通り、インデックスが関連したデッドロックの可能性もあるような気がしています。
２．排他ロックはDataAdapterが挿入・更新時にロックを掛けると思います。DataAdapterが自動でINSERTとUPDATEを発行するので、詳細は不明ですが、更新時の場合は事前にSELECTで更新ロックを掛けています。
３．実運用環境で発生している事象なのと、常に発生しない事象のため、検証に時間が掛かりそうです。
>質問をどこかで投げてたような気がします。
これですか？
[【SQL Server】同じ行をロックしているはずだが、SELECT時に排他エラーが発生しない？](https://teratail.com/questions/79936)
これです。
0
トランザクションの一貫性を保証するロック のSET TRANSACTION ISOLATION LEVEL のところが参考になるかと。
SQL Serverで「デッドロック」を回避する も読むと良いでしょう。
投稿2018/06/13 01:26
総合スコア16415
あなたの回答
tips
プレビュー
