インストールタイプの SQL Server のトランザクション分離レベルのデフォルトは SNAPSHOT が使用されない、READ COMMITTED が使用されており、データの検索を行った際にはロックが取得されるというのが有名な話だと思います。
N_NATIONKEY が主キーとなっているとき、次のようなクエリを実行した場合、どのようなロックがとられるでしょうか？
SELECT * FROM NATION WHERE N_NATIONKEY = '0'
READ COMMITTED が使用されているので「主キーに対しての検索なので主キーまたは検索対象のレコードに対して共有ロックが取得される」と考えることがあるのではないでしょうか？
実際には、「取得されるロックは状態によって変わるため、主キーによる検索でも、共有ロックが取得されないケースがある」というのが正解となります。
本投稿ではこの動作についてまとめてみたいと思います
主キーの検索で取得されるロック
実際に取得されるロックを確認した結果が次の画像となります。
主キーである、N_NATIONKEY で検索を行っていますが、取得されるロックは、
- DATABASE : S
- OBJECT : IS
- PAGE : IS
となっており、「KEY : S」は取得されていないことが確認できます。
SQL Server のロックの動作については、トランザクションのロックおよび行のバージョン管理ガイド で解説が行われています。
SQL Server のロックの取得は 動的ロック となっており、動作仕様としては次のようになります。
SQL Server データベース エンジン は、動的ロック ストラテジによって最もコストの低いロックを判断します。 SQL Server データベース エンジンはクエリを実行する際に、スキーマおよびクエリの特性に基づいて最適なロックを自動的に判断します。 たとえば、インデックス スキャンの実行時に、インデックス内でのページレベルのロックが選択されます。これにより、ロックのオーバーヘッドを減少させることができます。
現在の状態に応じて、最もコストの低いロックが取得されます。
そのため、READ COMMITTED で主キーによる検索を行っても、データの状態によっては、キーロックが取得されないケースがあります。
主キーによる検索でキーの共有ロックが取得されるケース
先ほどと同様のクエリを再度実行してみた結果が次の画像となります。
先ほどとは異なり、次のロックが取得されています。
- DATABASE : S
- OBJECT : IS
- PAGE : IS
- KEY : S
今回の検索では「KEY : S」が取得されています。
トランザクション分離レベルは先ほどと同一で、READ COMMITTED を使用しているのですが、取得されるロックが異なっていますね。
先ほどと異なる点としては、他のセッションで次のクエリが実行されていることです。
BEGIN TRAN UPDATE NATION SET N_COMMENT = NEWID() WHERE N_NATIONKEY = 1
今回、格納されているデータは次のようになっています。
「N_NATIONKEY = 0 , 1」のデータは、「8081 ページ」という同一のページに格納されているデータとなります。
検索対象としているのは「N_NATIONKEY = 0」のデータとなりますが、このデータの検索をしようとした際には、同一ページに格納されている「N_NATIONKEY = 1」のデータが更新中となっていました。
このような「検索対象と同一のページ内の他のデータが、他のセッションによって更新中」となっている場合は、「KEY : S」が取得されます。
しかし、「検索対象と同一のページ内の他のデータが、他のセッションで操作されていない」状態の場合は、「PAGE : IS」までのロックで、整合性が担保できると判断され、「KEY ：S」が取得されないというような動作となっています。
まとめ
READ COMMITTED を使用している場合「主キーに対しての検索なので主キーまたは検索対象のレコードに対して共有ロックが取得される」ケースも確かに存在するのですが、「すべてのケースにおいて KEY : S が取得されるということではない」ということは、本投稿のような内容で確認を行うことができます。
「こういうような動作になる」と思いこむのではなく、実際にどのような動作となることを確認することが重要ですね。