SQLServerのwith (nolock)のデメリットを教えて下さい。

Question

SQLServerについて勉強中の者です。

先日、社内システムで、Ａさんが大量のデータを検索している間にＢさんが同じテーブルのデータを更新しようとしたところ、Aさんの検索が終わるまで長時間の待ちが発生。
連日発生してしまったため、これを改善するために、検索で使用しているSELECT文にwith(nolock)をつけるということに決まりました。

そこで2点質問があります。
[質問]
・with (nolock)をつけた場合、何かデメリットがあるのでしょうか。またそれはどういったケースで起きるのでしょうか。
・SELECT文にwith(nolock)をつけるかつけないか、一般的に作成時にどう判断しているのでしょうか。

どなたかご教授頂けると幸いです。

Comments

[takasima20]

それはAさんのロジックを見直すとこじゃないかなあ。つかデメリットと時間を天秤にかけた結果じゃないの?

[退会済みユーザー]

オンライン処理やバッチ処理が走ってる最中の検索では、どう頑張ってみても一貫性のないデータしかとれない。（XX:XX時点のデータなどといっても無意味）
あくまでも概数しか取れないので、with(nolock) の有無は意味がない。
ならば、本来のデータ処理の邪魔をしないように、with(nolock) を付けるのが当然。

[nana]

>takasima20様
コメントありがとうございます。
社内では、今回with(nolock)を採用した際のデメリットが特に出てこなかったため、with(nolock)を採用することになりました。が、社外の方の話も聞いてみたいと個人的に思い、質問させて頂きました。

＞iruyas様
なるほど。確かに本来のデータ更新を邪魔しないように検索側にwith(nolock)をつけるのは当然、というのはすとんと落ちました。

もし時間があれば追加で教えて頂きたいのですが、本来は、with(nolock)の話以前に、スナップショット分離レベルをデフォルトの「READ COMMITTED」から「READ UNCOMMITTED」に設定することが多いのでしょうか？（検討違いのことを言っていたらすみません。）

[退会済みユーザー]

本来のデータ処理の中では、with(nolock) を付けてはいけません。
長時間かかるようなら何かしらの設計ミスです。
臨時の検索のみ、with(nolock) を付けるということです。

[退会済みユーザー]

質問者さん、2022/03/03 22:30 の後無言ですが、上の質問に関しては話は終わっているならこのスレッドはクローズしてください。

もし、回答のコメントに書きましたチューニングと with (nolock) どちらの方向に進むのが良いかを議論したいなら、最初の質問と違うことですので新たに別スレッドを立てて質問願います。（質問者さんの会社の事情が分からない第三者がアドバイスするのは難しいとは思いますけど）

Answer 1

「SELECT文にwith(nolock)をつける」というのが起きている問題の解決になるのかどうかわかりませんが、それはちょっと置いといて・・・

with (nolock)をつけた場合、何かデメリットがあるのでしょうか。またそれはどういったケースで起きるのでしょうか。

ダーティリードになる可能性があることでしょう。（ダーティリードとはコミットされていないトランザクションでの未確定の変更を他トランザクションが読めてしまうこと）

SELECT文にwith(nolock)をつけるかつけないか、一般的に作成時にどう判断しているのでしょうか。

一般的には付けないで、デフォルト（READ COMMITTED) にしておくことだと思います。どうしても「SELECT文にwith(nolock)をつける」しか今起きている問題の解決にならないということなら話は別ですが (ホントにそうかよく考えた方が良さそうな気がしますが)。

自分が調べた限りですが、英文ですが以下の記事が一番分かりやすいと思いましたので、紹介しておきます。

SQL Server table hints – WITH (NOLOCK) best practices
https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/

Comments

[nana]

回答ありがとうございます。
なるほど、一般的には付けないんですね。with(nolock)は本当に最終手段として、チューニングをして検索速度を改善するということでしょうか？
参考になる記事もありがとうございます。読んでみます。

[退会済みユーザー]

チューニングとかは関係ないかと思います。

今回のケースでは、たぶん A さんが長時間ロックしたとかで起こした問題でしょうから、他の人が with (nolock) で対応するのは少々筋違いの話で、A さんやり方を見直すというのが良さそうと思います。

[nana]

すみません、私の書き方が誤解を与えたかもしれません。。
営業のAさんがデータの一覧を取得する必要があり、社内アプリの検索ボタンを押す。
その間に営業のBさんが別の画面でデータの更新をかけようと更新ボタンを押したところ、待ちが発生した、というような状況です。

営業のAさんの使い方は本来のアプリの使用方法で問題ないので、検索ボタンで実行されるSQLのチューニングが必要になるのかなと思ったのですが、、
なかなか理解できずすみません。。。

[退会済みユーザー]

B さんが長時間ロックして起こした問題であれば、A さん他の人が with (nolock) で対応するのは筋違いの話で（「検索ボタンで実行されるSQLのチューニング」とはするのは筋違いの話で）、B さんやり方を見直すべきでは？

B さんの仕事が最優先で B さんのやり方を見直す余地なんかないということであれば、ダーティリードの可能性を覚悟で、A さん他の人は with (nolock) で対応という話もあるかもしれませんが。

[nana]

Aさんが検索ボタンを押したため、SELECTで共有ロックが発生。（大量のデータ取得だったため時間がかかる。）→Bさんが更新ボタンを押すが、共有ロックがかかっているのでAさんのSELECT処理が終わるまで待ちが発生。という状況なので長時間ロックしたのはAさん側です。
なかなか説明が下手ですみません。もう1回整理して考えたいと思います。

[退会済みユーザー]

理解が遅くてすみません。

https://qiita.com/maaaaaaaa/items/38fd95b142b07acf7700 ←この記事の「例：S Lock（共有ロック）とX Lock(排他ロック）」のセクションの上の図で S Lock をかけているのが A さん、X Lock をかけようとしているのが B さんで、これが現状というわけでしょうか？

で、A さんのロックが長時間になって困っているので、A さんの SELECT クエリに with (nolock) をつけてもらって、上の記事の「例：with(nolock)をつけたクエリの挙動」のセクションのようにしたいということですね？

[退会済みユーザー]

何にしても上に述べた私の回答

> 一般的には付けないで、デフォルト（READ COMMITTED) にしておくことだと思います。どうしても「SELECT文にwith(nolock)をつける」しか今起きている問題の解決にならないということなら話は別ですが (ホントにそうかよく考えた方が良さそうな気がしますが)。

・・・は変わらないです。チューニングと with (nolock) どちらの方向に進むのが良いかは質問者さんの事情が分からない第三者には何とも言えません。

[nana]

いえ、私の説明不足です。。
まとめて頂いた通りの現状です。

今までSロックとXロックの競合で困ることがなかった（おそらく周りのエンジニアの方がそうならないようにしていたのでしょうね、、）ので、異動先でこういう時はwith(nolock)を使う手があると聞いてそれでいいのか、、？と思っていました。
今回質問させて頂いて、よく考えて使うか検討すべきものということが分かりました。
何回もお付き合いいただき、本当にありがとうございました。