執筆者 : Kimberly L. Tripp、SQLskills.com

概要 : 現在、多くのシステムでは、大量の読み取り操作が、データ ウェアハウスまたは独立したシステムの形式で、書き込み操作から分離されています。この方法には、多くのメリットがあります。読み取りを集中的に実行するアプリケーションでは、より多くのインデックス構造、データの冗長性、さらにはデータの別のビューが必要になる傾向があります。トランザクション処理システムでは、スループットが要求されます。オーバーヘッドを最小限にした場合に限り、最善の書き込みスループットが可能になります。通常、読み取りを行うユーザーと書き込みを行うユーザーのアクセス パターンは異なります。読み取りを行うユーザーはより大きな分析型のクエリを実行し、書き込みを行うユーザーは単一の挿入、更新、削除を実行する傾向があります。これらの操作が分離されていると、管理者はより小規模で管理しやすいトランザクション処理システムの復旧方法に重点を置くことができます。OLTP データベースは、意思決定支援や分析データベースなどのデータの冗長性のほんの一部になる傾向があります。とはいえ、常にこの区別を明確にできるとは限りません。データが分析指向のデータベースにコピー、変換、アーカイブされた後は、定期的に管理および再構築される必要があります。ユーザーは、トランザクション処理として一貫性があるバージョンのデータベースを参照することで、確実にメリットが得られます。ただし、そのバージョンが最新のデータではなくなっている場合は、構築とインデックス設定に時間がかかる場合があるので、ユーザーのニーズに合わないことがあります。そこで、スナップショット分離を導入することになります。

この資料では、この分離レベルが適切である状況、考えられる比較検討条件、およびベスト プラクティスに主に重点を置いて説明します。この資料を読む前に、SQL Server Books Online の「同時実行の問題」を一読することをお勧めします。

トピック

データ アクセス パターンと使用法
定義、用語、および構文
開発のベスト プラクティス
管理のベスト プラクティス
関連情報

データ アクセス パターンと使用法

ビジネス要件や規制の変化と共に、運用データベースのサイズが急速に増加し、データの保有期間も長期化しています。さらに、ドライブの容量が 12 ～ 18 か月ごとに倍増し、ストレージ コストが減少しているので、"オンライン" に保つことが望まれるデータの量が増えています。1 つの解決策はトランザクション処理から分析を分離することです。この解決策には、複雑で詳細な分析とビジネス インテリジェンスの調査に関しては多数のメリットがありますが、ディスク領域と管理の容易性の面では優れているとは限りません。クエリの実行が活発になることによって、より多くのデータをオンラインにするニーズが増し、データの競合をより迅速、かつ、リアルタイムに分析するニーズが生じます。

SQL Server 2000 では、Read Committed トランザクション分離で競合を最小限に抑えることができました。この機能では、Select ステートメント処理によってリソースが読み取られた後で、読み取りロックが解放されます。コミットされたデータのみが読み取られ、コミットされていない変更は可視にならないという点で、既定の環境は標準 SQL-92 定義に準拠します。ただし、コミットされたデータのみを読み取れますが、標準ではトランザクション内でさえ、読み取りの一貫性が保証されていません。行の処理の直後にリソースのロック (共有ロック) が解放されるので、(他の行の読み取り処理が行われている途中でも) すぐにその行を変更できます。

[注意 : データの移動が発生し得ない (つまり、総合的かつ適切なインデックスの作成と管理によって分割が削減される) 場合は、1 つのステートメント内で行を再読み取りする可能性が非常に低くなるので、この例外を生み出すことは困難になります。]

この選択は多くの状況において適切で、パフォーマンスが向上します。コミットされた変更のみが、最小限のリソースのロックで迅速に可視になります。たとえば、現在処理中のシステムから予測として現在の売上の合計を検索する場合があります。(トランザクションの処理が継続しているため) 値はアクセスした直後にすでに "古く" なるので、予測だけを希望することができます。実際に多くの環境で、より制限が少ない READUNCOMMITTED (同義語である WITH (NOLOCK) ヒントと呼ばれることも多い) というロック モードが使用されています。この環境では、コミットされていないデータの読み取りが許可されています。ただし、売上数および総売上が予測である場合、"進行中" のデータを参照することが許可されることがあります。これが許可されない場合は、データの読み取りの繰り返し中の一貫性を保証するために、プログラマが行った分離レベルの変更を使用する必要があります。

では、どこに一線を引きますか? システムがアクティブに処理されているときに、ステートメント レベルまたはトランザクション レベルの読み取りで一貫したデータを返すことは可能ですか? 運用環境で実行時間の長いクエリを記述し、一貫性を求め、書き込みを行うユーザーをブロックしないことは可能ですか? SQL Server 2005 では、省略可能なデータベース レベルの設定により、この機能をユーザーに提供できます。この設定により、READ COMMMITTED の動作が自動的に変更され、ブロックおよびロックが発生しない、ステートメント レベルの読み取りの一貫性が提供されます。

トランザクション レベルの一貫性に対しては、新しい分離レベルであるスナップショット (SNAPSHOT) が追加されました。この分離レベルに変更すると、トランザクション レベルの一貫性が制御可能な設定になります。オプションを設定しないと、SQL Server 2005 データベースの既定の動作は、以前のリリースと変わりません。また、トランザクション処理のスループットとパフォーマンスが最優先の目的である多くのシステムでは、この既定が引き続き望まれています。(ステートメント レベルまたはトランザクション レベルのいずれかで) ロックなしのスナップショットの形式が望まれる場合は、行のバージョン管理を使用して行の変更を追跡します。この機能を使用するには、その時点で読み取りを行うユーザーが存在しない場合でも、更新時にデータの書き込みを行うユーザーに負担がかかります。行バージョンは、変更の前に開始されたすべてのトランザクションが完了するまで保持する必要があり、完了後はバージョンが破棄されます。ただし、バージョンを作成する必要があります。このバージョンを操作する負担はわずかですが、バージョンの実装を選択するには、慎重に考慮する必要があり、多数のベスト プラクティスが存在する場合に限ります。

使用シナリオ

ここでは、SQL Server 2005 スナップショット分離レベルと新しい形式の Read Committed 分離レベルを使用して、パフォーマンスの向上、遅延の削減、および組織の開発者とデータベース管理者の生産性の向上を実現する方法について説明します。

以下の一般的なビジネスのシナリオについて説明します。

• オンライン トランザクション処理のアプリケーション

• 実行中のデータに対するアドホック レポート

• コピーが管理されるデータベースに対するアドホック レポート

• 運用中のデータに対する夜間のレポート

• 一般的なデータベース テクノロジへの移行

オンライン トランザクション処理のアプリケーション

一見すると、スナップショット分離テクノロジの主な使用法は、データ ウェアハウジングや実務レポート配信システムなど、読み取りを集中的に実行するワークロードであるように見えます。これらのワークロードでは、トランザクションとして一貫性があるデータベースのビューを必要とする、大きなテーブルに対する複雑で実行時間の長いクエリ (特に集計) のテーブル レベルの読み取りロックにより、データの更新が必要なトランザクションを効果的にロック アウトできる必要があります。このテクノロジのアプリケーションは、これだけではありません。Read Committed 分離レベルの新しい既定の動作は、ステートメント レベルでスナップショットと連動し、混合ワークロード システムのスループットを大幅に向上し、大規模な結合と集計にトランザクションとして一貫性があるデータを提供できます。スナップショットでは、ステートメントのみに対して読み取りの一貫性が保証されるので、長期間にわたる競合が発生することはありません。さらに、この環境では、アプリケーションの変更は必要ありません。

ペシミスティック ロック (トランザクション分離の ANSI 標準) が使用されると、アプリケーションでは通常、ブロックが発生します。トランザクション内の読み取り処理と書き込み処理が同時にデータへのアクセスを要求すると、競合するロックが要求されます。これは完全に正常な動作で、ブロックの期間が短い場合は、重大なパフォーマンス ボトルネックにはなりません。これは、負荷の高いシステムでは変化する場合があります。トランザクションの処理にかかる時間の増加 (ユーザー入力を含むトランザクションなど、記述に問題があるトランザクションによる遅延や、ディスクの入出力、RAM または CPU などのシステム リソースの使いすぎで発生する遅延など) により、ブロックに不均衡な影響が与えられる場合があるためです。トランザクションの実行にかかる時間が長くなるほど、ロックが保持される時間が長くなり、ブロックが発生する可能性が高くなります。

