SQL Server / SQL Database の行のバージョン管理の基本動作 at SE の雑記

SQL Server / Azure SQL Database では、行のバージョン管理 (RLV : Row Level Versioning) と呼ばれる機能を使用することができます。

通常、データのアクセス時にはロックが取得され、特定のアクセスパターンでは、検索についても同時実行性の低下につながることがあります。
SQL Server のデフォルトの設定では、検索時にもロックが取得され、更新系の処理が実行されている場合は、ロック競合により、検索がブロックされる動作が行われることがあります。

このような検索時の同時実行性の低下を抑えるため、SQL Server では行のバージョン管理という機能を使用することができます。

これは、MVCC (MultiVersion Concurrency Control) により、読み取り時にはロックを取得しないようにすることで、読み込みと書き込みが同時に行われていても、競合を発生させず、同時実行性の低下を抑えることができる機能となります。

Azure SQL Database では、この機能はデフォルトで有効になっているのですが、SQL Server では 2005 から実装が行われたものとなり、現時点の最新バージョンの SQL Server 2019 でもデフォルトでも無効な状態となっています。

本投稿では、SQL Server ベースの環境で、MVCC を実現するための行のバージョン管理がどのように実装されているのかを見ていきたいと思います。

Contents

1 公式ドキュメント
2 行のバージョン管理の種類
- 2.1 スナップショット分離レベル
- 2.2 READ COMMITTED SNAPSHOT 分離レベル (RCSI)
3 スナップショット分離レベルと RCSI の違い
- 3.1 スナップショット分離レベルを使用する場合の注意点
4 WITH(NOLOCK) との違い
5 設定の有効化
6 行のバージョン管理の基本的な仕組み
7 行のバージョン管理のモニタリング
8 最後に

公式ドキュメント

最初に、この機能を利用しようとした場合に、どのような公式ドキュメントが公開されているのかをまとめておきたいと思います。
公式のドキュメントとしては、次のようなものが公開されています。

行のバージョン管理を使用する場合には、これらのドキュメントに目を通しておくと良いのではないでしょうか。

行のバージョン管理の種類

SQL Server の行のバージョン管理は 2 種類があります。

スナップショット分離レベル (Snapshot Isolation)
READ COMMITTED SNAPSHOT 分離レベル (RCSI : Read Committed Snapshot Isolation)

スナップショット分離レベル

スナップショット分離レベルはトランザクション分離レベルとして、明示的に指定することで、行のバージョン管理による検索を行うことができるようになります。

SQL Server のデフォルトのトランザクション分離レベルは「READ COMMITTED」となっています。
次のようなクエリを実行することで、分離レベルをスナップショット分離レベルに変更することができます。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

実際にセッションのトランザクション分離レベルを確認する方法としては次のようなクエリがあります。

SELECT 
    session_id,
    CASE transaction_isolation_level 
        WHEN 0 Then 'Unspecified'
        WHEN 1 Then 'ReadUncommitted'
        WHEN 2 Then 'ReadCommitted'
        WHEN 3 Then 'RepeatableRead'
        WHEN 4 Then 'Serializable'
        WHEN 5 Then 'Snapshot'
    END AS transaction_isolation_level
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT 
    session_id,
    CASE transaction_isolation_level 
        WHEN 0 Then 'Unspecified'
        WHEN 1 Then 'ReadUncommitted'
        WHEN 2 Then 'ReadCommitted'
        WHEN 3 Then 'RepeatableRead'
        WHEN 4 Then 'Serializable'
        WHEN 5 Then 'Snapshot'
    END AS transaction_isolation_level
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

トランザクション分離レベルを明示的に変更することで、セッションのトランザクション分離レベルが SNAPSHOT となり、検索については行のバージョン管理を使用した検索が行われるようになります。

READ COMMITTED SNAPSHOT 分離レベル (RCSI)

RCSI は SQL Server のデフォルトのトランザクション分離レベルである「READ COMMITTED」分離レベルを、「READ COMMITTED SNAPSHOT」に変更するものです。

スナップショット分離レベルで記載したクエリの実行結果から確認できますが、セッションの初期状態のトランザクション分離レベルは、READ COMMITTED となっています。

初期状態の READ COMMITTED 分離レベルは検索時に共有ロック (S) を取得する動作となります。

そのため、更新中のレコードを参照しようとした場合にはロック競合が発生します。

RCSI を有効にした場合、READ COMMITTED 分離レベルの検索時には行のバージョン管理を使用した検索となり、レコードに対して共有ロックを取得しなくなります。

スナップショット分離レベルの場合は、明示的にトランザクション分離レベルを変更する必要がありましたが、RCSI の場合は、デフォルトの READ COMMITTED 分離レベルの動作が変更になりますので、明示的にトランザクション分離レベルを変更しなくても、行のバージョン管理による検索が行われるようになります。

