SQL Server 2005 Tips and Tips

Microsoft SQL Server 2005 には、「READ_COMMITTED_SNAPSHOT」と「スナップショット分離レベル」という、排他ロックにブロックされない読み取りを実現する機能が追加されています。この 2 つは、「読み取り一貫性」を実現する機能として、Oracle 経験者にとってはおなじみのものです。今までロック待ちに悩まされていた方には、たいへん役立つ機能ですので、ぜひご活用いただければと思います。

トピック

	「テーブル スキャンによるロック待ち」とは
	排他ロックにブロックされない読み取りの実現「READ_COMMITTED_SNAPSHOT」と「スナップショット分離レベル」
	読み取り一貫性のオーバーヘッド。tempdb の利用状況に注意

「テーブル スキャンによるロック待ち」とは

SQL Server を利用するにあたって、最も“はまりがち”な動作といわれているのが「テーブル スキャンによるロック待ち」です。SQL Server では、SQL ステートメント実行時の内部動作としてテーブル スキャン (全表走査) が行なわれると、ロック待ちが発生します。それは次のような状況です。

トランザクション X が「a=4」を排他ロックしているときに、トランザクション Y が実行されて「b='BBB'」のデータを参照した場合。b 列にインデックスが作成されていない場合には、内部的にテーブル スキャンが行われ、ロック待ちが発生してしまいます。
拡大図を見る

トランザクション X が排他ロックしている行 (a=4) の b 列のデータは「DDD」であり、Y の検索条件「b='BBB'」には該当しないにもかかわらずロック待ちが発生しています。a=4 の b 列のデータは排他ロックされているので、Y からは「DDD」を確認できないのです。これが「テーブル スキャンによるロック待ち」という現象です。

この現象は、たった 1 つのロック (行単位のロック) によって、テーブル全体がロックされているように見えるため、“ロック エスカレーション”と勘違いされる方が多いようです。しかし、これは、あくまでも単なるロック待ちであることに注意してください。このロック待ちを回避するには、主として次の 7 つの方法があります。

初め 6 つは、SQL Server 2000 と SQL Server 2005 共通の手法で、ロックをかける範囲を小さくしたり、ロックの保持期間を短くしたりするためのものです。 7 番目の手法が、SQL Server 2005 からの新機能です。

ページのトップへ

排他ロックにブロックされない読み取りの実現「READ_COMMITTED_SNAPSHOT」と「スナップショット分離レベル」

「READ COMMITTED SNAPSHOT」と「スナップショット分離レベル」は、排他ロックにブロックされない読み取りを可能とする SQL Server 2005 からの新機能です。これにより、テーブル スキャンによるロック待ちも回避できるようになります。具体的には、次のような動作が可能になります。

内部的にテーブル スキャンが発生しても、排他ロックにブロックされずに読み取りが可能になります。
拡大図を見る

READ_COMMITTED_SNAPSHOT スナップショット分離レベルは、正しくは「読み取り一貫性」を提供する機能です。排他ロックのかかっている更新中のデータは、まだ確定していない未コミットのデータであり、それを参照させないようにし、更新前のデータ (その時点での正しいデータ) を参照させることで一貫性を保ちます。

READ_COMMITTED_SNAPSHOT は、Oracle でのデフォルトの動作とほぼ同じであり、ステートメント発行時点での正しいデータを参照できることを保証します (ステートメント レベルの読み取り一貫性を実現)。

一方、スナップショット分離レベルは、Oracle の READ_ONLY トランザクションまたは Serializable トランザクションとほぼ同じ動作であり、トランザクション発行時点での正しいデータを参照できることを保証します (トランザクション レベルの読み取り一貫性を実現)。したがって、スナップショット分離レベルは、会計処理のように、トランザクションが長く、かつその間にほかのトランザクションからの影響を受けたくないような場合に役立ちます。

両者の違いは、次のようになります。

READ_COMMITTED_SNAPSHOT とスナップショット分離レベルの違い
拡大図を見る

