SQL Serverでは、テーブルスキャンによるロック待ちに注意する必要があります。
私自身は、SQL Serverを使う前はOracleを使っていたのですが、このSQL Serverの挙動を知り驚きました。
テーブルスキャンによるロック待ちとは？
まずは、テーブルスキャンによるロック待ちについて簡単に動作を見ていきます。
以下のようなサンプルテーブルとデータを用意しておきます。
SSMSで新しいシートを開いて以下のように明示的トランザクションを宣言し、UPDATE文を実行します。ここでは、条件で「a=2」を指定して特定のレコードを更新しています。特に問題なく実行できました。
上記のトランザクションをコミットまたはロールバックせずにそのままにしておき、別途新しいシートを開いて同じテーブルを検索します。
以下が実行例ですが、条件で「a=1」を指定して特定の1レコードを検索しています。しかし、以下の画面の下の方を見ていただければわかりますが検索結果がかえってこきません。「クエリを実行しています...」が永遠と表示されます。
これが本記事で説明したいテーブルスキャンによるロック待ちになります。
今回のサンプルテーブルのように適切なインデックスが設定されていない場合、SQL Serverはテーブルの全行を検索する動きになってしまい、あるレコードがロックを獲得していた場合は別のレコードを検索しようとしてもロック待ちが発生してしまいます。
冒頭でもお話したとおり、これはOracleのデフォルトの動作では発生しない現象であるため、Oracleを使っていた人がSQL Serverを初めて使う時にかなり驚くような挙動になるかと思います。
このSQL Serverの挙動をやめて、Oracleと同じ挙動にしたい場合は「READ_COMMITTED_SNAPSHOT」という設定をONにすることで実現できます。
READ_COMMITTED_SNAPSHOTを設定する
上記までで見てきたとおり、Oracleと同様の動作にするには「READ_COMMITTED_SNAPSHOT」の設定が必要になります。この設定はデータベースごとに適用することができます。
SQL では以下のクエリを実行することで、READ_COMMITTED_SNAPSHOTの設定をONにすることができます。
ちなみに、元に戻す場合は"ON"を"OFF"に変更して実行すればOKです。
ALTER DATABASE [データベース名] SET READ_COMMITTED_SNAPSHOT ON;
また、本設定はSSMSで画面を操作して設定することも可能です。SSMSで設定するには、オブジェクトエクスプローラーから該当のデータベースを選択し右クリック、「プロパティ」を選択しデータベースのプロパティ画面を開きます。
「オプション」を選択し、右側のその他欄にある「Is Read Committed Snapshot On」がその設定項目になります。右のプルダウンから、「True」を選択して「OK」ボタンを押下することで設定をONにすることができます。
おわりに
いかがでしたでしょうか。この挙動を知らないと、なぜロック待ちが発生するか分からないことになりますので、SQL Serverを使うのであれば必ず知っておきたい挙動かと思います。
この「READ_COMMITTED_SNAPSHOT」の設定を行った場合、tempdbデータベースに更新前のデータを格納する挙動となるらしく、それに伴うオーバーヘッドが少なからず発生するみたいです。
そこらへんの理由もあり、デフォルトではOFFの設定となっているのでSQL Server側としてはこの機能は使用しないで、使ってほしいという意図なんでしょうか。
たしかに、適切なインデックスを付与していれば問題なかったりするので、そのシステムの特性やデータのアクセスする頻度など色々と考慮して設定するのが望ましい感じがしました。
実際の運用ではどの程度使われているのか気になるところですね。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。
