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

5778 ワード
SQLServer SQL ロック DB SQL テキストリンク

SQL Serverのロックについて出来る限り分かりやすく解説という記事を書いた後に、追加で説明しておきたい事項をこちらにまとめていきます。

ロックまわりで知りたいことや疑問があればコメントで教えてください。ベストエフォートで追記させていただきます。

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

ブロッキングのおさらい


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

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

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

シナリオ１．互換性のないクエリ実行

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

  • クエリAが実行中
  • そのあと、クエリBを実行したが、クエリAと互換性がないためブロッキングされて、待ち状態が続いている
--クエリA
select * from SomeTable

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

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

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

シナリオ２．互換性のあるクエリ実行

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

  • クエリAが実行中
  • クエリCを実行すると、クエリAと互換性があるためすぐに実行完了する
--クエリA
select * from SomeTable

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

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

シナリオ３．シナリオ１のブロッキング発生中にクエリBを実行

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

シナリオ１でブロッキングが発生している場合に、クエリ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など、強力なロックを獲得する必要がある処理については、以下のような工夫が必要となるケースがあるかと思います。

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