RCSI を使用した場合は、更新中のデータにアクセスした際に、今まではロック競合が発生し、検索がブロックされていたものが、更新前のレコードが参照されるようになるため、「アクセスされるレコードの操作が今までとは変わる可能性がある」というようなことが考えられます。

スナップショット分離レベルと RCSI の違い

行のバージョン管理については、スナップショット分離レベルと RCSI の違いですが、大きな内容としては次のようになります。

スナップショット分離レベル : 明示的に SNAPSHOT 分離レベルを変更した場合に、行のバージョンを使用した読み取りに変更する
RCSI : READ COMMITTED トランザクション分離レベルの動作を行のバージョンを使用した読み取りに変更する

行のバージョンを使用した読み取りを実施しようとした場合に、明示的にトランザクション分離レベルを変更する必要があるか、デフォルトの分離レベルで行のバージョンを使用するようにするかの違いが大きいのではないでしょうか。

RCSI を有効にした場合、 READ COMMITTED トランザクション分離レベルを使用している全セッションの動作が変更されることになります。

最初から RCSI が有効な状態で開発されていたシステムであれば、行のバージョンを使用した読み取りにを行ってしまって問題はないかと思いますが「既存のシステム」に対して RCSI を有効にすると、データアクセスの挙動が変わることになります。

今までは検索のロック競合が発生していたものが、ロック競合が発生せずに「検索時にコミットされているレコード」に対してアクセスが行われるようになります。

このようなデータアクセスが行われることに対して問題がないのであれば、RCSI を有効にすることで、実行するクエリを変更することなく、検索の同時実行性を向上させることができます。

RCSI を有効にできない場合は、スナップショット分離レベルを有効にすることにより、明示的にトランザクション分離レベルを変更した場合には、行のバージョン管理を利用できる状態にするということができます。

スナップショット分離レベルを有効にしても「明示的に SNAPSHOT トランザクション分離レベル」を使用していない場合は、行のバージョンを使用した読み取りは行われません。

必要なセッションのみ、行のバージョンを使用した読み取りができればよいのであれば「SNAPSHOT トランザクション分離を有効にし、セッション単位で明示的にトランザクション分離レベルを変更する」ことで、特定セッションのみ行のバージョンを使用することができます。

スナップショット分離レベル : トランザクションレベル / RCSI : ステートメントレベルというようなスコープの違いもあるかもしれませんね。

スナップショット分離レベルを使用する場合の注意点

スナップショット分離レベルを使用する場合には、更新の競合は意識しておく必要があります。

スナップショット分離レベルを使用したトランザクション間で更新が競合した場合、コミットをしたタイミングで、次のようなエラーが発生することがあります。

メッセージ 3960、レベル 16、状態 6、行 4

更新の競合により、スナップショット分離トランザクションが中断しました。スナップショット分離を使用してデータベース ‘TESTDB’ のテーブル ‘dbo.T1’ に直接または間接的にアクセスし、別のトランザクションによって変更または削除された行を更新、削除、または挿入することはできません。トランザクションを再試行するか、更新/削除ステートメントの分離レベルを変更してください。

スナップショット分離レベルを使用した場合、「トランザクションを開始したタイミングでコミットされていたデータ」を参照することになります。

複数のセッションでスナップショット分離レベルを使用して、同タイミングでトランザクションを開始し、同じレコードを更新した場合などに、上記のエラーが発生するケースがあります。

これについては、SQL Server でのスナップショット分離で解説が行われています。

スナップショット分離を使用した場合に、どのようなエラーが発生する可能性があるのかについては、次のようなクエリで確認することもできますので、発生する可能性のあるエラーについては、テキストを確認してみても良いかと思います。

SELECT * FROM sys.messages 
WHERE language_id = 1041 AND text　LIKE '%スナップショット分離%'

WITH(NOLOCK) との違い

検索時のロック競合を防ぐ方法としては「WITH(NOLOCK)」のロックヒントがあります。

行のバージョン管理を使用したデータアクセスと WITH(NOLOCK) のアクセスについては大きな違いがあります。

以前 SQL Server の「NOLOCK」ヒントは単純なロックを取得しないという動作ではありませんで記載しましたが、WITH(NOLOCK) は、更新中のデータ / ページに対してアクセスを行う、ダーティーリードによるアクセスが行われます。

そのため、未コミットのデータや、ページ分割が発生している場合のデータ重複の発生というように、実際のデータに即していないデータの取得が行われる可能性があります。

行のバージョン管理を使用したデータアクセスについては「トランザクションまたはステートメント実行時にコミットされているデータ」に対してアクセスが行われます。

WITH(NOLOCK) ダーティーページへのアクセスを許可するため、確定していない途中のデータにアクセスが行われますが、行のバージョン管理を使用したデータアクセスは「確定しているデータ」に対してのアクセス方法を同時実行性の低下を抑えて提供することができる方法です。

