SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server で Seek によりREAD COMMITTED で 1 行取得する際には行ロックが必ず取得されるということではないというお話

leave a comment

SQL Server で C1 をクラスター化インデックスとして設定されている次のようなテーブルがあるとします。

image

SQL Server のデフォルトの READ COMMITTED 分離レベルが設定されている状態に対して、次のクエリを実行した場合にどのようなロックが取得されるのかというのが本投稿の内容です。(SQL Server のデフォルトの動作であるため、READ COMMITTED SNAPSHOT ISOLATION (RCSI) は無効の状態です)

SELECT * FROM [dbo].[CT_01] WHERE C1 = 100001

 

C1 に対してクラスター化インデックスが設定されていますので、「C1 = 100001」のレコードに対して、Key Lock (行ロック) が取得されると思うかもしれませんが、「取得されているロックの状態で変わり、Key Lock が取得されないこともある」が答えとなります。

取得されているロックの状態によっては、上記の SELECT を実行した際に Key Lock は取得されず、データの検索が行われることもあります。

本投稿では、Clustered Index Seek により、ピンポイントで 1 件のデータを取得する場合に、必ず Key Lock が取得されるという考えは誤りですということを書いておきたいと思います。

SQL Server は動的ロックにより取得されるロックが判断される

SQL Server のロックの挙動については、トランザクションのロックおよび行のバージョン管理ガイド に記載されています。

この中で今回の挙動に影響をするのが 動的ロック の記載です。

SQL Server は「動的ロック」により、自動的な最適なロックの取得が行われます。

SQL Server データベース エンジン は、動的ロック ストラテジによって最もコストの低いロックを判断します。 SQL Server データベース エンジンはクエリを実行する際に、スキーマおよびクエリの特性に基づいて最適なロックを自動的に判断します。 たとえば、インデックス スキャンの実行時に、インデックス内でのページレベルのロックが選択されます。これにより、ロックのオーバーヘッドを減少させることができます。

SQL Server は動的ロックにより最もコストの低いロックの取得が行われます。

そのため、Clustered Index Seek により 1 件のデータを取得する場合にも「その時に取得されているロックの状態」によって、検索により取得されるロックが変化します。

Clustered Index Seek により取得されるロック

それでは、実際に取得されるロックを確認してみたいと思います。実行するクエリは冒頭に記載した次のクエリです。

SELECT * FROM [dbo].[CT_01] WHERE C1 = 100001

自身以外に該当のデータに関係するロックを取得されていない場合には、上記のクエリでは、次のようなロックが取得されます。

image

image

これらのロックが取得されていることが確認できます。

自身以外がデータの操作を行っていない場合、該当のデータに対してはロックの取得は行われず、データが格納されているページに対してのみ IS のロックの取得が行われます。

Key Lock については取得されておらず、該当のデータに対して直接のロックは取得されません。

Cluster Index Seek により Key Lock が取得されるケース

それでは、Cluster Index Seek により、Key Lock が取得されるのはどのようなケースがあるでしょうか?

一般的なケースとしては「他のセッションでロックが取得されている場合」です。

SELECT を実行するセッションとは別のセッションで次のクエリを実行しておきます。

BEGIN TRAN
UPDATE [dbo].[CT_01] SET C2 = NEWID() WHERE C1 = 100002

 

この場合、次のようなロックが取得されます。

image

UPDATE を実行することで該当のデータに対して KEY:X のロックが取得されます。

image

SELECT を実行するのは「100001」ですが、同一のページ内に格納されている「100002」に対して「Key:X」が取得されている状態です。

この状態で、「100001」を検索を検索した場合には「KEY:S」が取得されます。

image

image

他のセッションのトランザクションによってページ内の操作対象外のデータに対して排他ロック (Key:X) が取得されている場合は、SELECT による「Key:S」が取得されます。

「Key:S」が取得されるかどうかについては、トランザクションの状態によって変わりますので、Seek による 1 件のデータ取得で必ず「Key:S」が取得されるということはありません。

まとめ

SQL Server は「動的ロック」により、処理実行時に適しているロックの取得が行われます。

取得されるロックは他のセッションのトランザクションの状態によっても変化しますので、「必ずこのロックが取得される」とするのではなく「想定されるワークロードで取得されるロック」を意識して、「どのようなワークロードで取得されるロックを確認すればよいのか」を意識することが重要です。

Share

Written by Masayuki.Ozawa

9月 25th, 2021 at 2:13 pm

Posted in SQL Server

Tagged with

Leave a Reply