この現象の例として、内部および web ベースの予約アプリケーションを使用して、顧客のために車を予約する、レンタカー会社について考えます。これらのシステムでは、同じデータ (ここでは車) に対して競合する複数のトランザクションがあります。顧客サービスの担当者は、顧客のために車を予約する前に、システムで提供される実行時間の短いクエリを使用して、特定の場所の車を利用できるかどうかを確認できます。このような場合、非接続のデータセットなどのプログラミング技術により、オプティミスティック同時制御が提供されます。具体的には、以下のように使用します。

1. レンタル場所で、特定の日付の範囲内で、特定のクラスのすべての予約可能な車を検索するクエリ。このクエリは、Car、Class、Reservation および Location など、少なくとも数個のテーブルの結合になる可能性が高くなります。さらに、このクエリは Read Committed 分離レベルで実行され、コミットされたデータのみがユーザーに返されることが保証されます。

2. データが呼び出し側のアプリケーションに表示されている間は、データで保持されているすべてのロックが解除されるように、クエリで取得されるレコードセットはデータベースから "切断" されます。データベースの同時実行制御のオプティミスティックな形式をエミュレートするので、この操作は通常 "バッチ オプティミスティック" と呼ばれます。データがアクティブであるにもかかわらず、競合が発生する可能性が低いという点で、この操作はオプティミスティック (楽観的) です。行レベルのタイムスタンプにより、プログラマがデータの変更を識別し、ユーザー インターフェイスに適切なメッセージを表示して、競合を管理できるようになります。

3. 呼び出し側で特定の車を選択します。データセットが編集され、予約が反映されます。

4. 次に、アプリケーションが再接続され、行レベルの SQL Server タイムスタンプ列を使用して、変更がデータベースに同期されます。データが切断されている間にその他の呼び出し側によってデータが変更されていないことが確認されます。

5. アプリケーションにより、呼び出し側にレポートが返されます。予約が確定した場合は、成功がレポートされます。車が別の呼び出し側によって予約された場合は、競合が示され、別の車を予約する機会が与えられます。

上の技法は、本当に楽観的であるわけではないことに注意します。このデザイン パターンでは、手順 1. のクエリを実行して候補の車を検索する際に、大量の競合が発生する場合があります。クエリの実行中に、SQL Server 2005 Read Committed スナップショット分離 (RCSI) により、ロックおよびブロックが発生しない、トランザクションとして一貫性があるバージョンのデータがこれらの要求に提供されます。この分離により、サーバーの負荷が軽減され、他のユーザーが車を予約する際にデータがブロックされなくなります。結果として、車を予約するトランザクションのパフォーマンスが向上しますが、実行時間の長いクエリで参照される車が利用可能になる機会が増えるとは限りません。それでも、この交換条件は許容範囲です。予約がより迅速に行われ、同時に行われた車のレンタルの要求によってブロックされなくなります。したがって、特に休日の予約や出張旅行のピークの時期などによってワークロードがピークになると、トランザクションのスループットが増加します。

データベース管理者によってデータベース レベルで RCSI が有効にされると、上の手順 1 ～ 5 で使用されたプログラミング ロジックでは、変更なしで、この新機能を利用できます。実際に、データベースの設定が完了すると、この形式のステートメント レベルの読み取りの一貫性がすべてのクエリに既定で適用されます。

実行中のデータに対するアドホック レポート

すべての企業は、情報システムの機能を拡張しながら、継続的にコストの削減に努めています。SQL Server 2005 の指針の対象の 1 つは、データベース内でデータがキャプチャされてから、組織で使用できるようになるまでの待機時間を短縮することです。この待機時間が短縮されることで、開発者は、従来のバッチ レポート スケジュールの範囲外でデータを提供するシステムを構築できるようになります。

食品小売業者のシナリオを考えます。この食品小売業者では、各店舗のサンドウィッチ、牛乳、およびその他の生鮮食品などの日用消費財の在庫を最小限に抑えるというニーズと、顧客が購入する商品がスーパーマーケットの棚に揃っているというニーズのバランスを取ろうとしています。これらの商品は気候によって非常に影響を受けます。たとえば、バーベキュー用品やアイスクリームは、晴れた日により多く売れます。手軽なパック食品は、雨の日に売れます。

新しい分離レベルを導入する前は、スーパーマーケットのアプリケーションの開発者は Read Uncommitted 分離レベルを使用して、実行中のデータでの長時間のブロックを回避しているはずです。複数のテーブルで結合を行う場合は、Read Uncommitted 分離レベルは使いにくい場合があります。Read Uncommitted 分離レベルでは、ステートメント レベルの、トランザクションとして一貫性がないデータベースのビューに、ブロックなしでアクセスできますが、このビューでは、データベースに提供されているのは、ビジネス トランザクションに関連するデータの一部分のみである可能性があります。

さらに、日用消費財と共に売れている商品の組み合わせを確認するために売上を分析する (バスケット分析とも呼ばれます) 際は、非常に集中的にデータが使用され、実行時間が長くなるので、データが継続してデータベースに提供されると、一貫性がなくなる可能性がさらに増加します。

トランザクションとして一貫性があるデータのビューが必要な状況では、通常、システムの設計者はこれらの種類のレポートで時間を使い果たし、実行中のシステムの同時実行に影響することを避けるように設計します (上の図 1 に示されているように、使用のピーク時に起動された、実行時間の長い、読み取り専用のレポートによって、書き込みを行うユーザーによるシステムの更新がすべてブロックされることになります)。

あらかじめ計画された夜間のレポートのみを提供する IT インフラストラクチャでは、スーパーマーケットのマネージャが予期しない要求に対応し、どの商品が売り切れそうになっているかを確認することが妨げられる場合があるので、倉庫から補充するために 2 回目の配送を要請して要求に応える機会を逃す可能性があります。これは、収益の損失、さらには顧客の損失にもつながります。

新しい分離レベルにより、アプリケーションでは、トランザクションに対するロックなしのアクセス、またはステートメント レベルのトランザクションとして一貫性があるデータベース全体のビューが可能になるので、レポート作成者の作業が容易になり、はるかに有益になります。SQL Server 2005 では、データベース エンジンの能力が Analysis Services の高度なレポート機能にさらに緊密に統合されました。これにより、統一データ モデルが導入され、データを抽出してスター スキーマに変換しなくても、完全な分析レポートを作成できるようになりました。スナップショット分離テクノロジは、この種類のアプリケーションにおいてデータのアクセスを向上するために主要な役割を果たし、抱合せ販売レポートの能力を実行中のデータに組み合わせることができます。スナップショット分離テクノロジにより、これらのビジネス プロセスの処理が変化する可能性があります。

• 再調整されたオプティミスティック Read Committed スナップショット分離は、既存のレポート システム (またはサード パーティから購入した、分離レベルを変更できないシステム) に最も適しています。ロックなしの読み取りを利用するためにアプリケーションを変更する必要がなく、特にこれらのアプリケーションのほとんどでは、1 つのクエリの結果でレポートが作成されるためです。

• 新しいスナップショット分離レベルは、より複雑な要件に適しています。たとえば、同じトランザクション内で実行できる一連のレポートを実行し、すべてのレポートで同じトランザクションとして一貫性があるデータのビューが参照されるようにする必要がある場合などです。このような要件は、複雑な財務レポート システムで多く発生します。これらのシステムでは、レポート間の合計やチェックサムに不整合が生じる原因になりやすいため、一連のレポートの実行中にデータの変更を取得することは望ましくありません。

SQL Server 2005 では、データベースでこれらの新しい分離レベルを有効にする操作が簡単になります。新しい形式の Read Committed スナップショット分離が構成されると、SQL Server で自動的に使用されます。アプリケーションまたはトランザクション コードの変更は必要ありません。トランザクション レベルのスナップショット分離を利用するには、スナップショット トランザクションを開始する前に、接続の分離レベルを構成する必要があります。

これらの機能を有効にした後は、店舗のレジから提供されたデータをブロックせずに、店舗で予期しない要求が発生した場合に使用できる一連のパラメータ化されたレポートを、スーパーマーケットのマネージャに安全に提供できます。マネージャは、店舗の顧客のニーズが予測され、満たされていることを確認できます。これは、高い顧客満足につながります。しかし、スーパーマーケットに最も適しているのはどの機能でしょうか? スナップショット分離は、複数のクエリで構成される複雑なレポートの需要に適しています。この分離レベルを使用することで、トランザクションの実行中はデータが一貫してバージョン管理されるので、レポート内のすべての要素の一貫性が保証されます。レポートが常に個別のクエリに基づいている場合、行バージョンを管理する必要があるのは個別の select ステートメントの存続期間だけなので、Read Committed の新機能 (行バージョン管理を使用します) を選択することをお勧めします。