設定の有効化

スナップショット分離と RCSI の有効化ですが、データベース単位で設定を行います。

Azure SQL Database については、デフォルトで有効化されていますので、設定を行うことは不要です。

設定の有効化については次のようなクエリで実施できます。

-- スナップショット分離の有効化
ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

-- RCSI の有効化
ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT ON
GO

スナップショット分離と RCSI は、それぞれを個別に有効化することができますので、どちらか一方の機能だけを有効にすることも可能です。(もちろん、両方を有効にすることも可能です)

有効化を行う際ですが、該当のデータベースに対してアクティブなトランザクションが存在している場合は、「ENABLE_VERSIONING」という待機が発生します。

これは、ALTER DATABASE の SET オプション (Transact-SQL) に記載されている、次の動作のためです。

ALLOW_SNAPSHOT_ISOLATION を新しい状態に (ON から OFF へ、または OFF から ON へ) 設定した場合、ALTER DATABASE は、データベース内にあるすべての既存のトランザクションがコミットされるまで、呼び出し元に制御を返しません。

無効化する場合は、次のようなクエリで無効化できるのですが、無効化のタイミングでもトランザクションが存在していると「DISABLE_VERSIONING」とい待機が発生します。

-- スナップショット分離の無効化
ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

-- RCSI の無効化
ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT OFF
GO

アクティブなトランザクションが存在していると有効化 / 無効化に時間がかかりますので、設定変更のクエリの完了に時間がかかっている場合は、長時間実行されているトランザクションがいないかを確認してみると良いかと思います。

スナップショット分離 / RCSI のどちらかを有効にすると、テーブルのデータは自動的に行のバージョン管理を行う形式に変更が行われます。

行のバージョン管理の基本的な仕組み

それでは、SQL Server で MVCC を実現するために使用される行のバージョン管理の仕組みについて確認をしていきたいと思います。

行のバージョン管理のオーバーヘッド

Overhead of Row Versioning で解説が行われているのですが、行のバージョン管理が有効にされると、2 種類のオーバーヘッドが発生します。

レコードに 14 バイトの情報が追加
データの変更が行われると tempdb に変更前のデータが格納される
tempdb に格納されている変更前のデータがメモリ上にキャッシュされる

レコードに 14 バイトの情報が追加

行のバージョン管理を有効にすると「有効以降に変更されたデータ」に対しては、14 バイトの付加情報が追加されるようになります。

既存のデータに対しては変更は行われません。

有効以降に変更 (INSERT / UPDATE / DELETE) されたレコードには、次の情報が付与されるようになります。

トランザクションシーケンス番号 (XTS : Transaction Sequence Numbber) : 6 バイト
行識別子 (RID : Row Identifier) : 8 バイト

設定を有効化した後に、変更されていないデータの実際のバイナリデータが次の内容となります。

設定を有効化した後に、変更をしたデータのバイナリデータが次の内容となります。

実レコードは異なるのですが、データ構造は同じものを表示しています。

設定を有効化した後に変更されていないレコード (既存レコード) については、19 バイトのレコード構造となっています。

設定を有効化した後に変更したレコードについては、33 バイトのレコード構造となっており、14 バイトのサイズの増加が発生していることが確認できます。

「既存のレコード」の構造は変わりませんが、「変更されたレコード」については、行のバージョンを管理するために、レコードが変更されるごとに 14 バイトのバージョン情報が追加で行われるようになります。

既にデータが入っているデータベースで有効にした場合は、レコード単位に 14 バイトのオーバーヘッドが発生する可能性がありますので、設定を有効化した後のデータ変更によってページ分割の頻度が一時的に多くなるかもしれません。

データの変更が行われると tempdb に変更前のデータが格納される

次のようなデータがあるとします。

これで「C1 = 1」のレコードの「C2 = 8」に変更を行った際の動作を確認してみます。

現時点では、行のバージョン管理の情報は設定されておらず、C2 は 7 に設定されています。

それでは、C2 を 8 に変更した後のデータを確認してみます。

データが 7-> 8 に変更されていることが確認できていると同時に、「Version Information」に情報が登録されています。

「Transaction Timestamp」が XTS となり、トランザクションのタイムスタンプとなります。

このタイムスタンプにより、自トランザクションが実データを見ればよいのか、「Version Pointer」先に格納されているデータを見ればよいのかを判断することができます。

「Version Pointer」は「tempdb」に登録されている更新前のデータとなります。

Version Pointer は tempdb のどの情報を確認すればよいかを示したものとなり、上記の情報であれば、つぎのクエリでデータを確認することができます。

DBCC PAGE(N'tempdb', 3, 8, 1)

実際に、tempdb のデータを格納すると次のようになっています

