Microsoft SQL Serverのロックの仕組みについて

最近仕事でMicrosoftのRDBMS（データベース管理システム）である、SQL Serverを使っているのですが、ロックの仕組みについて個人的にはかなり驚くことがありましたのでメモ程度でまとめます。

結論

SQL Serverではデフォルトで、あるトランザクションAがUPDATE/DELETEなどで更新中の場合、別のあるトランザクションBはその更新中のデータに対してSELECTするとロック開放待ちになります。つまり、トランザクションAを開放（COMMIT or ROLLBACK）しないとトランザクションBは永遠と待ち続け、結果が返ってきません（タイムアウトを設定していたらそれまで）。

別のRDBMSであるOracleだったりすると、この挙動はなくて更新前のデータをすんなり返してくれるので少し驚くような挙動でした。簡単に以下で確認していきます。

環境

今回確認テストする環境は以下の通りです（全体的にちょっと古い・・・）。
・MicroSoft SQL Server 2016 Express（無料なので）
・MicroSoft SQL Management Server 2016（こちらも無料）※以後、SSMSと呼称

・OSはWindows8

windows8にSQL Serverをインストールしてローカルで確認していきたいと思います。

サンプルテーブル

あらかじめ、sampleDBという名前でデータベースを作成します（特別な設定はなにもしていないもの）。そして、サンプルで使用するテーブルt1は以下のようなカラムおよびデータをもっているものです（データ型とかは今回の話には関係ないので言及しません）。

レコードをロックする

ロックの確認を行うために、以下のような明示的トランザクションの定義BEGIN TRANを宣言し、UPDATE文で任意のレコードを更新します。
SQL Serverでは、更新系のステートメントを実行すると占有ロック（排他ロック）がかかるようです。そして、ロックの開放（COMMIT OR ROLLBACK）はしないで、ロックされたまま次に行きます。

レコードのロックを確認する

上記でUPDATE文により、ロックをしている状態なのでSSMSで別のSQLシートを開いて、該当のレコードに対してSELECTしてみましょう。特にオプションなども付与していない、普通の検索するSQLクエリです。

おや？？？ちょっとわかりづらいですが、上記画面キャプチャの下部に表示されているメッセージの通り、クエリを実行しています...がずーっと表示されたままになっています。レコードは数件しかないので、帰ってきていないのは性能が悪いのではなくロックされているだなと推測できます。
※本当はロックされているレコード、しているレコードは調査できるのですが別の機会に譲ることにします。

さいごに

上記で見てきたように、SQL Serverでは排他ロックがかかったレコードに対してSELECTクエリをなげるとロックの開放待ちをしてしまいます。
ただ参照したいだけである場合は、無駄なロック待ちが発生することになるので同時利用の効率が下がることになります。

本記事のはじめのほうでも言及していましたが、Oracleでは上記で同じように操作してもロック開放待ちはしないで更新前レコードを検索してダーティーリード（確定していない更新中のデータを読み取ってしまう問題のこと）防ぐことができます。

上記のどちらかがいいのかは、そのシステムの業務の話なので一概には言えません。が、SQL Serverのデフォルトはロック待ちするので本来はこちらが望ましいのかもしれません。

今度は、SQL ServerでOracleと同じような読み取り一貫性の挙動ををする設定をしてみたいと思います。