想定外のブロッキングに注意する

既にS Lockをかけていた場合は、X Lockはかけられない。このとき、X Lockをかけるためにクエリが待ち状態になる。
→これが「ブロッキング」

クエリレベルだと、「次の二つのクエリは同時に実行できない」という意味。

この「互換性がないロックリクエストは競合し、ブロッキングにつながる」という挙動を踏まえて、想定外のブロッキングが発生する状況について説明します。

まず、以下の状況を想定します。

--クエリA
select * from SomeTable

--クエリB
alter table SomeTable add Column2 int

SSMSで再現した動画です。クエリAによってSロックを取り続けていると、クエリBがブロックされ続けることが確認できます。

（※クエリAは瞬時に実行が完了してしまうため、Sロックをかけ続けるためにトランザクション分離レベルを変更しています。）

次に、以下の状況を想定します。

--クエリA
select * from SomeTable

--クエリC
select * from SomeTable where Column1 = 1

SSMSでの再現です。クエリCが瞬時に実行完了することが確認できます。

ここからは想定外な挙動と感じる方もいらっしゃると思います。

シナリオ１でブロッキングが発生している場合に、クエリCを実行するとどうなるでしょうか？
シナリオ２では、クエリAとクエリCは互換性があるため、クエリCは瞬時に実行完了していました。

--クエリA
select * from SomeTable

--クエリB
alter table SomeTable add Column2 int

--クエリC
select * from SomeTable where Column1 = 1

SSMSで再現させた動画です。今回は、クエリCもブロックされてしまっています。

実際にロックをかけているのはクエリAのSロックだけなのに、なぜクエリAと互換性のあるクエリCまでブロックされたのでしょうか？

この挙動は、SQL Serverのロックに関連する仕様によるものです。
ロック待ちのリクエストはキューで管理されます。
このとき、「自分よりもキューの先頭側にあるロックリクエストは、すべてロック獲得済みのようにみえる」という挙動になります。

シナリオ３を例にすると、以下のようなイメージになります。

クエリCは、実際にロックを獲得しているクエリAとは互換性があるのですが、クエリCよりも先にロック獲得のためのキューにはいったクエリBのSch-Mロックと互換性がないため、クエリBが待ち続けている限り、クエリCもブロックされ続けます。

すべてのロックモードと互換性が無い「Sch-Mロック」の獲得待ちでブロックされてしまうと、後続のあらゆるクエリがブロックされてしまいます。このロックは主にALTER TABLE等のDDLを実行する場合に獲得されます。

CREATE TABLEなど、CREATEする場合は他に同一オブジェクトを参照するクエリが発行されていないはずなので問題ないのですが、ALTER TABLEの場合は別のクエリが該当のオブジェクトを参照している可能性があります。

したがって、ALTER TABLEなど、強力なロックを獲得する必要がある処理については、以下のような工夫が必要となるケースがあるかと思います。

ALTERしたいテーブルを参照している、長時間実行中のクエリが無いかを必ず確認してからALTER文を実行する
「SET LOCK_TIMEOUT 1000」など、あらかじめ「〇秒間ロックをとれなかったらタイムアウトする」という設定を入れたうえで実行することで、想定外のブロッキングが発生しても影響を最小限に抑えられるようにしておく（参考：SQL Server: ブロッキングが発生しやすい環境でALTER系クエリを実行させるための方法について考えてみた）

あらゆるクエリが瞬時に完了しているような環境であればここまで神経質になる必要はありませんが、この挙動は覚えておいて損はないと思います。

続・SQL Serverのロックについて出来る限り分かりやすく解説