更新前の C2=7 のレコードが格納されていることが確認できますね。(実際には更新部分だけでなく、レコード全体が tempdb に格納されます)

INSERT に関しては、新規レコードの生成のため tempdb への書き込みは行われませんが、UPDATE / DELETE については、更新前のデータが tempdb に書き込まれるようになりますので、tempdb の負荷が増加することは意識しておく必要があります。

tempdb に格納されている変更前のデータがメモリ上にキャッシュされる

先ほど、tempdb に格納されているデータを確認しましたが、「Record Type」は「INDEX_RECORD」となっていました。

tempdb に格納されている更新前のデータについては、インデックスの構造として格納が行われています。

SQL Server ではデータにアクセスされる際には、「ディスク-> メモリ -> 結果」というような流れとなり、基本的にはメモリにキャッシュされたデータに対してアクセスが行われます。

これは、tempdb に格納された行バージョンの情報も同様です。

tempdb に格納された行バージョンの情報にアクセスを行う際には、バッファプール上のインデックスページとしてキャッシュされるため、行バージョンの情報が頻繁に生成されている環境では、tempdb のメモリ使用率が上昇する可能性があります。

行のバージョン管理のモニタリング

行のバージョン管理ですが、DMV とパフォーマンスモニターの 2 種類の方法でモニタリングを行うことができます。

DMV

パフォーマンスモニター

SQL Server: Transactions オブジェクト

パフォーマンスモニターで取得できる情報は、行のバージョン管理のモニタリングを実施するために、重要な情報となります。

tempdb に格納された行のバージョン管理の情報 (変更前のデータ) ですが、定期的にクリーンアップが実行されます。

これについては、SQL Server データベースエンジンでの行のバージョン管理に基づく分離レベルで次のように記載されています。

これらの行のバージョンは、必要ではなくなった時点で解放されます。また、定期的に実行されるバックグラウンドのスレッドにより、古い行のバージョンが削除されます。

tempdb に格納された、行のバージョン管理の情報は定期的に実行されるバックグラウンドスレッドによって削除が行われます。

この動作により tempdb に不要な行のバージョン情報が格納されたままとなり、tempdb の肥大化が発生するのを防ぐことができます。

ただし、削除対象となるバージョン情報は最も古いコミット済みのトランザクションによって生成されたものとなり、長時間実行されているトランザクションが残っていると、それ以降に実行されたコミット済みトランザクションによって生成されたバージョン情報の削除は行われません。

これは、TempDB で使用される領域に記載されています。

アクティブなトランザクションで行バージョンにアクセスする必要がある限り、その行バージョンを格納しておく必要があります。 1 分ごとに、バックグラウンドのスレッドによって、不要になった行バージョンが削除され、TempDB 内のバージョン領域が解放されます。次の条件のいずれかに該当する場合、実行時間の長いトランザクションにより、バージョンストアの領域の解放が妨げられます。

実行時間の長いトランザクションが存在していると、それ以降に完了したトランザクションのバージョン情報が tempdb に残り続け、最も古いトランザクションの完了を待たないと、tempdb の領域が解放されないという状況が発生します。

SQL Server: Transactions オブジェクトのパフォーマンスカウンターにはアクティブになっているトランザクションの時間 (Longest Transaction Running Time) や、行のバージョンを格納する「バージョンストア」のサイズ (Version Store Size (KB)) を確認することができます。

行のバージョンが格納されたバージョンストアについては定期的に削除が行われ、サイズが解放されていないと「何らかの理由により残留しているトランザクションによって tempdb が肥大化している可能性」があります。

Version Store Size (KB) : バージョンストアのサイズ
Version Cleanup rate (KB/s) : バージョンストアの削除レート
Version Generation rate (KB/s) : バージョンストアの生成レート (行バージョンの作成レート)

このような項目を確認して、tempdb のバージョンストアの情報が定期的に削除され、一定サイズに抑えられていることを確認することが重要です。

最後に

行のバージョン管理は SQL Server 2005 で実装され、10 年以上が経過している、SQL Database では標準で有効化されており、ついこの間でた機能ではなく、10年以上経過している枯れてきた機能かと思います。

公式のドキュメントとして、トランザクションのロックおよび行のバージョン管理ガイド、公式ドキュメント以外にも様々な情報が公開されておりますので、動作を把握しようとした際に必要な情報も十分に公開されています。

既存システムへの適用はアクセスされるデータの変化が起きるため、有効化は悩ましいですが、スナップショット分離であれば、明示的にトランザクション分離レベルを設定しない限りは、設定前と同じデータアクセスの方法とすることができます。(行のバージョン管理が動作するため、オーバーヘッドは発生しますが)

設定の意味 / 動作を把握することで有効になっていない既存システムでも活用することができるかと思いますので、興味を持たれた方は、冒頭で紹介したドキュメントを確認してみてはいかがでしょうか。

SE の雑記