SQL Server 2005 Beta 2 スナップショット分離

公開日: 2004年9月8日

執筆者 : 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 つは、データベース内でデータがキャプチャされてから、組織で使用できるようになるまでの待機時間を短縮することです。この待機時間が短縮されることで、開発者は、従来のバッチ レポート スケジュールの範囲外でデータを提供するシステムを構築できるようになります。

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

図 1: レポート ユーザーによってブロックされたレジ

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 台のサーバー間での一般的なトランザクション レプリケーションのセットアップを示します。

    SQL05B02.gif

以前のトランザクション レプリケーションの使用に関する主な問題は、配布とサブスクリプションのデータベース リンクで、実行中のシステムと同様の問題が発生していたことです。サブスクライバ データベースで実行時間の長いレポートが実行されている場合、これらのレポートにより、サブスクライバ システムに到着するレプリケートされたデータがブロックされます。次に、このブロックが原因で、レプリカを同期ができないことが多くなり、実行中のシステムから遅れるようになります。さらに、最近の購入や予約の情報を顧客に提供しようとしているコール センターのスタッフが頓挫させられます。この問題には、新しいスナップショット分離および再調整された 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 分離のバージョン ストアは、以前の行バージョンを頻繁に無効にすることによってサイズを自己管理しているので、あまり大きくなったり、予測が難しくなるようなことはありません。ただし、これはステートメントの長さや複雑さに依存します。

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

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

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

SQL05B03.gif

行バージョン管理を使用すると、古いバージョンの保持に関する作業が追加されるので、更新のパフォーマンスが低下します。ただし、競合にコストがかかっていた場合は、競合の軽減によってパフォーマンスが向上する場合があります。さらに、スナップショット ステートメントとトランザクション (バージョン リーダー) には、バージョン リンク ポインタをスキャンするための追加のコストがかかります。多くのスナップショット トランザクションが存在し、これらのトランザクションの実行時間が長い場合は、より大規模な TempDB が必要になり、TempDB が適切に管理されないと、パフォーマンスが低下します。

スナップショット分離内の DDL ステートメント

行バージョン管理により変更を確認できないので、オブジェクトの構造を変更する特定のデータ定義言語ステートメントが許可されなくなります。テーブル 1 を読み取り、6 つの行が見つかったスナップショット トランザクションを例に説明します。