スナップショット分離を有効にすると、データベース サーバーに対する追加の要求が発生します。上で説明したシナリオでは、実行中のデータに対してアドホック レポートを実行する臨時の要件をサポートするために、スーパーマーケットのレジからのデータの収集に使用されるバック オフィス サーバーに十分な予備の容量があることが想定されています。スナップショット分離の使用により、データの書き込み処理とデータの読み取り処理の両方に対する更新トランザクションを実行中のサーバーに、追加の負荷が発生します。データの書き込み処理による変更は、バージョン管理される必要があります。データの読み取り処理による読み取りでは、バージョン チェーンをスキャンして、トランザクションの開始時に適したバージョンを取得する必要があります。

追加の負荷は、特に TempDB に適用されるので (SQL Server によって、変化するデータのトランザクションとして一貫性があるビューを提供するために使用されるバージョン ストアが TempDB に格納されるため)、データベース管理者は、運用前のシステムで負荷をシミュレーションしてこの新しいテクノロジをテストしてから、運用環境に展開することをお勧めします。TempDB の入出力の帯域幅の拡大などの単純な方策が、スナップショット分離を有効にするよりも効果的である場合があることに注意します。ただし、システムにおいて更新と読み取りのワークロードの組み合わせの負荷が大きい場合は、(この資料の後半の) 他のシナリオで説明する構成がより適切である場合があります。これは、ステートメント レベルの Read Committed スナップショット分離よりも、トランザクション全体のスナップショット分離が必要な場合に、特に当てはまります。

コピーが管理されるデータベースに対するアドホック レポート

変更されるデータの割合が高いシステムでは、新しいスナップショット分離レベルの使用を有効にすると、行の以前のバージョンの作成と管理のオーバーヘッドにより、トランザクションの速度が遅くなることがあるので、全体的なパフォーマンスが低下する場合があります。これは特に TempDB またはディスク サブシステムがすでにシステムのボトルネックに近くなっている場合に当てはまります。この状況では、新しいインフラストラクチャを有効にするパフォーマンス コストをかけて、リアルタイム データに対するレポートを実行する価値がない可能性があります。特に、すでに混み合っているシステムに、レポートによってさらに負荷が追加されることが多いためです。

このシナリオは、予約システム (航空会社やホテルの予約システムなど) と共に、web ショッピング サイトなどのオンライン システムを含む受注エントリ システムにおいて一般的です。負荷のピーク時の更新のパフォーマンスは重要です。更新が遅いと、顧客が注文を諦めて、別のサイトに移動してしまうことがあります。逆に、顧客サービス部門と需要予測スタッフは、実行中のデータを含むレポートにアクセスして、顧客との対話に利用したり、計画を実行したりできます。

コピーが管理されるデータベースを作成することで、これらの競合する要件に最も適切に対応できます。コピーが管理されるデータベースは、データのほぼリアルタイムのレプリカで、実行中のシステムの背後で遅れて動作しますが、レポートを実行するには十分に新しい状態が保たれます。このレプリカの目的は、レポートするユーザーを、別のサーバー (または複数のサーバー) に負荷分散して、実行中のシステムのワークロードを増やさないようにすることです。

SQL Server 2005 では、レプリカ データベースの管理を自動化するための 2 つのオプションを提供しています。その両方がトランザクション ログ メカニズムの内部で動作するので、コミットされたデータが使用されます。

1. データベース ミラーリング (同時ログ配布) – このテクノロジは、本来は実行中のシステムのスタンバイを提供するためにデザインされました。各トランザクションのコミット処理で、データがレプリカに送信されます。実行中のデータベースとレプリカ データベースの両方のログにデータが配置されるまで、コミットは完了しません。実行中のシステムのパフォーマンスは、スタンバイ システムのコミットの能力に影響されます。そのため、データ ミラーリングは、レポート ワークロードを軽減するには適していません。レポート ワークロードの急増は、実行中のシステムのパフォーマンスに直接影響する可能性があるためです。データベース ミラーリングは、補助的なレポート データベースではなく、可用性の機能であると考える必要があります。

   データ ミラーリングには、以下の主な利点があります。まず、セットアップと管理が非常に簡単です。データベース管理者が特定のテーブルを選択しなくても、すべてのデータが転送されます。実際に、実行中のシステムに変更が加えられると、レプリカにも自動的に反映されます。また、レプリカ サーバーでデータベース スナップショットを使用して、その時点のレポートを提供できます。ただし、データベース スナップショットは手動で作成する必要があります。各レポートの観点を管理すること (最新のデータへのアクセスが必要であることが想定されます) はできないことがあります。

   その他の欠点は、レプリカのみに対する変更はできないことです。たとえば、データのサブセットのフィルタ処理、読み取り専用の特権を持つレポート限定のユーザーの追加、レポートのパフォーマンスを支援するためにデザインされたテーブル インデックスとインデックス付きビューの追加などはできません。これらの変更は、実行中のシステムに対してのみ実行でき、結果として、更新のパフォーマンスが低下することがあります。

2. レプリケーション (特にトランザクション レプリケーション) – このテクノロジにより、実行中のシステムでの軽微なオーバーヘッドのみが実行されます。このオーバーヘッドは、データベース ログ ファイルの入出力の帯域幅を拡大することで軽減できます。コミットされたトランザクションは、データベース トランザクション ログ ファイルから非同期に読み取られます。ディストリビューション データベースに移動されたデータは、そこから複数のサブスクライバに展開できます。このテクノロジは、管理が困難である場合がありますが、長期にわたって SQL Server の主要なコンポーネントだったので、データベース管理者には良く知られています。実行中のシステムのサブセット (テーブル単位、または行単位と列単位の両方) をデータとしてレプリケートできます。また、さまざまなユーザー、インデックス、およびビューをサブスクライバ (レポート) データベースに表示できる利点があります。

   欠点はほとんどありません。データの追加のコピーは、すべてのサブスクライバに配布されるまで、ディストリビューション データベースで管理されます。

   レプリケートされたオブジェクトでの SQL Server 2005 スキーマの変更では、以前のバージョンでは必要だった、再実行の並べ替え (およびレプリケーション固有のストアド プロシージャを使用して列を追加および削除すること) が必要になることはほとんどありません。以前は、リリース間でスキーマを変更するレプリケーション コマンドが使用されていないサード パーティのアプリケーションでは、これらのコマンドによってレプリケーションの使用が制限されていました。図 2 は、2 台のサーバー間での一般的なトランザクション レプリケーションのセットアップを示します。

   

以前のトランザクション レプリケーションの使用に関する主な問題は、配布とサブスクリプションのデータベース リンクで、実行中のシステムと同様の問題が発生していたことです。サブスクライバ データベースで実行時間の長いレポートが実行されている場合、これらのレポートにより、サブスクライバ システムに到着するレプリケートされたデータがブロックされます。次に、このブロックが原因で、レプリカを同期ができないことが多くなり、実行中のシステムから遅れるようになります。さらに、最近の購入や予約の情報を顧客に提供しようとしているコール センターのスタッフが頓挫させられます。この問題には、新しいスナップショット分離および再調整された Read Committed スナップショット分離によって完全に対応できます。

サブスクライバ データベースでは、スナップショット分離を使用するように設定できます。また、データに依存するレポート (読み取り専用アプリケーション) では、個別のレポートに対して、スナップショット分離 (一連のレポートとダイアログ間の一貫したビューのため) または Read Committed スナップショット分離のいずれかを使用できます。これらのアプリケーションのいずれも共有ロックを必要としないので、読み取り操作が大部分であるデータベースが、実行中のシステムから大幅に遅れることを回避できます。着信するデータが、実行時間の長い読み取りトランザクションの背後でロックされることがなく、トランザクションとして一貫性があるデータベースのビューに対して、クエリが実行されます。さらに、レプリケーションでは、システム内でデータを移動する際に、より優れたトランザクションの忠実性を保持できるようになります。