[READ_COMMITTED_SNAPSHOT の利用方法]

READ_COMMITTED_SNAPSHOT は、デフォルトでは利用できません。利用するには、以下のようにデータベースに対して READ_COMMITTED_SNAPSHOT を ON に設定する必要があります。

ALTER DATABASE データベース名
SET READ_COMMITTED_SNAPSHOT ON

READ_COMMITTED_SNAPSHOT の利用例
拡大図を見る

[スナップショット分離レベルの利用方法]

スナップショット分離レベル (Snapshot Isolation Level) もデフォルトでは利用できません。利用するには、以下のようにデータベースに対して ALLOW_SNAPSHOT_ISOLATION を ON に設定する必要があります。

ALTER DATABASE データベース名
 SET ALLOW_SNAPSHOT_ISOLATION ON

また、SELECT ステートメントを実行する側で、以下のように分離レベル (Isolation Level) を SNAPSHOT へ変更する必要があります。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

ページのトップへ

読み取り一貫性のオーバーヘッド。tempdb の利用状況に注意

READ_COMMITTED_SNAPSHOT とスナップショット分離レベルでは、更新前のデータ (その時点のスナップショット データ) を格納するために、tempdb データベース内の「Version Store」の領域を利用します。また、更新前データは、列単位の更新であっても行単位で格納されます。したがって、行サイズが大きく、かつ更新の多いデータベースの場合には、tempdb の利用頻度や肥大化に注意する必要があります。なお、Oracle では、更新前データの格納先には、UNDO セグメント (Oracle 8i 以前はロールバック セグメント) が利用されます。

[動的管理ビューで tempdb の監視]

tempdb (Version Store) の監視には、動的管理ビュー (DMV： Dynamic Management View) を利用すると便利です。たとえば、データベースごとの Store サイズの合計を表示するには、次のように dm_tran_top_version_generators という動的管理ビューを利用します。

データベースごとの Store サイズの表示
拡大図を見る

また、dm_tran_version_store という動的管理ビューを利用すれば、Version Store 内の各 Store を 1 件ずつ確認することもできます。

[スナップショットデータの保持期間]

スナップショット データの保持期間は、READ_COMMITTED_SNAPSHOT とスナップショット分離レベルでは異なります。READ_COMMITTED_SNAPSHOT では、更新側のトランザクションが完了した時点で、更新前データは不要になるので破棄されます (正確には、すぐには破棄されず、1 分ごとに動作しているガベージ コレクションによってまとめて破棄されます)。

これに対し、スナップショット分離レベルの場合には、“読み取り側”のトランザクションが完了するまで、更新前データは破棄されません。もし破棄されてしまうと、トランザクション開始時点でのデータが削除されることになり、トランザクション レベルの読み取り一貫性が実現できなくなるからです。したがって、READ_COMMITTED_SNAPSHOT よりも、スナップショット分離レベルの方がスナップショット データの保持期間が長くなっています。その分、tempdb へのオーバーヘッドも大きくなります。

このように、読み取り一貫性にはオーバーヘッドがありますので、現状のシステムに問題がない場合には利用しない方が良いです。また、ロック待ちを回避したい場合には、適切なインデックスを作成したり、適切な分離レベルを利用するなどの方法もあります。どんな状況ででも、読み取り一貫性を利用すべきというわけではありませんので、ご注意ください。

私自身は、READ_COMMITTED_SNAPSHOT とスナップショット分離レベルの最大のメリットは、Oracle と同じような動作が可能になる点だと思っています。しかし、Oracle の経験者が SQL Server を利用するときに必ずと言っていいほど悩まされる、ロックの問題 (テーブル スキャンによるロック待ち) を回避できるようになったのは、本当に画期的なことです。READ_COMMITTED_SNAPSHOT とスナップショット分離レベルを利用する場合には、tempdb の利用状況に注意しながら、正しく活用していただければ良いと思います。

ページのトップへ

3 of 10