構文 :

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
BEGIN TRAN 
SELECT count(*) FROM <tablename> -- (6 行が返されます) 
(

2 番目のトランザクションで、このテーブルに行が追加されます。スナップショットでは、このテーブルはこのトランザクションからは見えません。このトランザクションで CREATE INDEX ステートメントが実行できたとすると、どのように機能するでしょうか? データのスナップショット ビューにインデックスを作成することになるでしょうか? それとも、通常のインデックスの作成のように、すべての行を含めることになるでしょうか? 前者を選択すると、DDL で同時に行われた変更では、どのように変更の競合を調整することになるでしょうか? さらに不都合なことに、複数のスナップショットで追加のインデックスが作成されたらどうでしょうか。そうではなく、スナップショット トランザクションの内部では CREATE INDEX が許可されなくなります。実際には、多くの DDL ステートメントが "スナップショット" の概念に違反するので、許可されなくなります。これらの DDL ステートメントは、オブジェクトの "バージョン" ではなく、実際の基本オブジェクトに対して呼び出される必要があります。

スナップショット分離内で許可されない DDL ステートメント

  • CREATE INDEX

  • CREATE XML INDEX

  • ALTER INDEX

  • ALTER TABLE

  • DBCC DBREINDEX

  • ALTER PARTITION FUNCTION

  • ALTER PARTITION SCHEME

  • DROP INDEX

  • CLR DDL

CREATE TABLE など、この一覧に記載されていないその他の DDL ステートメントは、新しいオブジェクトが作成されるので、その他のトランザクションではデータの以前のバージョンが表示されないので、許可されます。これは、上記に一覧した規則に違反しません。さらに、Service Broker で使用される DDL ステートメントは、常に read committed モードで実行されます。このようなステートメントが実行されるときは、スナップショット トランザクションの内部でも成功します。

スナップショット分離の開始後の DDL ステートメントの変更

ほとんどの運用データベースでは、スキーマは比較的安定しています。ただし、変更を行う必要がある場合もあります。システムの使用頻度が高く、ユーザーの操作がスキーマの変更と同時に行われる可能性がある場合は、プログラマはこれらの変更によって発生するエラーの準備を行う必要があります。行バージョン管理は、メタデータではなく、データ行に関してのみ存在するので、ビューがデータベースの一貫性を維持する方法の 1 つとして、スナップショット トランザクションの実行中は、サーバー インスタンス内のすべての DDL をブロックする方法があります。この方法では、実行時間の長いスナップショット トランザクションにより、データベース管理者によるデータベースの DDL の実行が妨げられることがあるので、制約がありすぎます。また、スナップショット トランザクションの実行中はDDL がサポートされますが、スナップショット トランザクションの開始後に変更されたオブジェクトにアクセスしようとすると、障害が発生する場合があります。以下にタイムライン (時間は左から右に流れます) を示します。

T1   |--- スナップショット トランザクション ------------------------ オブジェクトの使用 (失敗) -----------|

T2         |--- DDL、オブジェクトの変更---コミット --|

アプリケーションのプログラマは、スナップショット トランザクションの再試行ロジックを導入して、この種類のエラーに対応する必要があります。管理者は、1 日のうちで最もアクティブな時間に行われる DDL の変更を最小限に抑えるように努める必要があります。

トランザクションの外部での DDL の変更によるスナップショット トランザクションの障害

すべてではありませんが、ほとんどの DDL の変更がスナップショット トランザクションの失敗の原因になります。以下の表を使用してスナップショット トランザクションの実行中に発生する可能性がある DDL の影響を確認します。安定したスキーマにより、スナップショットを使用する場合のトランザクションの障害が最小限に抑えられることを、理解しておくことが重要になります。

スナップショット トランザクションの外部での DDL の変更

スナップショット トランザクションが失敗する場合

  • CREATE TABLE

  • ALTER TABLE

    * 列の変更、型の変更、XML 型のバインドの変更、制約の変更などが含まれます。

  • DROP TABLE

スナップショット トランザクションで、変更が行われた後にテーブルを使用しようとしたとき。

  • CREATE INDEX

  • ALTER INDEX

  • DROP INDEX

すべてのインデックスの変更が含まれます (クラスタ化、非クラスタ化、XML インデックス、フルテキスト インデックスなど)。

スナップショット トランザクションで、テーブルの関連するインデックスで変更が行われた後に、テーブルまたはビューを使用しようとしたとき。

  • CREATE TYPE

  • DROP TYPE

スナップショット トランザクションで、型の変更が行われた後に型を使用しようとしたとき。

  • CREATE PROC/FUNCTION/VIEW

  • ALTER PROC/FUNCTION/VIEW

  • DROP PROC/FUNCTION/VIEW

ユーザー定義集計関数を含む、TSQL と CLR の両方のプロシージャと関数が含まれます。

スナップショット トランザクションで、変更が行われた後にプロシージャ、関数、またはビューを使用しようとしたとき。

  • CREATE TRIGGER

  • ALTER TRIGGER

  • DROP TRIGGER

TSQL と CLR の両方のトリガが含まれます。

スナップショット トランザクションで、変更が行われた後にテーブルを使用しようとしたとき。

  • sp_addextendedproc

  • sp_dropextendedproc

拡張ストアド プロシージャのことです。

スナップショット トランザクションで、変更が行われた後にプロシージャを使用しようとしたとき。

  • CREATE DEFAULT/RULE

  • DROP DEFAULT/RULE

  • sp_bindefault/sp_bindrule

  • sp_unbindefault/sp_unbindrule

スナップショット トランザクションで、変更が行われた後にテーブルを使用しようとしたとき。

  • CREATE SCHEMA

  • ALTER SCHEMA

  • DROP SCHEMA

XML スキーマが含まれます。

スナップショット トランザクションで、変更が行われた後にテーブルを使用しようとしたとき。

  • CREATE ASSEMBLY

  • ALTER ASSEMBLY

  • DROP ASSEMBLY

スナップショット トランザクションで、アセンブリに変更が行われた後にアセンブリを使用しようとしたとき。

  • CREATE PARTITION SCHEME/FUNCTION

  • ALTER PARTITION SCHEME/FUNCTION

  • DROP PARTITION SCHEME/FUNCTION

データ領域が含まれます。

スナップショット トランザクションで、パーティション関数または構成を使用しようとしたとき。

テーブルでフルテキスト クロールを開始します。

スナップショット トランザクションで、変更が行われた後にテーブルを使用しようとしたとき。

フルテキスト カタログの変更

スナップショット トランザクションで、変更が行われた後に DDL を試行したとき。

CREATE EXTENDED TRIGGER ON DB FOR DDL

拡張トリガが存在するかどうかを確認する必要があるスナップショット トランザクション DDL が定義されるとき。

  • CREATE SERVICE

  • ALTER SERVICE

  • DROP SERVICE

スナップショット トランザクションで、変更が行われた後にサービスを使用しようとしたとき。

開発のベスト プラクティス

いずれかのオプティミスティック トランザクション分離手法をアクティブにする前に、システムのデータベース管理者は、システムとアプリケーションの影響に関する適正評価を行うことが期待されます。そのため、開発者は新しい分離レベルの機能の活用方法を理解し、より適切なアプリケーションを作成する役割があります。SQL Server 2005 の新機能により、開発者に新しいツールキットが提供されます。作成したアプリケーションが期待通りに機能するためには、これらの新しいツールを使用するタイミングと使用法を理解することが重要です。この資料の前半のシナリオでは、新しい分離レベルを使用できる構成について説明しました。ここでは、開発者が新しい機能を活用する方法について、さらに詳しく説明します。

Read committed スナップショット

SQL Server 2005 では、ステートメント レベルの行バージョン管理に基づいた、ブロックなしの Read Committed トランザクション分離レベルが提供されます。データベース管理者はこのオプションを有効にする必要があり、アプリケーション レベルの変更を行う必要はありません。このオプションが有効になっている場合、既定の Read Committed 分離レベルで実行中のトランザクションでは、データを読み取る際にロックが保持されません。代わりに、読み取り操作が実行される際に行われる変更からトランザクションを分離するために、バージョン ストアが使用されます。この保護はステートメント レベルで行われます。アプリケーションでは、同じ read committed トランザクション内で 2 つの select ステートメントが実行されると、結果が異なることがあります。Read Committed スナップショットでは、実行される各ステートメントの新しいバージョンにアクセスすることになります。2 つのステートメントの間にデータの変更がコミットされると、異なるデータが作成されます。

開発者はこの動作により、データの読み取り処理とデータの書き込み処理が競合してブロッキングが増加することなく、より多くのアプリケーション データを使用できるようになります。以下のようなステートメントがよく見受けられます。

SELECT count(*) 
FROM sales.dbo.orders AS o WITH (READUNCOMMITED)

または

SELECT count(*) 
FROM sales.dbo.orders AS o WITH (NOLOCK)

これらのクエリでは、分離レベルを READ UNCOMMITTED に変更して、このクエリによって新しい注文がブロックされることは阻止されますが、まだ処理中のデータ (トランザクションの完了を保留しているデータ) が表示されるという弊害もあります。したがって、合計が不正確になる可能性があります。Read Committed スナップショットの新機能を使用することで、ロック ヒントなしでクエリを実行できます。また、オンライン更新処理をブロックすることなく、コミットされたデータの正確なビューを取得できます。この機能では、データベースの安定したビューがステートメントに提供されるので、複数の結合に関与する、より複雑なクエリを実行する場合にさらに役に立ちます。この機能により、Read Uncommitted ヒントによって親レコードまたは子レコードのどちらかの側が取得されるので、これらのレコードの到着の遅れによってまれに発生する可能性がある、結果の不整合が防止されます。

Read Committed スナップショットの新機能を使用する場合、一部のアプリケーション (特にテーブルのクエリを実装するもの) では、古いブロック機能が必要になることがあります。この場合、ロック ヒント READCOMMITTEDLOCK を使用します。

SELECT TOP 1 o.OrderID AS [NextOrderID] 
FROM sales.dbo.orders AS o WITH (READCOMMITTEDLOCK) 
WHERE o.OrderStatus = 0 -- Unprocessed

このクエリでは、注文を処理するトランザクションがコミットまたはロールバックされるまで、select ステートメントはブロックされます。この機能により、注文がコミットされるまでその注文が取得されないことが保証されます。

既に説明したとおり、新しい Read Committed 機能は、複数手順のトランザクション内で使用される場合でも、ステーメント レベルのみで機能します。この機能には、さらに利点があります。スナップショット分離レベルで発生する可能性がある更新の競合は、Read Committed スナップショットでは発生しないので、開発者は、競合を処理するためのロジックを追加する必要がありません。

統一された、一貫性があるデータベースのビューを必要としない、複数の select ステートメントを含むトランザクションを実装する際は、最初の選択肢として Read Committed を選択してください。

スナップショット分離

トランザクション内の複数の select ステートメントで、統一された、一貫性があるデータベースのビューを保持することが重要になる場合があります。財務や人事のレポートなどの読み取り専用のアプリケーションには、この例が豊富にあります。このようなレポートでは、合計、小計、およびチェックサムが複数の選択で計算され、計算に数分かかる場合があるにもかかわらず、これらの値が一貫していることが重要になります。システムで、さらに長期間にわたって統一されたビューが必要な場合は、ここでは説明されていない別のデザインの考慮事項であるデータベース スナップショットを使用します。スナップショット分離では、実行時間の長いトランザクションやレポートで一貫性を保証できます。ただし、(データベース スナップショットまたはデータベースの静的な読み取り専用のコピーなしで) 一連のレポートが実行中にデータが変更される場合は、レポートに軽微な矛盾が生じるので、データの品質の問題が発生します。

常に変化しているデータベースに対して、複数のデータの集計やレポートの並べ替えを実行する、読み取り専用のシステムを構築中の (かつ、できるだけ新しいデータを必要とする) 開発者は、スナップショット分離を考慮する必要があります。トランザクションとして一貫性があるデータのビューが必要で、データベース管理者が、データベース サーバーの容量がデータベースの入出力のわずかな増加に対応できると判断した場合は、主要なシステムに対して、または SQL Server 2005 のトランザクション レプリケーションを使用して作成されたレプリカ システムに対して、このクラスのアプリケーションを作成できます。スナップショット分離では、レポート アプリケーションによる実行時間の長い読み取りロックによって、データの書き込みを行うユーザー (その他のユーザーまたはレプリケーション タスク) がブロックされることが防止されます。

開発者が、同じトランザクションのクエリ全体で一貫性のあるデータを必要とするレポートに加えて、スナップショット分離を使用できる例は他にもあります。たとえば、相関関係があるデータ ドリブンのダイアログ要素にデータを設定するとき (ここでも、ドロップダウン リストやその他の配列のコントロール間の不整合を防止するため)、またはデータベース全体に格納されたデータからシステム統計に相関関係が設定される、データベース管理者が集中管理する実稼動システムの状態ダイアログなどで使用できます。

アプリケーションで、トランザクション内で読み取られるデータに対する更新を実行する必要がある場合に、スナップショット分離による開発が役立ちます。これは、トランザクションとして一貫性があるデータベースのビューでは、トランザクションの開始時点で、競合する更新が必ずマスクされるためです。競合する更新は、更新がデータベースに送信され、競合エラーが発生するまで発見されません。

SQL05B04.gif

上記のスクリーンショットは、競合の結果として発生した SqlException を示しています。ベスト プラクティスでは、アプリケーションでこの例外をインターセプトし、訂正動作を行うことが推奨されています。

このオプティミスティック同時実行制御 (他のアプリケーションのユーザーが同じデータを更新することがないという点で、アプリケーションは楽観的です) の副作用は、ユーザーのデータが失われないことを保証するために、開発者が追加の作業を行う必要があることです。このようなロジックの多くは、データベースから データを "切断" するシステムに既に存在しています。おそらく、行レベルの増加するタイムスタンプ値に基づいて、オプティミスティック同時実行制御が提供されます。既にこのロジックがアプリケーションに存在する場合、データの作成段階で発生するブロックを削減し、データベースのアクティブなトランザクションを管理することなく競合の検出を管理するには、Read Committed スナップショットの方が適していることがあります。リソース (ここではバージョン ストア) を拘束することを避けるため、データベースへの入出力をできるだけ早くすることが、ベスト プラクティスであることは変わりません。

スナップショット分離では、トランザクション内の競合を検出するための自動的なメカニズムが提供されるので、タイムスタンプ列の追加、またはその他のスキーマの変更は必要なくなります。更新がデータベースに送信される際に競合が発生した場合、SqlException がスローされ、現在のトランザクションが中断されます。

以下の Visual C# 2005 コード フラグメントを考えてみます (ベスト プラクティスでは、(存在しない) 接続の Open とトランザクションが開始される Fill コマンドを try/catch ロジックでラップすることが推奨されていることに注意してください)。

// (SqlConnection オブジェクトの定義は省略します) 
// スナップショット分離レベルを使用してオブジェクトを定義します。
SqlTransaction DT = sqlCon.BeginTransaction(IsolationLevel.Snapshot); 
// Select コマンド ハンドラと Update コマンド ハンドラを dataadapter にフックします
// スナップショット トランザクション "DT" を使用します
SqlCommand selectCMD = new SqlCommand(); 
selectCMD.Connection = sqlCon; 
selectCMD.Transaction = DT; 
selectCMD.CommandText = "select MessageNo, MessageText from dbo.DialogText"; 
sqlDataAdapter1.SelectCommand = selectCMD; 
SqlCommand updateCMD = new SqlCommand(); 
updateCMD.Connection = sqlCon; 
updateCMD.Transaction = DT; 
updateCMD.CommandText = "update dbo.DialogText set MessageText = 
@MessageText where MessageNo = @MessageNo"; 
updateCMD.Parameters.Add("@MessageText", SqlDbType.NVarChar, 15, "MessageText"); 
updateCMD.Parameters.Add("@MessageNo", SqlDbType.SmallInt, 2, "MessageNo"); 
sqlDataAdapter1.UpdateCommand = updateCMD; 
// データを取得します
sqlDataAdapter1.Fill(dataSet1, "DialogText");

上記の Visual C# コードでは、ADO.NET と Sql Client を使用して、データベース管理者がスナップショット分離を有効にした SQL Server 2005 からのデータをデータセットに設定しています。上記のコードでは、dataset1 データセットに読み取られたデータを別のトランザクションが変更できるように、トランザクションを開いたままにしています。

下記のコードは、データベースにデータを返す操作を例示します。

// フォームのグリッド コントロールにデータをバインドします
dataGridView1.DataSource = dataSet1; 
dataGridView1.DataMember = "DialogText"; 
dataGridView1.AutoGenerateColumns = true; 
// ... 時間が経過し、競合する変更が行われます
// ユーザーが "update now" ボタンを押します
try 
{ 
sqlDataAdapter1.Update(dataSet1, "DialogText"); 
dialogTrans.Commit(); 
dataSet1.AcceptChanges(); 
} 
catch (SqlException h) 
{ 
string errorMessages = ""; 
for (int i = 0; i < h.Errors.Count; i++) 
{ 
errorMessages += "Index #" + i + "\n" + 
"Message: " + h.Errors[i].Message + "\n" + 
"ErrorNumber: " + h.Errors[i].Number + "\n" + 
"LineNumber: " + h.Errors[i].LineNumber + "\n" + 
"Source: " + h.Errors[i].Source + "\n" + 
"Procedure: " + h.Errors[i].Procedure + "\n"; 
} 
if (dialogTrans.Connection != null) 
{ 
dialogTrans.Rollback(); 
} 
MessageBox.Show(errorMessages, "Conflict Errors"); 
} 
catch (Exception i) 
{ 
dialogTrans.Rollback(); 
}

2 番目のコードでは、データセット オブジェクトがフォームのグリッド コントロールにバインドされます。グリッド コントロールでは、ユーザーがデータに対して複数の更新を実行できます。ここでは、他のトランザクションから競合する変更が並列に行われました。変更がデータベースに格納されることをユーザーが要求した場合、データセットの変更が、一連のデータベースの update ステートメントとして sqlDataAdapter に送信されます。最初に競合を検出した update ステートメントにより、作業をロールバックする例外が呼び出されます。例外が呼び出されないと、dialogTrans.Commit() ステートメントによって、トランザクションが明示的にコミットされます。

SQL05B05.gif

上記の例外ハンドラにより、スローされた SqlException がキャッチされ、エラー メッセージ (上記のスクリーンショット) が表示されます。このエラー メッセージは、アプリケーション ログに送信できます。競合は、SqlException.Errors[i].Number で明示的にテストできます。3960 はエラー番号です。ベスト プラクティスでは、その他の、より重大なエラーの場合は、エラー コレクションの全体を確認することが推奨されています。

SqlException ハンドラで dialogTrans SqlTransaction オブジェクトがテストされ、まだアクティブである (データベースに接続されている) かどうかが確認されることにも注意してください。アクティブの場合はロールバックされ、トランザクションとしての一貫性が保証されます。アクティブでないオブジェクトをコミットまたはロールバックしようとすると、COM+ 例外コード 0xE0434F49 (-532459699) とテキスト "This SqlTransaction has completed; it is no longer usable." を示す SystemException が発生します。

アプリケーションでは、例外が検出されたら、変更が拒否されたことをユーザーに通知し、新しいトランザクションで変更を再送信する機会を提供する必要があります。

競合が検出され、結果のトランザクションがロールバックされたら、開発者は作業を再実行する必要性について判断する必要があります。オプティミスティック同時実行制御メカニズムが楽観的すぎると、データの競合が頻繁に発生するので、ペシミスティック同時実行制御の方が適している場合があります。どちらのトランザクション分離の方法をアプリケーション内に展開するかを決定する際は、ロックの競合が原因で発生するブロックと、トランザクションのロールバックが原因で発生する追加の作業のバランスをとる必要があります。

管理のベスト プラクティス

問題解決に使用する方法を誤ると、パフォーマンスに悪影響が及ぶ場合があるので、管理で Read-committed 分離またはスナップショット分離を有効にする際は、慎重に決定する必要があります。適切なインデックス設定とクエリ パフォーマンスの欠如によるパフォーマンスの問題が存在する場合、行バージョン管理への変更ではこの問題を解決できません。読み取り処理と書き込み処理のワークロードの混在による重大な競合が原因で、クエリ パフォーマンスに問題が生じている場合は、Read-committed 分離 (スナップショット付き) が必要になることがあります。実行時間の長いトランザクションにトランザクションとしての一貫性が必要である場合は、スナップショットが必要になることがありますが、Read-committed 分離またはスナップショット分離により、TempDB にかかる負荷が増加します。

データベース レベルの設定

スナップショット分離はデータベース レベルで構成されるので、管理者はスナップショット分離が必要なデータベースごとに、スナップショット分離を有効にする必要があります。一部のデータベースが正しく構成されていない状態で、スナップショット分離を使用して複数データベース トランザクションが試行された場合、トランザクションは失敗します。すべてのデータベースがスナップショット分離用に構成されている場合は、複数データベース トランザクションでは、1 つのサーバー インスタンス内のデータベース全体で一貫性のあるスナップショットが使用されます。たとえば、2 つのデータベースに、同じサーバー内のスナップショットが有効になっている 2 つのテーブルがあり、更新トランザクションでこれらの 2 つのテーブルに対して同じ変更を行うことを想定します。スナップショット分離のトランザクションでは、2 つのテーブルに異なる値が設定されることはありません。

アップグレードの問題

SQL Server 2005 へのアップグレードは動的で、行ベージョン管理をサポートするための内部的な変更のみが必要になります。行バージョン管理を有効にするには、すべての text/image データに対して変更を行う必要があります。これらの変更は、アップグレード中ではなく、後で text/image データを変更する際に行われます。SQL Server 2005 では、データベースがスナップショット ベースの分離用に構成されるかどうかに関係なく、text/image データにバージョン管理の変更が行われます。アップグレードされたデータベースでは、LOB データの任意の部分が変更される際に、text/image 列がバージョン管理の変更を含むように動的に変更されます。特定の text/image 値に属するすべての text/image ページが変更されます。この操作は、多くのページにまたがる大きな値では、(ページ アロケーション、コピー、ログにより) 非常に負荷がかかる可能性があります。text/image 列の値を変更する場合の負荷は、このオーバーヘッドのみです。親データ行のみを変更する場合は、オーバーヘッドは発生しません。

text/image データの変更は最小ログ モードで実行できるので、データベース管理者は、SQL Server 2005 へのアップグレードの一環として、別の手動の手順を実行することが有益であるかどうかを判断する必要があります。text/image 値の一部のみに対する、ランダムで小規模な更新が多数存在する場合は、フラグメントのサイズを変更すると、既存の blob に多数のフラグメント化が発生することがあります。blob に対するランダムで小規模かつ部分的な更新は、一般的に行われる種類の text/image 操作ではありませんが、このオーバーヘッドは、実行中のシステムで (時間とログの両方の面で) 負担になる可能性があります。データベース管理者は、SQL Server 2005 を実稼動にする前に、(アップグレードで) すべての text/image データをこの新しい形式に変更する手順を追加することを考慮する必要があります。

この変更を行うには、通常、以下の手順を行います。

  1. データベースを SQL Server 2005 にアップグレードします。

    • インプレース – 1 つのアップグレード プロセスですべてのコンポーネントが更新されるので、インプレース アップグレードが最も簡単な方法です。

    • 新しいサーバーに SQL Server 2005 をインストールし、バックアップと復元を使用してアップグレードします。SQL Server 2005 では、SQL Server 2000 データベースの復元がサポートされています。

    SQL Server 2000 からのアップグレードの詳細については、Books Online の「Preparing to Upgrade to SQL Server 2005」を参照してください。

  2. 復旧モデルを確認し、"SIMPLE" または "BULK_LOGGED" に変更します。この処理を完了した後でデータベースの完全バックアップが実行されるので、単純復旧モデルが適しています。

  3. すべての text/image データ値に対して更新を実行します。

  4. 復旧モデルを変更して、希望する復旧モデル (完全復旧モデル) に戻します。

TempDB のバージョン ストアの使用法

バージョン ストアは、TempDB で管理されます。そのため、TempDB のサイズの設定は、システムの全体的なパフォーマンス、および一部の実行時間の長いトランザクションで行バージョン管理を使用できるかどうかにとって重要です。たとえば、少ない空き領域で TempDB が実行されている場合、バージョン ストアでクリーンアップが試行されると、パフォーマンスが低下します。通常のクリーンアップ関数は、バックグラウンドで 1 分ごとに実行され、バージョン ストアからすべての再利用可能な領域を再利用しようとします。TempDB で空き領域が不足すると、自動拡張が行われる前に、通常のクリーンアップ関数が呼び出されます。ディスクがいっぱいで、自動拡張ではファイルのサイズを拡大できない場合は、行バージョン管理は停止されます。スナップショット クエリでレコードが検出され、領域の制限で生成されなかった、そのレコードの古いバージョンを読み取ろうとすると、クエリは失敗します。更新と削除は失敗せず、更新と削除の行バージョンを要求するクエリのみが失敗します。バージョン ストアがいっぱいになると、更新と削除で行バージョンが生成されなくなるためです。

または、実行時間の長いスナップショット クエリおよびスナップショット トランザクションが検出されたら、それらを終了します。クエリをキャンセルすることで、バージョン ストアのサイズを縮小できます。TempDB のイベント (エラー番号 3958) にスクリプトを関連付けることで、この操作を自動化できます。これは、ほとんどのアプリケーションに望ましいエラー操作です。このような処理を行わないと、バージョン ストアの領域不足の問題により、多くのトランザクションが失敗することになります。

スナップショット分離を使用して運用システムの円滑な実行を保証するには、データベース管理者は TempDB に十分なディスク領域を割り当てて、常に約 10 % の空き領域があるようにする必要があります。空き領域が 10% 以下になると、バージョン ストアの領域を再利用しようとして、バージョンのクリーンアップ処理の時間が長くなるので、システムのスループットは低下します。

TempDB の入出力のパフォーマンスが問題になった場合、データベース管理者は複数の TempDB を異なるディスクに作成して、入手力の帯域幅を拡大する必要があります。実際に、マルチプロセッサのコンピュータでは、プロセッサの数に合わせてファイル数を増やすと、パフォーマンスが向上することが多くあります。詳細については、「サポート技術情報」 (Microsoft Knowledge Base) の文書番号 328551 「tempdb データベースの同時実行制御の強化」を参照してください。

TempDB のサイズの設定

Read Committed スナップショットのみが必要である場合は、行バージョン管理が長時間保持されることはないので、TempDB のサイズの設定はそれほど重要ではありません。ただし、トランザクションが過度に長い場合、読み取り処理と書き込み処理の両方による、実行時間の長いトランザクションが原因で問題が発生する場合があります。さらに、スナップショット分離モードで実行中の場合、必要とされる TempDB の領域が増加します。以下の式を使用して、スナップショット分離のクエリを実行するために必要な TempDB の領域の量を予測することをお勧めします。

TempDB で保持する必要がある領域の量を予測するには、後で開始されるスナップショット トランザクションで古いバージョンにアクセスできるように、バージョン ストアのすべての変更をアクティブなトランザクションで保持する必要があることを、最初に考慮する必要があります。さらに、アクティブなスナップショット トランザクションがある場合、スナップショットが開始された時点でアクティブだった、以前のトランザクションによって生成されたすべてのバージョン ストアのデータも、それらのデータを使用する最後のスナップショット トランザクションが完了するまで、保持される必要があります。

[バージョン ストアのサイズ] = 2 *
[1 分あたりに生成されるバージョン ストアのデータ] *
[トランザクションの最長の実行時間 (分)]

トランザクションで 1 分あたりに生成されるバージョン ストアのデータは、そのトランザクションで 1 分あたりに生成されるログ行とほぼ同じです。パフォーマンス モニタ カウンタを使用して、1 分あたりに生成されるバージョン ストアのデータの量を確認できます。運用システムでは、これらのカウンタを監視して、TempDB のサイズを微調整することを考慮する必要があります。

十分なディスク領域がある場合は、潜在的な領域の問題を回避するために必要であると予測されるよりも多い領域を常に割り当てます。TempDB のサイズを予測する際は、データベース管理者は DBCC CHECKDB、DBCC CHECKTABLE、インデックスの作成、クエリ、およびその他の操作に必要な領域の要件も考慮する必要があります。

バージョン ストアの利用状況の監視

バージョン ストアの利用状況を監視するには、仮想テーブルにアクセスする関数から、パフォーマンス モニタ カウンタ、プロファイラ イベントまでさまざまな方法があります。各方法では、異なる観点から、システムで現在行われている操作が監視されます。

関数 : dm_tran_active_snapshot_database_transactions ():

この関数では、すべてのアクティブなトランザクションに対応する仮想テーブルが、行バージョンに関連する sequence_number と共に返されます。シーケンス番号は、スナップショット分離で実行中のトランザクションのみに含まれます。自動コミット モードの読み取り専用のトランザクションおよびシステム トランザクションは、仮想テーブルに表示されません。

関数により、以下の列が返されます。

列名

説明

transaction_id

bigint

システムで開始された各トランザクションに設定された一意な番号すべてのトランザクションに id があります。

transaction_sequence_number

bigint

トランザクションの開始時点を示す、一意なシーケンス番号。バージョン レコードが生成されないトランザクション、およびスナップショット スキャンが使用されないトランザクションでは、トランザクションの連続番号は必要ありません。

commit_sequence_number

bigint

トランザクションの終了時点 (コミットまたはアボート) を示すシーケンス番号。アクティブなトランザクションでは、値は NULL です。

is_snapshot

bit

トランザクションがスナップショット トランザクションであるかどうか。

Spid

Int

このトランザクションを開始した接続のプロセス ID。

first_snapshot_sequence_number

Bigint

スナップショット トランザクションの開始時は、その時点でアクティブなすべてのトランザクションのスナップショットを取得します。これは、スナップショット内のトランザクションの中で最も低い sequence_number です。

max_version_chain_traversed

int

スキャンされたバージョン チェーンの最長の長さ。

average_version_chain_traversed

int

スキャンされたバージョン チェーンの平均の長さ。

elapsed_time_seconds

bigint

トランザクションで sequence_number が取得されてからの経過時間 (秒単位)。

テーブルでは、"transaction_sequence_number" 列のシーケンスでデータが出力されます。この出力で、開始時点に基づいてトランザクションが表示されるので、"elapsed_time_seconds" によって、実行時間の長いトランザクションを判断できます。

実行時間の長さ (古さ) が上位 10 個のトランザクションの確認

SELECT TOP 10 atx.transaction_id 
, atx.[name] 
FROM sys.dm_tran_active_snapshot_database_transactions() AS atx

最長の長さのバージョン チェーンをスキャンしたトランザクションの確認

SELECT TOP 1 atx.* 
FROM sys.dm_tran_active_snapshot_database_transactions() AS atx 
ORDER BY atx.max_version_chain_traversed
関数 : dm_tran_transactions_snapshot():

この関数では、すべてのアクティブなトランザクションに対応する仮想テーブルが、行バージョンに関連する sequence_number と共に返されます。この関数では、各スナップショット トランザクションが開始された時点でアクティブであるトランザクションの sequence_number の仮想テーブルが返されます。

関数により、以下の列が返されます。

列名

説明

transaction_sequence_number

BIGINT

トランザクションの sequence_number。X とします。

snapshot_sequence_number

BIGINT

トランザクション X の開始時点でアクティブであるトランザクションの sequence_number。最初の "snapshot_sequence_number" も dm_tran_active_transactions の first_snapshot_sequence_number 列に表示されていることに注意してください。

Snapshot_id

BIGINT

read committed スナップショットで開始された各ステートメントのステートメント ID。

:

T1: BEGIN TRAN T1

T1:    SELECT ... FROM ...

T2: BEGIN TRAN T2

T2:    SELECT ... FROM ...

T3: BEGIN TRAN T3

最初に、トランザクションのシーケンス情報を確認します。

SELECT atx.transaction_sequence_number 
, atx.[name] 
, atx.first_snapshot_sequence_number 
, atx.commit_sequence_number 
FROM sys.dm_tran_active_transactions() AS atx

T3 では、以下の結果が返されます。

sequence_number

name

first_snapshot_sequence_number

commit_sequence_number

50

T1

0

NULL

52

T2

50

NULL

53

T3

50

NULL

次に、実行中のスナップショットトランザクションを確認します。

SELECT txs.* 
FROM sys.dm_tran_transactions_snapshot() AS txs

T3 では、以下の結果が返されます。

transaction_sequence_number

snapshot_sequence_number

Snapshot_id

50

0

0

52

50

0

53

50

0

53

52

0

結果が示すことは、実行中のスナップショット トランザクションが複数存在し、最初のトランザクションのシーケンス番号は 50 であるということです。このシーケンス番号での更新は、トランザクションが完了するまでバージョン ストアに保持する必要があります。このスナップショット トランザクションがシーケンス番号 52 で中断された時点で、トランザクション T2 が開始されます。新たなトランザクション (T3) が開始され、すでに行バージョンを備えたトランザクションが 2 つ存在するので、T3 は (順序の面で) T1 と T2 の両方に依存します。

パフォーマンス モニタ カウンタ

Windows 2003 パフォーマンス ツール (Windows 2000 のシステム モニタ) により、データベース管理者は、さまざまなシステムと SQL Server のカウンタをグラフィック インターフェイスで監視し、パフォーマンス カウンタをパフォーマンス ログに記録し、パフォーマンス ログを分析し、これらのイベントに基づいて操作を定義できます。また、API を使用して、データベース管理者が独自のプログラムを開発し、これらのカウンタにアクセスして適切な操作を実行できます。

以下のさまざまなカウンタを使用できます。

カウンタ

説明

(1) Free Space in tempdb (KB)

tempdb の空き領域 (KB) です。

バージョン ストアは tempdb に存在するので、データベース管理者は tempdb に十分な空き領域があることを確認する必要があります。これは、tempdb の空き領域の計算を実行することで実装できます。

(2) Version Store Size(KB)

バージョン ストアのサイズ (KB) です。

データベース管理者は、バージョン ストアに使用されている tempdb の領域を認識できます。

(3) Version Generation rate(KB/s)

バージョンの生成率 (KB/秒) です。

(4) Version Cleanup rate(KB/s)

バージョンのクリーンアップ率 (KB/秒) です。

カウンタ 3 および 4 の情報を使用して、データベース管理者は TempDB のサイズの要件を予測し、そのための領域を準備できます。

カウンタ

説明

(5) Version Store unit count

バージョン ストアで使用されている AppendOnlyStorageUnit の数です。このカウンタは、現在アクティブなバージョン単位の数を表します。

(6) Version Store unit creation

バージョン ストアでの新しい AppendOnlyStorageUnit の作成です。このカウンタは、インスタンスの開始以降の数を示します。

(7) Version Store unit truncation

バージョン ストアで使用されている AppendOnlyStorageUnit の切り捨てです。このカウンタは、インスタンスの開始以降の数を示します。

カウンタ 5、6 および 7 から、データベース管理者は、バージョン ストアで現在使用されている AppendOnlyStorageUnit の数と、現在までに作成された AppendOnlyStorageUnit および切り捨てられた AppendOnlyStorageUnit の数を認識できます。

カウンタ

説明

(8) Update conflict ratio

更新スナップショット トランザクションの合計に対する、データの競合が発生している更新スナップショット トランザクションの部分です。

データベース管理者は、この比率に基づいて、スナップショット分離トランザクション レベルの適切性を確認できます。トランザクションでは複数の更新が保持される場合があることに注意します。ここでは、更新自体ではなく、更新を実行するトランザクションの数が基準になります。更新の数を基準として使用しない理由は、異常に低い数値が提供されることがあるためです。これは、更新の競合では、トランザクション内のその他の以前の更新がロールバックされる際に限り、分子の数が 1 ずつ増えるためです。トランザクションが成功した場合は、トランザクション内の更新の数だけ、分母の数が増えます。

注意 : これは比率のカウンタで、最後の秒の更新の競合率を示します。

(9) Longest Transaction Running Time

トランザクションの最長の実行時間 (秒単位) です。

データベース管理者は、これを確認し、非常に長い時間実行されているトランザクションがあるかどうかを確認できます。詳細については、データベース管理者は仮想テーブル dm_tran_active_transactions() にクエリして、transaction_id と spid を取得できます。このテーブルは elapsed_time 列で並べ替えられ、データベース管理者は、実行時間の長さが上位 n 個のトランザクションを、情報と共に参照できます。

(10) Transactions

アクティブなトランザクションの合計数です。

数は、システムのすべてのアクティブなトランザクションを示します。SQL Server のバックグラウンドの内部トランザクションが含まれますが、システム トランザクションは含まれません。

(11) Snapshot Transactions

アクティブなスナップショット トランザクションの合計数です。

(12) Update Snapshot Transactions

update ステートメントも含むアクティブなスナップショット トランザクションの合計数です。

(13) NonSnapshot Version Transactions

バージョン レコードを生成するスナップショット以外のトランザクションの合計数です。

この値は、スナップショット分離を要求しなかった更新から取得されます。

更新が行われるすべてのスナップショット トランザクションで、結果としてバージョンが生成されるので、バージョンを生成するトランザクションの合計数は、カウンタ 12 と 13 の合計になります。また、データベース管理者は、カウンタ 11 と 12 から、読み取り専用のスナップショット トランザクションの数を確認できます。また、データベース管理者は、カウンタ 11 と 12 から、読み取り専用のスナップショット トランザクションの数を確認できます。

このように、データベース管理者はこれらのカウンタから、バージョン管理機能の使用状況および使用方法を確認できます。

上記のすべてのカウンタは、サーバー全体に対応し、"SQLServer: Transactions" という新しいパフォーマンス モニタ オブジェクトにグループ化されています。

関連情報

スナップショット分離は、システムの管理と開発の両方の側面に影響を与えるので、必ずすべての側面を理解することが重要です。実行時間の長いトランザクションが発生し、頻繁に変更が行われる場面で、データベース管理者が不必要にスナップショット分離を許可すると、TempDB のサイズが適切でない場合、ユーザーが変更をコミットする際に問題が発生します。さらに、開発者が期待するように Read Committed スナップショットが設定されていない場合、検出されないデータの不整合が発生することがあります。必ず、すべての関連リソースを確認してください。詳細については、ベータ版のニュースグループにご参加ください。

Books Online のトピック

Understanding Snapshot Isolation

Adjusting Transaction Isolation Levels

Using Snapshot

Preparing to Upgrade to SQL Server 2005

関連するサポート技術情報 (Microsoft Knowledge Base)

328551: tempdb データベースの同時実行制御の強化

その他の情報

Generalized Isolation Level Definitions: https://research.microsoft.com/~adya/pubs/icde00.pdf 

A Critique of ANSI SQL Isolation Levels: https://research.microsoft.com/research/pubs/view.aspx?tr_id=5