これも、非常に拡張性のあるソリューションです。レポート ワークロードの増加に合わせて、新しいサーバーに 2 番目 (またはそれ以上) のサブスクライバ データベースを追加することで、実行中のシステムにこれ以上影響を与えずに、追加の負荷に対応できます。顧客サービスの担当者は、レプリカにアクセスしているときに (実行中のシステムで) 顧客が実行したトランザクションを確認して、必要に応じて顧客を支援できるようになります。

運用中のデータに対する夜間のレポート

このシナリオでは、"昼間のオンライン" と "夜間のバッチ" の従来のデータ処理モデルについて考えます。昼間のオンラインでは、データがシステムに入力される、通常の "営業時間" の明確なセットを、短いトランザクション専用に構成されるワークロードに一致させます。夜間のバッチでは、実行時間の長いレポートを実行して、昼間に到着したデータに対してレポートします。このシナリオは、メインフレームのアプリケーションでは非常に一般的で、昼間に TP モニタが実行され、夜間にバッチ作業が実行されます。

企業のますますのグローバル化 (オンラインになるオフィスと、オフラインになるオフィスが存在する) と同様に、顧客が使用する、インターネット対応のアプリケーションが増加していることは、このモデルが現代的なデータセンターに対応できなくなっていることを意味しています。ただし、この古いテクノロジを見直すことで学べることも、まだいくつか存在します。

1. ユーザー中心のワークロードには、山と谷ができる傾向があります。

2. その後の一貫性を保持するため、レポートは特定の時点で実行される傾向があります。

3. ほとんどのデータベースのワークロードには更新のピーク (データの読み込みなど) と読み取りのピーク (レポートなど) があります。

"Gadget.com" という、パーソナル オーディオ テクノロジのサポートおよび販売を行う、インターネット対応の企業について考えます。ニューヨークに、グローバル ビジネスに対応するデータセンターがあり、小規模なオフィスが 7 か国に存在します。ほとんどの企業と同様に、そのオンライン システムには明確な使用のパターンがあります。ここでは、米国での負荷のピークは、スタッフの出社、および主要な顧客の web サイトへのアクセスと同時に発生することがパターン化しています。

トランザクション処理システム ワークロード

時刻 (東部標準時)	ビジネスのイベント	データセンターのイベント
午前 08:00	米国のオフィスがオンラインになり始める	残りのレポートが停止される
午前 12:00	すべての米国オフィスがオンラインで、ヨーロッパのオフィスが終業する	オフィスの負荷のピーク
午後 06:00	米国のオフィスが終業し始め、アジアとオーストラリアの小規模なオフィスがオンラインになり始める	オンラインの負荷のピーク
午後 10:00		オフィスとオンラインの負荷が最少 スナップショット分離が有効になり、データの抽出が開始され、主要な一連の実務レポート配信が起動する
午前 02:00	ヨーロッパのオフィスがオンラインになり始める	スナップショット分離が無効になり、一部の米国限定の実務レポートが継続される

上のシナリオでは、データベースがオンラインのときに、データセンターでスナップショット分離の状態を管理できます。データベースを再起動して、異なる設定を取得する必要はありません。短い期間にスナップショット分離をアクティブにするだけで、Gadget.com では、継続してオンラインにでき、世界のオフィスのユーザーにサービスを提供できます。さらに、実行時間の長いレポートによってこれらのユーザーがブロックされないことを保証できます。また、Gadget.com では、使用のピーク時にスナップショット分離を無効にすることにより、主要なユーザーと顧客が最大のスループットを使用できることを保証しました。

Gadget.com では、複雑なデータ ウェアハウスも実行しています。このデータ ウェアハウスは、顧客と株価の動向に関する情報の提供、および長期にわたってデータ内のその他のパターンを検索する、より大きなレポートの実行に使用されます。このシステムは主に読み取り専用です。ただし、一部のユーザーは、データベースに対して更新アクセスを実行し、会計形式のジャーナルの調整、および会計監査に続く株調整を実行する必要があります。Gadget.com では、このシステムのニーズに合わせたスナップショット分離の使用方法を採用しているので、問題が発生することはありません。

データ ウェアハウジング システム ワークロード

Gadget.com では、データ ウェアハウス システムを毎日、24 時間運用しています。スナップショット分離を使用して、レポートの ユーザーが、トランザクションとして一貫性があるデータに、高いパフォーマンスでアクセスできるようにしています。

時刻 (東部標準時)	ビジネスのイベント	データセンターのイベント
午前 08:00	米国のオフィスがオンラインになり始める	オンライン レポートの負荷のピーク
午前 12:00	すべての米国オフィスがオンラインで、ヨーロッパのオフィスが終業する	調整の負荷のピーク (ただし、軽微)
午後 06:00	米国のオフィスが終業し始め、アジアとオーストラリアの小規模なオフィスがオンラインになり始める	レポートはまだオンライン
午後 10:00		レポートの負荷が最少 データベースが単純復旧モデルに配置されます。レポート アプリケーションにより、着信するレポートがクエリされ、データが読み込まれ、データの変換が開始されます。
午前 02:00	ヨーロッパのオフィスがオンラインになり始める	システムが完全復旧モデルに配置されます。データベースの完全バックアップが開始され (速度が保証された適切なハードウェアで)、実行時間の長い、キュー レポートが開始され、最後に、アドホック ワークロードが開始されます。

Gadget.com では、毎日システムに取り込む必要があるデータの量に着目し、完全復旧モデルと単純復旧モデルを組み合わせて使用することで、読み込みのパフォーマンスを最大化することを決定しました。完全復旧モデルは、ウェアハウスの管理者によってデータに対して行われたアドホック調整の保護に使用されます。単純復旧モデルは、データの読み込み時に必要なログの管理 (小規模ですが) およびログの削減に使用されます。データの読み込みのケースでは、行の挿入でバージョン チェーンのエントリが生成されることはないので、スナップショット分離の設定を変更する必要はありません。毎日 24 時間のスナップショット分離と共に実行することで、データの読み込みプロセスをブロックする可能性がある、実行時間の長いレポートの影響を受けずに、高速なデータの読み込みおよびアドホック データの調整を続行できるようになります。運用上の調整は、ログの削減およびデータの読み込みの向上のために行われた、復旧モデルの調整のみです。

• 以前にバックアップしたバックアップ、関連するログ、および着信するデータ抽出ファイルを使用した、データ読み込み中の単純復旧モデル (システムを完全に復旧できるため)。読み込みの後に、復旧モデルを "完全" に変更し、データベースの完全バックアップを実行します。

• 残りの時間は完全復旧モデルを実行し、ログのバックアップを実行して、ハードウェアの障害またはメディアの破損によって、アカウントの調整が失われないようにします。

上のシナリオでは、オンライン トランザクション処理およびデータ ウェアハウジングの両方を含む、さまざまなトランザクション ワークロードを備えたシステムでスナップショット分離を展開する方法について説明しています。システムの主要な作業に重大な影響を与えずに利用できるように、スナップショット分離をアクティブな状態に保持できることと同様に、このテクノロジのユーティリティに重点を置いて説明しています。

一般的なデータベース テクノロジへの移行

SQL Server 2005 以前は、商用リレーショナル データベース 管理システムの分野には 2 つの方式がありました。1 つは、SQL-92 標準 (ANSI X3.135-1992, American National Standard for Information Systems — Database Language — SQL, November, 1992) で定義された 4 つの ANSI 標準の分離レベルのサポートを可能にするロック機能に基づいた、ペシミスティック同時実行を実装したシステムです。このようなシステムには、Microsoft SQL Server、IBM DB2 (すべてのコードベース/プラットフォーム) および Sybase Adaptive Server があります。もう 1 つは、トランザクションの開始時点のデータのビューの保持に基づくオプティミスティック同時実行を備えた、標準以外のトランザクション分離モデルを実装したシステムです。この方式のシステムは Oracle のみでした。

この区分により、3 種類のソフトウェア開発が存在することになりました。

1. Oracle で開発し、Microsoft SQL Server に移植する

2. Microsoft SQL Server で開発し、Oracle に移植する

3. 両方の方式で開発と最適化を行う

通常、"種類 3" を採用できるのは、SAP、Siebel および Peoplesoft などの大規模なソフトウェア会社だけでした。ほとんどの開発者は、種類 1 または種類 2 のいずれかを選択する必要がありました。どちらを選択するかは、通常、Unix 市場が売上に関係する程度によって予測されていました。

SQL Server 2005 とスナップショット分離によるオプティミスティック同時実行制御の導入により、種類 1 のアプリケーション ベンダが SQL Server への直接移植を行い、Oracle/Unix というプラットフォームの境界を越えて市場を拡大することが、非常に容易になりました。IT 部門では、複数のデータベース プラットフォームをサポートすることに関連する複雑性を低減し、以下のようなコストを回避することを希望しています。

• 複数のデータベース チーム

• トレーニングのコストの増加

• ソフトウェアのボリューム ライセンス コストの減少

• 複数のベンダとやりとりする管理時間

• 異なるサプライヤのサービス レベルの調整

SQL Server 2005 により、アプリケーション ベンダの変更、またはトランザクション分離モデルでのパラダイム シフトによって、最適であったパフォーマンスが低下することなく、ユーザーがこれらの追加コストを削減できるようになります。

オプティミスティック同時実行の実装は、SQL Server 2005 と Oracle では大きく異なります。SQL Serverでは、実装はデータベース管理者が制御しやすいデザインになっていて (前のシナリオで説明したように、コマンドで有効および無効にできます)、より管理しやすくなっています。システム関数を使用してアクセスできる、Windows System Monitor パフォーマンス カウンタおよび仮想テーブルの豊富な機能は、データベースで発生している問題の検出および解読に役立ちます。

スナップショットでの SQL Server と Oracle の相違

Microsoft SQL Server 2005	Oracle
テーブルの変更は必要ありません。	SERIALIZABLE を使用する前に、CREATE/ALTER TABLE DDL で INITRANS >= 3 と MAXTRANS を使用して、オンページ トランザクション情報のための領域を有効にする必要があります。
バージョン ストアは TempDB で保持されます。データベース管理者は、TempDB データベースのサイズも監視できるように、バージョン ストア ワークロードに基づいて、増加された入出力帯域幅に合わせて TempDB が最適化されていることを確認する必要があります。また SQL Server で、多くのリリースのデータベースとログの自動拡張設定の比率や絶対値がサポートされていることを確認する必要があります。ただし、これらはディスク領域の物理的な可用性によって明確に制限されます。	"実行時間の長い" トランザクションによって、ロールバック セグメントのバージョン ページが上書きされることが原因で発生する ORA-01555: "Snapshot too old." を回避するためには、ROLLBACK SEGMENTS (定義とオンライン / オフライン) およびトランザクション レベルの USE ROLLBACK SEGMENT ステートメントの複雑な構成が必要です。注意 :Oracle では、"実行時間の長い" トランザクションに関する定義はありません。
TempDB は、現在のサイズの比率 (自動拡張の試行数を自在に削減するため) または絶対値を使用して自動拡張できます。	ROLLBACK SEGMENTS では、PCTINCREASE がサポートされていないので、"自動拡張" しません。したがって、作成時にサイズを正しく取得する必要があります。
行ベースのデータのバージョン管理 – バージョン ストアに対するデータの書き込みおよび読み取りがより少なくなります。行レベルのバージョン管理は、トランザクションによるデータ アクセスが真に行レベルでシリアル化されることを意味します。	ページ ベースのデータのバージョン管理 – Oracle ではページ全体を再構成する必要があります。他のトランザクションによって、更新されたページの他の行に対する SERIALIZABLE アクセスを使用すると、ORA-08177: "Can't serialize access for this transaction." が発生します。
スナップショット分離と Read Committed は、データベース レベルで有効になります。このオプションが必要なデータベースだけで、スナップショット分離と Read Committed を有効にし、関連するオーバーヘッドの発生をそのデータベースだけに限定します。スナップショット分離を使用して、複数データベース トランザクションに参加するすべてのデータベースで、スナップショット分離と Read Committed を有効にする必要があります。	データのバージョン管理はオプションではなく、常に有効になっています。
拡張運用パフォーマンス カウンタにより、データベース管理者が以下のバージョン ストアの状態を監視できます。 TempDB の空き領域 バージョン ストアのサイズ 増加率 競合の数 実行時間が最長のアクティブなトランザクション	パフォーマンス モニタ カウンタ
以下の仮想テーブルでは、データベース管理者が、バージョン ストアのサイズ、バージョン ストアの最初のレコード、およびスナップショット トランザクションが発生したかどうかを確認できます。 sys.dm_tran_active_snapshot_database_transactions() sys.dm_tran_top_version_generators() sys.dm_tran_transactions_snapshot() sys.dm_tran_current_transaction() sys.dm_tran_version_store()	仮想テーブル

上で説明した相違点 (データベース管理者の作業を軽減するためにデザインされました) と同様に、類似点 (開発者がアプリケーションを Oracle から Microsoft SQL Server 2005 に移植する作業を容易にするためにデザインされました) もあります。

スナップショットでの SQL Server と Oracle の類似点

Microsoft SQL Server 2005	Oracle
SELECT ( WITH (UPDLOCK) 等価です。競合の確認をすぐに実行します。	SELECT( FOR UPDATE トランザクション内のレコードをロックして、競合を防ぐために使用します。
READ COMMITTED (行のバージョン管理付き)	READ COMMITTED
スナップショット	SERIALIZABLE
スナップショット	READ ONLY
READ UNCOMMITTED (コミットされていないデータへのアクセス)	等価な機能なし
READ COMMITTED (ロック)	等価な機能なし
REPEATABLE READ	等価な機能なし
SERIALIZABLE	等価な機能なし
ペシミスティック分離レベルでブロックを使用できます。または競合を処理し (トランザクションの外部で更新されたデータ行)、失敗したトランザクションを再試行する必要があります。行レベルのバージョン管理により、競合が発生する可能性が少なくなります。	競合を処理し (ORA-08177: トランザクションの外部で更新されたデータ ページ)、失敗したトランザクションを再試行する必要があります。
アプリケーションでは、適切な同時実行モデルを選択できます。	使用する同時実行モデルを選択できないので、アプリケーションでは常に、古い可能性があるデータが参照されます。
Transact-SQL TRY/CATCH ロジックでは、競合エラーが処理されますが、TempDB の領域の問題以外は処理されません。	PL/SQL には、ORA-08177 (競合) エラー処理を有効にするエラー処理がありますが、ORA-01555 (ロールバック セグメントの領域の問題) は処理されません。

これらの類似点に基づいて見ると、SQL Server 2005 では、以前のリリースに比較して、オプティミスティック同時実行をサポートするデータベースに対して実行するように構築されたアプリケーションの移植が大幅に容易になりました。さらに、SQL Server 2005 では、ペシミスティック同時実行制御とオプティミスティック同時実行制御のいずれかを選択できるプログラミング モデルが導入されました。また、多数の実装メカニズムも導入されました。データベース レベルで有効にできる、単純で、容易に構成できるオンラインのバージョン ストアにより、データベース管理者の作業は容易になりました。また、Oracle のスキーマと SQL Server 2005 のスキーマの密接な機能上の組み合わせにより、開発者がコードを移植する作業も容易になりました (ただし、SQL Server 2005 では、バージョン管理がページ レベルではなく行レベルである点で、よりきめ細かい一貫性動作を備えています)。

同時実行制御の理解

使用シナリオに見られるように、同時実行制御で使用される主要なモデルは、2 つあります。ペシミスティック同時実行とオプティミスティック同時実行です。ペシミスティック同時実行制御ベースのシステムでは、ロックを使用して、ユーザーが他のユーザーに影響を与える方法でデータを変更することを防止します。ロックが適用されると、所有者がロックを解放するまで、その他のユーザーはそのロックと競合する操作を実行できません。このレベルの制御は、データが競合する確率が高い環境、およびロックを使用してデータを保護するコストが、同時実行の競合が発生した場合にトランザクションをロールバックするコストよりも低い環境で使用されます。逆に、オプティミスティック同時実行制御ベースのシステムでは、ユーザーはデータを読み取る際にデータをロックしません。代わりに、更新が実行される際に、データが読み取られた後に他のユーザーがデータを変更したかどうかが、システムによって確認されます。他のユーザーがデータを更新した場合は、エラーが発生します。通常は、エラーを受け取ったユーザーにより、トランザクションがロールバックされるか、再送信されるか (アプリケーションおよび環境によって異なります)、またはやり直されます。データの競合が少ない環境、および時々トランザクションをロールバックするコストが、読み取り中にデータをロックするコストを上回る環境で使用されるので、これはオプティミスティック同時実行と呼ばれます (スナップショット分離の Read Committed で実行される更新では、競合が発生しないこと、さらにロールバックのコストが発生しないことに注意してください)。

SQL Server 2005 以前は、トランザクションはペシミスティックな方法で制御されていました。つまり、すべてのトランザクションにロックが必要でした。ロックはほとんどのアプリケーションにとって最良の同時実行の制御方法ですが、書き込みを行うユーザーが読み込みを行うユーザーをブロックする場合があります。あるトランザクションで行が変更された場合、書き込みを行うユーザーがコミットするまで、他のトランザクションではその行を読み取れません。変更が完了するまで待つことが正しい対応である場合がありますが、トランザクションとしての一貫性があれば、以前の行の状態で十分な場合もあります。

スナップショット ベースの分離レベルでは、"現在" データにアクセスしているユーザーがいない場合でも、行の変更時にこのバージョンを保持する必要があるという条件で、読み取りを行うユーザーが行の以前にコミットされた値を取得できます。これは、すべての select、update および delete (inserts は除く) ステートメントで、バージョン管理ストアに対する追加の入出力を行う、バージョン管理のコストを負担する必要があることを意味します。より適切な同時実行を行うため交換条件として、オーバーヘッドが生じ、パフォーマンスが犠牲になることを許容できるかどうかを判断する必要があります。(バージョン管理によって) 各クエリの実行にかかる負担が大きくなる代わりに、競合が軽減されることによって、最終的には、より多くスループットをサポートできるようになることを理解しておくことが重要です。競合によってスループットが犠牲になっていた場合は、この機能を有効にすることが重要になります。この機能は、競合が原因ではないパフォーマンスの問題の解決策として使用するものではないので、このような場合に使用すると、実際にはシステムのスループットが低下することがあります。

通常、データベースでバージョン管理ベースの分離を使用して自動的にデータのビューが制御されると、アプリケーションのプログラミングは容易になります。この環境では、デッドロックとブロックについてはあまり心配する必要はなく、管理のオーバーヘッドとパフォーマンスにかかる追加コストもわずかです。多くの場合、管理のオーバーヘッドおよび TempDB に提供するディスク スループットを増やすことにコストをかけることが、より容易な選択肢になります。これはよく "killing it with iron" と呼ばれ、プログラマが複雑なプログラミング ロジックに悩む必要がないというメリットがあります。スナップショットベースのすべてのクエリが、単に読み取りの一貫性を維持するために使用され、それを基にそれ以降の変更が行われない場合は、アプリケーションに再試行ロジックは必要ありません。ただし、スナップショット分離レベルを使用し、後から更新を行うトランザクションでは、競合が発生する可能性があります。バージョンが "古い" 場合は、再試行ロジックを使用して更新を行うことが必要になる可能性が高くなります。

(テーブルで実装されたキューなどの) リソースへのアクセスの制御にブロックが使用される状況 では、Read Committed スナップショットを有効にした場合、スナップショット ベースの読み取りでは決してブロックされないので、WITH (READCOMMITTEDLOCK) ロック ヒントを使用して、予想される動作を取得する必要があります。

プログラマは、以前のタイムスタンプの管理方法の代わりに、アプリケーションおよび Transact-SQL のトランザクション エラー処理と組み合わせて、SQL Server 2005 競合解決を使用できるようになりました。さらに、多数の行が変更されるバッチ方式の更新でワークロードが構成されている場合は、競合が発生する可能性が急激に高まることがあるので、スナップショット分離はお勧めしません。その場合、ロック ベースの分離レベル (READ COMMITTED、REPEATABLE READ、または SERIALIZABLE) を選択して、トランザクションを短い状態に保持し、トランザクションを慎重にデザインしてリソースの競合を最小限に抑え、デッドロックを最小限に抑える必要があります。

分離の理解

分離レベルは SQL Server 2005 で完全に制御できるので、適切なレベルの正確性を保持しながら、同時実行とパフォーマンスの両方で、アプリケーションにとって最適な分離を理解することが重要です。分離レベルの概念は新しいものではありません。実際に、ANSI の分離仕様の詳細については www.ansi.org で参照できます。現在の仕様 ANSI INCITS 135-1992 (R1998) を参照してください。ただし、標準は実装に依存しないことを意図しているので、一貫性とパフォーマンスでの実際の比較検討、およびこれらの目標と標準を実現する方法が、少しあいまいになります。結果として、標準をさらに明確にするために、多数の資料が作成されました。「 Generalized Isolation Level Definitions」、さらにその批評である「A Critique of ANSI Isolation Levels」などが発行されています。これらの資料が示す考え方、および ANSI 標準のあいまい性に基づいて、SQL Server 2005 では、一般的に求められることが予想される組み合わせを多数提供しています。

SQL Server 2005 で提供される分離レベル

分離レベル	ダーティ リード (発生し得る現象)	反復可能以外の読み取り (発生し得る現象)	ファントム (発生し得る現象)	同時実行制御
Read uncommitted	あり	あり	あり	(なし)
Read committed	なし	あり	あり	ペシミスティック
Read committed snapshot	なし	あり	あり	オプティミスティック
Repeatable read	なし	なし	あり	ペシミスティック
Snapshot	なし	なし	なし	オプティミスティック
Serializable	なし	なし	なし	ペシミスティック

上のそれぞれの条件に対するアプリケーションの使用法は、希望する "正確さ" のレベル、およびパフォーマンスと管理のオーバーヘッドで選択された比較検討によって異なります。

分離レベルと最適なアプリケーション

分離レベル	最適なアプリケーション
Read uncommitted	アプリケーションで、データの絶対的な正確性が必要なく (最終値よりも大きい/小さい数値になり得る)、OLTP 操作のパフォーマンスが、その他すべての操作よりも高いことを希望します。バージョン ストアがなく、ロックが取得されず、ロックが受け付けられません。この分離のクエリのデータの正確さでは、コミットされない変更が参照される場合があります。
Read committed	アプリケーションでは、実行時間の長い集計または実行時間の長いクエリの、特定の時点での一貫性は必要ありませんが、読み取られるデータの値にトランザクションとして一貫性があることのみが要求されます。アプリケーションでは、繰り返し可能ではない読み取りによる、実行時間の長いクエリでの不正確性と比較検討して、バージョン ストアのオーバーヘッドを希望しません。
Read committed snapshot	アプリケーションでは、実行時間が長い集計および実行時間が長いクエリの特定の時点での絶対的な一貫性が必要になります。クエリが開始される時点で、すべてのデータの値に、トランザクションとしての一貫性がある必要があります。データベース管理者は、ロックの競合が削減されることによるスループットが増加するメリットを得るために、アプリケーションのバージョン ストアのオーバーヘッドを選択します。さらに、アプリケーションでは、トランザクションではなく大規模なクエリに、トランザクションとしての一貫性が求められます。
Repeatable read	アプリケーションでは、実行時間の長い、複数ステートメントのトランザクションの絶対的な正確さが必要で、トランザクションが完了するまで、その他の変更からのすべての要求されたデータを保持しておく必要があります。アプリケーションでは、このトランザクション内で繰り返し読み取られるすべてのデータの一貫性が必要で、その他の変更が許可されないことが必要です。これは、読み取りを行うユーザーがロックしたデータが、他のトランザクションによって更新されようとしている場合、複数ユーザー システムの同時実行に影響することがあります。この方法は、アプリケーションが一貫性のあるデータに依存し、後で同じトランザクション内でそのデータを変更する予定がある場合に適しています。
Snapshot	アプリケーションでは、実行時間の長い、複数ステートメントのトランザクションで、絶対的な正確性が必要ですが、データを変更する予定はありません。アプリケーションでは、このトランザクション内で繰り返し読み取られるすべてのデータに一貫性が必要ですが、データの読み取りだけを予定しています。データの変更トランザクションのコミットまたはロールバック後まで、およびスナップショット トランザクションの完了後まで、変更が参照されることはないので、その他のトランザクションによる変更を防止するために、読み取りロックは必要ありません。スナップショット トランザクションが開始された後で同じデータを更新したトランザクションと競合するおそれはありますが、トランザクション レベル内でデータを変更できます。この競合は、各更新トランザクションによって処理される必要があります。読み取り処理が複数存在し、書き込み処理が 1 つのシステム (上記のシナリオ セクションのレプリケートされたレポート システムなど) では、競合は発生しません。
Serializable	アプリケーションでは、実行時間の長い、複数ステートメントのトランザクションの絶対的な正確さが必要で、トランザクションが完了するまで、その他の変更からのすべての要求されたデータを保持しておく必要があります。さらに、トランザクションでは、単一の行だけではなく、データのセットが要求されます。各セットでは、トランザクション内の各要求で同じ出力を生成する必要があります。また、変更に対しては、その他のユーザーが読み取ったデータを変更することだけでなく、新しい行にセットが入力されることも防止することが求められています。これは、アプリケーションが一貫性のあるデータに依存し、後で同じトランザクション内で変更する予定があり、(アクティブなデータ内で) トランザクションの最後でも絶対的な正確性とデータの一貫性が必要な場合に特に適しています。

スナップショット分離の考慮事項

スナップショットでの Read Committed への変更には、アプリケーションの変更は必要ありませんが、管理の変更は必要です。データベースでスナップショット分離を有効にすると、管理の計画と、場合によってはアプリケーションの計画が必要になります。どちら場合も、スナップショット オプションがデータベース レベルで有効になっていて、すべての場合で、TempDBの内部に行バージョン管理が格納されます。

定義、用語、および構文

SQL Server 2005 のスナップショット分離を実装するには、いくつかの新しい概念、用語および構文に慣れておく必要があります。以前のリリースでは、分離レベルはセッション設定 (SET TRANSACTION ISOLATION LEVEL) またはクエリ ヒント (FROM tablename WITH (分離ヒント)) のみによって制御されていました。SQL Server 2005 では、スナップショット分離を使用するには、2 つのサポートされたデータベースのオプションのうちの 1 つがすでに設定されている (かつ、保留中ではない) 必要があります。スナップショット分離が必要で、データベースでまだスナップショットを処理する準備ができていない (まだ保留中である) 場合は、スナップショットを要求するステートメントが失敗します。前後に変更を行う場合は、変更の時点でのデータベースとクライアント要求の状態を理解すると共に、適切な時間に変更を行うことが重要になります。

行のバージョン管理を使用するには、最初に、アプリケーションで必要な分離レベルを判断する必要があります。SQL Server 2005 では、2 種類のスナップショット分離がサポートされています。ステートメント レベルのスナップショットと、トランザクション レベルのスナップショットです。

スナップショット分離付きの Read Committed (ステートメント レベルのスナップショット)

ステートメント レベルのスナップショットが設定されているときは、read-committed 分離の各ステートメントで、ステートメントの開始前に行われた、コミット済みの変更のみが表示されることが保証されます。トランザクション内のそれぞれの新しいステートメントでは、最後にコミットされた変更が取得されます。各 SELECT ステートメントの開始時点で、バージョンの "更新" が発生します。つまり、このバージョンの read committed は、コミット済みの変更のみが表示されるという点で、意味的に似ていますが、これらの変更がコミットされるタイミングが異なります。各ステートメントでは、リソースが読み取られたときではなく、ステートメントの開始前にコミットされた変更が表示されます。言い換えると、これは read committed のまったく新しい機能です。ロックやブロックが発生せず、特定の時点、つまりステートメントの開始時点のデータの正確性が保たれます。

ステートメント レベルのスナップショットは、READ_COMMITTED_SNAPSHOT データベース オプションをオンにすることで有効になります。オンにしたら、アプリケーションにその他の変更を加える必要はありません。

構文 :

ALTER DATABASE <databasename> 
SET READ_COMMITTED_SNAPSHOT ON 
WITH <termination>

このステートメントを実行するには、データベースへのシングル ユーザー セッション アクセスが必要です。ALTER DATABASE WITH <termination> オプションを使用して、データベースのその他のユーザー セッションを終了し、未完了のトランザクションをロールバックします。理想的には、このような変更は営業時間外に行い、これが永続的な変更になるようにします。データベースにこのオプション セットがあるかどうかを確認するには、sys.databases システム ビューを使用します。

構文 :

SELECT sd.is_read_committed_snapshot_on 
FROM sys.databases AS sd 
WHERE sd.[name] = <databasename>

is_read_committed_snapshot_on では、true (1) または false (0) のいずれかの値が返されます。Read_committed_snapshot オプションがオンになっている場合は、Read Committed スナップショット分離での読み取り操作は、スナップショット スキャンに基づき、ロックなしモードで実行されます。Read_committed_snapshot がオフになっている場合は、Read Committed スナップショットでのスキャンは短期間のロック モードで実行され、ロックは読み取り要求の期間のみ保持されます。

スナップショット分離 (トランザクション レベルのスナップショット)

トランザクション レベルのスナップショット分離が設定されている場合は、既定で、スナップショット分離トランザクション内のすべてのステートメントで、トランザクションの開始前に行われた、コミット済みの変更のみが表示されることが保証されます。事実上、トランザクション内の各ステートメントでは同じデータのセットが示されますが、このトランザクションの外部で、データを変更できます。変更は防止されず、"スナップショット" トランザクションでは変更が認識されません。トランザクション レベルのスナップショットのセマンティクスで実行している場合は、各トランザクションの開始時点のみで、バージョンの "更新" が発生します。トランザクションで READ COMMITTED ヒントを優先する場合は、新しい Read Committed スナップショットがオンになっていない限り、ロック (およびブロックの可能性もあります) が発生します。新しい Read Committed スナップショットが有効である場合は、新しい READCOMMITTEDLOCK ロック ヒントを優先しない場合は、行バージョン管理を使用してクエリにデータを返します。

データベース カタログに対する DDL (データ定義) の変更は、スナップショット分離で実行中のトランザクションに直接的に影響する場合があることに注意します。

トランザクション レベルのスナップショットを実現するには、2 つの変更が必要です。最初に、データベースで ALLOW_SNAPSHOT_ISOLATION データベース オプションを使用して、トランザクション レベルのスナップショットを有効にする必要があります。次に、アプリケーションおよびユーザーがスナップショット トランザクションを要求する必要があります。

スナップショット分離の許可

管理者は、データベース オプションを設定して、スナップショット分離を許可する必要があります。このデータベース オプションはすぐに有効にならない場合がありますが、ユーザーがデータベースに接続している間に変更できます。状態の変更が行われる際に、ユーザーがトランザクションを処理している場合は、スナップショット トランザクションが発生する前に、すべてのトランザクションが完了する必要があります (現在実行中のトランザクションに対して、行バージョンが管理されていないため)。状態の変更に時間がかかり、データベースがまだ "保留中" の間にトランザクションでスナップショット トランザクションが試行されると、エラーが発生します。変更時に実行時間の長いトランザクションが実行されている場合は、バージョン管理の状態に対する変更を完了するまでに時間がかかることがあります。必要に応じて、データベース管理者は要求をキャンセルできます。要求がキャンセルされた場合は、バージョン管理の状態がロールバックされ、以前のバージョン管理 (またはバージョン管理なし) の状態に戻ります。データベースのスナップショット分離を要求するには、ALTER DATABASE を使用してデータベースの状態を変更します。

構文 :

ALTER DATABASE <databasename> 
SET ALLOW_SNAPSHOT_ISOLATION ON

オプションが有効になっているかどうかをチェックするには、sys.databases システム ビューを確認できます。関係する行は 2 つあります。snapshot_isolation_state と sd.snapshot_isolation_state_desc です。snapshot_isolation_state では、0 から 3 までの tinyint 値が返されます。

0 = スナップショット分離はオフ

1 = スナップショット分離はオン

2 = スナップショット分離の状態がオフに移行中

3 = スナップショット分離の状態がオンに移行中

snapshot_isolation_state_desc では、この保留中の状態の文字列の説明である nvarchar(60) が返されます。

OFF = スナップショット分離はオフ

ON = スナップショット分離はオン

IN_TRANSITION_TO_OFF = スナップショット分離の状態がオフに移行中

IN_TRANSITION_TO_ON = スナップショット分離の状態がオンに移行中

構文 :

SELECT sd.snapshot_isolation_state 
, sd.snapshot_isolation_state_desc 
FROM sys.databases AS sd 
WHERE sd.[name] = <databasename>

スナップショット分離の状態	説明
OFF	データベースで、スナップショット分離の状態が無効になっています。言い換えると、スナップショットベースの分離レベルのトランザクションが許可されていません。復旧の再開中は、データベースのバージョン管理の状態は当初、オフに設定されています (新しい SQL Server 2005 の機能では、復旧の REDO フェーズの後でデータベースが使用可能になります)。バージョン管理が有効である場合は、復旧が完了した後で、バージョン管理の状態がオンに設定されます。
PENDING_ON	スナップショット分離の状態を有効にする処理の途中です。ALTER DATABASE コマンドが発行された時点でアクティブであるすべての更新トランザクションが完了するまで待機します。このデータベースの新しい更新トランザクションでは、レコード バージョンを生成して、このデータベースの新しい更新トランザクションでは、レコードのバージョンを生成することによって、バージョン管理の負荷がかかり始めます。スナップショット分離のトランザクションは開始できません。
ON	スナップショット分離の状態が有効になっています。このデータ ベースで、新しいスナップショット トランザクションを開始できます。 バージョン管理の状態がオンになる前に開始された (スナップショットが有効な別のデータベースの) 既存のスナップショット トランザクションでは、このデータベースでスナップショット スキャンを実行できません。これらのトランザクションで使用されるスナップショットは、更新トランザクションでは正しく生成できないためです。
PENDING_OFF	スナップショット分離の状態を無効にする処理の途中です。新しいスナップショット トランザクションを開始できません。このデータベースのバージョン管理の負荷は、まだ更新トランザクションが担っています。既存のスナップショット トランザクションでは、まだスナップショット スキャンを実行できます。すべての既存のトランザクションが完了するまで、PENDING_OFF は OFF になりません。

スナップショット トランザクションの要求

開発者とユーザーは、トランザクションの開始点を要求するために、トランザクションがこのトランザクション モードで実行されることを要求する必要があります。

構文 :

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

データベースでこの変更が完了される前にユーザーがこのセッションの設定の変更を実行する場合、ユーザーのトランザクションはエラー「3959: Transaction failed in database <databasename> because an ALTER DATABASE command which enables snapshot isolation is not finished yet. Wait until the command is finished. 」で失敗します。

トランザクションの "開始点" について

データベースでスナップショットが許可されている場合、バージョン管理はすべての更新で実行されます。ただし、トランザクションで使用されるバージョンは、BEGIN TRAN ではなく、データに最初にアクセスするステートメントに基づきます。実際に、トランザクションの正式な開始点は、トランザクション内の最初のステートメントがデータにアクセスしなければ、そのステートメントでもありません。

構文 :

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
BEGIN TRAN 
SELECT getdate() -- (T1) transaction has not "officially begun" 
SELECT * FROM <tablename> -- (T2) transaction has officially begun 
SELECT... -- will see all committed changes as of (T2) 
SELECT... -- will see all committed changes as of (T2) 
COMMIT TRAN

Read-Committed とスナップショット分離の組み合わせ

トランザクション	スナップショット ベースの分離レベルがない場合	Read committed スナップショット (ロックなし)	スナップショット 分離	Read Committed スナップショットとスナップショット分離の両方
BEGIN TRAN
SELECT * FROM t1	ロック (アクセスされるとデータがロックされます)。ステートメントの実行中に行われたコミット済みの変更を表示できます。リソースの読み取り後にロックが解放されます。	行のコミットされたバージョン – ステートメントの開始前にコミットされました。ロックとブロックが発生しません。	行のコミットされたバージョン – トランザクションの前にコミットされました。ロックとブロックが発生しません。	行のコミットされたバージョン – トランザクションの開始前にコミットされました。ロックとブロックが発生しません。
SELECT * FROM t1 WITH (NOLOCK) OR (READUNCOMMITTED)	コミットされていないデータにアクセスできます。	コミットされていないデータにアクセスできます。	コミットされていないデータにアクセスできます。	コミットされていないデータにアクセスできます。
SELECT * FROM t1 WITH (READCOMMITTED)	ロック (アクセスされるとデータがロックされます)。ステートメントの実行中に行われたコミット済みの変更を表示できます。リソースの読み取り後にロックが解放されます。	行のコミットされたバージョン – ステートメントの開始前にコミットされました。ロックとブロックが発生しません。	ロック (アクセスされるとデータがロックされます)。ステートメントの実行中に行われたコミット済みの変更を表示できます。リソースの読み取り後にロックが解放されます。	行のコミットされたバージョン – ステートメントの開始前にコミットされました。ロックとブロックが発生しません。
SELECT * FROM t1 WITH (REPEATABLEREAD)	アクセスされるデータは、トランザクションが終了するまでロックされます。他のトランザクションではデータを変更できません。	アクセスされるデータは、トランザクションが終了するまでロックされます。他のトランザクションではデータを変更できません。	アクセスされるデータは、トランザクションが終了するまでロックされます。他のトランザクションではデータを変更できません。	アクセスされるデータは、トランザクションが終了するまでロックされます。他のトランザクションではデータを変更できません。
SELECT * FROM t1 WITH (SERIALIZABLE)	アクセスされるデータセットは、トランザクションが終了するまでロックされます。他のトランザクションではデータの変更またはデータセットへの追加ができません。	アクセスされるデータセットは、トランザクションが終了するまでロックされます。他のトランザクションではデータの変更またはデータセットへの追加ができません。	アクセスされるデータセットは、トランザクションが終了するまでロックされます。他のトランザクションではデータの変更またはデータセットへの追加ができません。	アクセスされるデータセットは、トランザクションが終了するまでロックされます。他のトランザクションではデータの変更またはデータセットへの追加ができません。
COMMIT TRAN

行バージョン管理について

バージョン管理は、実質的に行が変更または削除される際に呼び出されるコピー オン ライト メカニズムで開始されます。そのため、以前のトランザクションとして一貫性がある状態を必要とするトランザクション向けに、トランザクションの実行中に古いバージョンの行を使用できる必要があります。スナップショット トランザクションは、実質的に、これらの以前の行バージョンの、一貫性があるバージョンのデータを "参照" できます。行バージョンは、TempDB データベース内に存在するバージョン ストア内に格納されています。

より具体的には、テーブルまたはインデックスのレコードが変更されると、変更を実行しているトランザクションの "sequence_number" が新しいレコードにスタンプされます。古いバージョンのレコードはバージョン ストアにコピーされ、新しいレコードにはバージョン ストアの古いレコードへのポインタが含まれます。実行時間の長いトランザクションが複数存在し、複数の "バージョン" が必要である場合、バージョン ストアのレコードには、その行のさらに前のバージョンへのポインタが含まれている場合があります。特定のレコードの古いバージョンはすべて、リンク リスト内で連結されています。また、実行時間の長いスナップショット トランザクションの場合は、リンクをスキャンして、トランザクションとして一貫性があるバージョンの行に到達する必要がある場合があります。バージョン レコードは、関係するスナップショット クエリが存在する限り、バージョン ストア内で保持する必要があります。この期間は、主にスナップショットがステートメント ベースまたはトランザクション ベースのいずれかであるかどうかによって異なります。

Read-Committed 分離の行バージョン管理

Read-committed 分離で実行される選択では、行を参照するクエリの実行が終了すると、特定の行バージョンが必要なくなります。つまり、トランザクションの実行前または実行中に開始された、行を変更するすべての SELECT が完了したら、特定の行バージョンは必要ありません。行の変更トランザクションの実行後または実行中に開始される SELECT では、格納されたバージョンでその行バージョンがアクティブのままである必要があります。ただし、すべての SELECT が完了したら、行バージョンを削除できます。Read-committed 分離のバージョン ストアは、以前の行バージョンを頻繁に無効にすることによってサイズを自己管理しているので、あまり大きくなったり、予測が難しくなるようなことはありません。ただし、これはステートメントの長さや複雑さに依存します。

スナップショット分離の行バージョン管理

スナップショット分離で実行されるクエリでは、トランザクションの最後まで行バージョンを保持する必要があります。トランザクションでは、複数のステートメントが展開され、実行期間が長くなる可能性があります。バージョン ストアでは、期間が長くなる場合に備えて、行のバージョンを複数保持する必要があります。

次の図では、レコードの現在のバージョンはトランザクション T₃ で生成され、通常のデータ ページに格納されます。以前の状態のデータにアクセスしているスナップショット トランザクションがまだあるので、トランザクション T₂ とトランザクション T₁ で生成された、レコードの以前のバージョンは、バージョン ストア内のページに格納されます。

行バージョン管理を使用すると、古いバージョンの保持に関する作業が追加されるので、更新のパフォーマンスが低下します。ただし、競合にコストがかかっていた場合は、競合の軽減によってパフォーマンスが向上する場合があります。さらに、スナップショット ステートメントとトランザクション (バージョン リーダー) には、バージ