ADO.NET 2.0 のクエリ通知

  • [アーティクル]

Bob Beauchemin
DevelopMentor

September 2004

適用対象:
   Microsoft ADO.NET 2.0
   Microsoft SQL Server 2005 Beta 2

概要: ADO.NET 2.0 と SQL Server 2005 の新しい通知テクノロジを使用してアドホックなデータ更新をハンドルする方法を説明します。

目次

はじめに
SqlDependency がキャッシュ使用にもたらすソリューション
SQL Server 2005 のクエリ通知
エンド ユーザーやキャッシュに対する通知のディスパッチ
データベース クライアントからのクエリ通知の使用
通知を使用すべきでない状況: 注意事項
まとめ

はじめに

重要なリレーショナル データベース アプリケーションには必ず多数のルックアップ テーブルがあるものです。 専用のグラフィカル ユーザー インターフェイスを作成する場合、これらはドロップダウン リスト ボックスに設定されるリストになります。 ここでは、ルックアップ テーブルを 2 種類に分類します。 1 つは読み取り専用テーブル、もう 1 つは read-mostly テーブルです。 この 2 種類のテーブルの違いは、どのような場合にテーブルを変更できるかにあります。 テーブルを変更するのにスタッフ会議やユーザー会議が必要な場合、そのテーブルは読み取り専用テーブルであると考えられます。 たとえば、企業の製品カテゴリを含むテーブルなどがこれに当たります。 そのようなテーブルは、新製品の開発や組織の再編が行われない限り変更されることはありません。 一方、read-mostly テーブルは、あまり変更されることはないがエンド ユーザーによる変更が可能なリストです。 これらは通常、ドロップダウン リストではなくコンボ ボックスで提示されます。 たとえば、敬称テーブルが read-mostly テーブルの使用例です。 様、殿、先生などの一般的な敬称をアプリケーションの設計者があらかじめ用意しておいても、設計時には思いつかなかった敬称をユーザーが後から追加したくなることが必ずあります。 実際、このようなことはよくあります。たとえば、筆者がこの前に関わった中規模製品の第三正規形リレーショナル データベースには、350 ～ 400 のテーブルが含まれていましたが、 そのうち約 250 のテーブルは読み取り専用テーブルか read-mostly テーブルだと考えられます。

従来の Web アプリケーション (3 層アプリケーションの典型) では、これらの種類のテーブルは一般にできるだけ多くキャッシュされます。 そうすることによって、データベースへのラウンドトリップの回数はもちろん、データベースのクエリの負荷も減るため、新しい注文のようなユース ケースで応答性が向上します。 読み取り専用テーブルをキャッシュするのは簡単です。テーブルを常にキャッシュに保持し、ごくまれにテーブルの再読み込みが必要になったときのために、データベース管理者 (DBA) がキャッシュを再読み込みできるようにしておきます。 なお、ここでは、データベースの基本的な構造や内容を変更する会議はめったに開かれないものと想定しています。 一方、中間層のキャッシュに保持されている read-mostly ルックアップ テーブルの更新は、それほど簡単ではありません。 スケジュールを設定してたまにキャッシュを更新したとしても、必要な動作は得られません。つまり、それでは、ユーザーによる変更がすぐに他のユーザーに反映されるようにはなりません。 また、サポート担当者が別のアプリケーションを使用して新しい項目を追加し、その項目を使用しようとしているユーザーにインスタント メッセンジャーでメッセージを送信したとしても、そのユーザーの選択肢のリストにはその新しいエントリは含まれていません。 さらに悪いことに、そのユーザーがその "見つからないエントリ" をリストに追加しようとすると、その項目は既に存在するという内容のデータベース エラーが表示されます。 こうした問題があるため、read-mostly テーブルは、"更新ポイント" が複数ある場合には一般にキャッシュされません。

かつては、メッセージ キュー、ファイルへの書き込みを行うトリガ、または帯域外プロトコルを使用して、read-mostly テーブルがアプリケーションの外部から更新された場合にキャッシュに通知するソリューションを独自に作成するしかありませんでした。 こうした "シグナリング" ソリューションは、行が追加または変更されたことを通知して、キャッシュを更新する必要があることを知らせるだけです。 どの行が変更または追加されたかの通知はまた別の問題で、分散データベースおよびトランザクション レプリケーションやマージ レプリケーションの領域になります。 オーバーヘッドの低いシグナル ソリューションでは、キャッシュが無効になったというメッセージをプログラムが受け取ると、単純にキャッシュ全体が更新されます。

SqlDependency がキャッシュ使用にもたらすソリューション

SQL Server 2005 と ADO.NET 2.0 を使用する場合は、組み込みのシグナリング ソリューションを使用できます。このソリューションは、SqlClient データ プロバイダと、クエリ通知と呼ばれるデータベースに組み込まれています。 ついに、この一般的な問題に対するソリューションが組み込まれて、簡単に使用できるようになったのです。 ASP.NET 2.0 内部の組み込み機能によって、クエリ通知も直接サポートされています。 ASP.NET の Cache クラスを通知に登録できるほか、ASP.NET が使用するページやページの断片のキャッシュと組み合わせて通知を使用することもできます。

この便利な機能を実現するインフラストラクチャは、SQL Server 2005 クエリ エンジン、SQL Server サービス ブローカ、システム ストアド プロシージャ、 sp_DispatcherProc、ADO.NET の SqlNotification (System.Data.Sql.SqlNotificationRequest) クラスと SqlDependency (System.Data.SqlClient.SqlDependency) クラス、および ASP.NET の Cache (System.Web.Caching.Cache) クラスによって構成されています。 この機能のしくみを簡単にまとめると、次のようになります。

  1. ADO.NET の各 SqlCommand には、通知の要求を表す Notification プロパティが含まれています。 SqlCommand が実行されたときに Notification プロパティが設定されていると、通知の要求を示すネットワーク プロトコル (TDS) パケットが要求に追加されます。
  2. SQL Server は、要求された通知のサブスクリプションをクエリ通知インフラストラクチャに登録し、コマンドを実行します。
  3. SQL Server は、当初返された行セットを変更するような操作が行われないかどうか、SQL DML ステートメントを "ウォッチ" します。 変更が発生した場合は、サービス ブローカ "サービス" にメッセージが送信されます。
  4. このメッセージが送信されると、次のいずれかの処理が行われます。
    1. 登録されているクライアントに通知が返信されます。
    2. 高度なクライアントによる独自の処理のために、メッセージがサービス ブローカのサービスの使用可能なキューに入れられます。

Dd278204.querynotification_01(ja-jp,MSDN.10).gif

図 1. クエリ通知の高レベルの概要

ASP.NET の SqlCacheDependency (System.Web.Caching.SqlCacheDependency) クラスと OutputCache ディレクティブは、SqlDependency を使用して自動通知機能を利用します。 ADO.NET クライアントでより高度な制御が必要な場合は、SqlNotificationRequest を使用して、サービス ブローカのキューを手動で処理できます。これにより、どのようなカスタム セマンティクスでも実装できます。 サービス ブローカの詳細な説明はこのアーティクルの範囲を超えますので、拙著『A First Look at SQL Server 2005 for Developers』のサンプル (英語) および Roger Wolter 氏によるアーティクル「SQL Server Service Broker の紹介」を参照してください。

先に進む前に、行セットが変更されたときに受け取る通知メッセージは各 SqlNotificationRequest または SqlDependency につき 1 つであることを理解しておく必要があります。 このメッセージは、変更が INSERT によるものであっても、DELETE (1 つ以上の行の削除) によるものであっても、UPDATE (1 つ以上の行の更新) によるものであっても同じです。 どの行が変更されたかや、行がいくつ変更されたかなどの情報は、通知には一切含まれません。 この 1 つの通知メッセージを受け取ったキャッシュ オブジェクトやユーザー アプリケーションの側には、選択肢は 1 つしかありません。 行セット全体を最新の情報に更新し、通知に対して再登録するだけです。 メッセージが複数送られることはないため、メッセージが 1 つ送られると、データベースのユーザーのサブスクリプションは削除されます。 また、クエリ通知のフレームワークは、通知がまったく行われないよりは余分なイベントの通知まで行われる方がよい、という前提に立っています。 このため、行セットが変更されたときだけでなく、行セットに関係するテーブルが削除または変更されたときや、データベースがリサイクルされたときなど、その他の場合に通知が送られることもあります。 通知が送られた原因が何であれ、一般にキャッシュやプログラムの反応は変わりません。 キャッシュされたデータを更新し、通知に対して再登録するだけです。

以上でこの機能の全般的な意味を把握できたので、今度は、次の 3 つの観点からこの機能の動作について詳しく見ていきます。

  1. SQL Server ではクエリ通知がどのように実装されているのか、また、オプションのディスパッチャはどのように機能するのか。
  2. SqlClient の SqlDependency と SqlNotificationRequest はクライアント層や中間層でどのように機能するのか。
  3. ASP.NET 2.0 は SqlDependency をどのようにしてサポートするのか。

SQL Server 2005 のクエリ通知

サーバー レベルでは、SQL Server がクライアントからのクエリをバッチ処理します。 各クエリ (SqlCommand.CommandText プロパティとしてのクエリ) に含めることができるバッチは 1 つだけですが、1 つのバッチに複数の T-SQL ステートメントを含めることができます。 また、SqlCommand を使用してストアド プロシージャやユーザー定義関数を実行することもできます。この場合にも、複数の T-SQL ステートメントを含めることができます。 さらに、SQL Server 2005 では、クライアントからのクエリに 3 つの追加情報が含まれている場合もあります。 通知の配信先となる サービス ブローカ サービスの名前、通知の識別子 (文字列)、および通知のタイムアウトの 3 つです。 クエリ要求にこれら 3 つの情報と SELECT ステートメントまたは EXECUTE ステートメントが含まれていた場合、SQL Server は、クエリによって生成された行セットで他の SQL Server セッションによる変更を "ウォッチ" します。 ストアド プロシージャの実行などによって複数の行セットが生成された場合は、すべての行セットが "ウォッチ" されます。

では、行セットを "ウォッチ" するとはどういうことでしょうか。また、SQL Server はそれをどのようにして行うのでしょうか。 行セットの変更検出のメカニズムは、SQL Server エンジンに組み込まれています。そこで使用されているメカニズムは、インデックス付きビューを同期するための変更検出のメカニズムとして SQL Server 2000 から実装されていたものです。 Microsoft は SQL Server 2000 でインデックス付きビューの概念を導入しました。 SQL Server のビューは、1 つ以上のテーブルの列に対するクエリで構成されています。 ビューには、テーブル名のように使用できる名前があります。 以下に例を示します。

CREATE VIEW WestCoastAuthors
AS 
SELECT * FROM authors
  WHERE state IN ('CA', 'WA', 'OR')

こうして作成したビューは、クエリでテーブルのように使用できます。以下に例を示します。

SELECT au_id, au_lname FROM WestCoastAuthors
  WHERE au_lname LIKE 'S%'

ビューをご存じの方は多いでしょうが、インデックス付きビューはまだそれほど知られていないかもしれません。 インデックス付きではないビューでは、ビューのデータが別のコピーとしてデータベースに格納されることはなく、ビューが使用されるたびに基になるクエリが実行されます。 したがって、たとえば上の例では、行セット WestCoastAuthors を取得するクエリが実行され、そのクエリに、特定の WestCoastAuthors を取り出すための述語が含まれます。 一方、インデックス付きビューではデータのコピーが格納されるため、WestCoastAuthors をインデックス付きビューにすると、これらの著者のデータが 2 つ存在することになります。 このため、インデックス付きビューからと元のテーブルからの 2 とおりの方法でデータを更新できるようになります。 したがって、SQL Server では、一方の変更をもう一方に適用するために、両方の物理データ ストアで変更を検出しなければなりません。 この変更検出のメカニズムと同じメカニズムが、クエリ通知を使用するときにエンジンによって使用されます。

インデックス付きビューでは、変更検出の実装方法との関係で、すべてのビューにインデックスを付けられるわけではありません。 このインデックス付きビューの制限は、クエリ通知で使用できるクエリにも当てはまります。 たとえば、WestCoastAuthors ビューでは、書き込まれた内容にそのままインデックスを付けることはできません。 インデックスを付けるには、ビュー定義で 2 部構成の名前を使用して、行セットのすべての列に明示的に名前を付ける必要があります。 したがって、ビューを次のように変更すると、インデックスを付けられるようになります。

CREATE VIEW WestCoastAuthors
WITH SCHEMABINDING
AS 
SELECT au_id, au_lname, au_fname, address, city, state, zip, phone
  FROM dbo.authors
  WHERE state in ('CA', 'WA', 'OR')

通知で使用できるのは、このインデックス付きビューのルールに従っているクエリだけです。 なお、クエリ通知では、クエリの結果が変更されていないかどうかを特定するために使用される "メカニズム" はインデックス付きビューと同じですが、インデックス付きビューのように SQL Server でデータのコピーは作成されません。 ** ** インデックス付きビューには多岐にわたるルールがあります。ルールのリストについては、SQL Server 2005 Books Online を参照してください。 通知要求と共に送信されたクエリがこのルールに従っていなかった場合、SQL Server は、理由 ("無効なクエリ") を含む通知を直ちにポストします。 では、SQL Server はどこに "通知をポストする" のでしょうか。

SQL Server 2005 は、サービス ブローカの機能を使用して通知をポストします。 サービス ブローカは、SQL Server に組み込まれている非同期キュー機能です。 クエリ通知はサービス ブローカ "サービス" を使用します。 この場合の　"サービス" は非同期メッセージの宛先で、メッセージは、コントラクトと呼ばれる一連のルールに従っている必要があります。 サービス ブローカ "サービス" は常に、メッセージの物理的な宛先となる "キュー" に関連付けられています。 なお、クエリ通知のコントラクトは SQL Server に組み込まれています (https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification という名前)。

メモ   コントラクトの SQL Server オブジェクト名は、たまたま URL になっていますが、これは Web 上の場所を意味するものではありません。 dbo.authors などのテーブル名と同じ、単なるオブジェクト名です。

要するに、クエリ通知メッセージの宛先には、適切なコントラクトをサポートする任意のサービスを指定できます。 このようなサービスを定義する SQL DDL は、たとえば次のようになります。

CREATE QUEUE mynotificationqueue
CREATE SERVICE myservice ON QUEUE mynotificationqueue
 ([https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
GO

これで、サービス myservice をクエリ通知要求で宛先として使用できるようになります。 SQL Server は、サービスにメッセージを送信することによって通知を送ります。 独自のサービスを使用することも、MSDB データベースの組み込みサービスを使用するように指定することもできます。 独自のサービスを使用する場合は、メッセージを読み取って処理するためのコードを記述する必要があります。 MSDB の組み込みサービスを使用する場合は、メッセージを配信するためのコードがあらかじめ用意されています。 これについては、後に説明します。

クエリ通知はサービス ブローカを使用するため、追加の要件がいくつかあります。

  1. 通知クエリが実行されるデータベースでサービス ブローカが有効になっている必要があります。 Beta 2 の AdventureWorks サンプル データベースでは、サービス ブローカが既定では有効になっていません。DDL ステートメント "ALTER DATABASE SET ENABLE_BROKER" を使って有効にすることができます。

  2. クエリを送信するユーザーには、クエリ通知にサブスクライブするための権限が必要です。 この権限はデータベース単位で設定されます。サブスクライブするための権限を現在のデータベースでユーザー 'bob' に与える DDL ステートメントは次のようになります。

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO bob

エンド ユーザーやキャッシュに対する通知のディスパッチ

これまでに、正しい種類のクエリ バッチを通知要求と共に SQL Server に送信する段階まで説明しました。 SQL Server が行セットを監視し、誰かが行セットを変更すると、指定したサービスにメッセージが送信されます。 では、その後についてはどうでしょうか。 通知が発生したらメッセージを読み取って必要なロジックを実行するコードを独自に記述することもできますが、 組み込みのディスパッチャを使用することもできます。 ここでは、このディスパッチャについて見ていきます。

クエリ通知で独自のサービスを指定しない場合は、MSDB データベースの既定のサービス (https://schemas.microsoft.com/SQL/Notifications/QueryNotificationService) が使用されます。 ** このサービスのキューに入れられたメッセージは、キューに関連付けられているストアド プロシージャ (sp_DispatcherProc) によって自動的に処理されます。 ここでのポイントは、このプロシージャが使用するコードが .NET で記述されていることです。つまり、SQL Server 2005 のインスタンスで .NET 共通言語ランタイム (CLR) の読み込みが有効になっていないと、クエリ通知の自動配信は機能しません (.NET CLR の読み込みの有効/無効は SQL Server のインスタンスごとに設定できます)。

クエリ通知メッセージが送られてくると、sp_DispatcherProc (以降は "ディスパッチャ" と呼びます) は、SqlDependency 通知キューのクエリ通知サブスクリプションのリストを確認し、各サブスクライバにメッセージを送信します。 つまり、ディスパッチャが使用されると、サーバーからクライアントにデータの変更が通知されることになります。 これには 2 つの利点があります。 1 つは、クライアントが通知をポーリングする必要がなくなること、もう 1 つは、クライアントが通知を受け取るために SQL Server に対する接続を開いたままにする必要がなくなることです。 ディスパッチャはこの通知を、HTTP プロトコルまたは TCP およびプライベート プロトコルを使用して各サブスクライバに送信します。 オプションとして、サーバーからクライアントへの通信で認証を使用することもできます。 通知が配信されると、アクティブなサブスクリプションのリストからサブスクリプションが削除されます。 前にも述べたように、クライアントが各サブスクリプションで受け取る通知は 1 つだけです。再びクエリを送信して再度サブスクライブする指示はクライアント側に任されます。

データベース クライアントからのクエリ通知の使用

以上でクエリ通知の内部のしくみがすべてわかったので、今度は、この機能を使用する ADO.NET クライアントを実際に作成してみましょう。 この比較的単純なクライアント側コードを記述する前にすべての説明を行ったのは、 このコードを記述する際には、ごく簡単なコードとはいえ、先に述べたルールに従う必要があるからです。 クエリ通知のコードでは、たとえば、通知に対して無効なクエリを送信したり、サービス ブローカやユーザー権限をセットアップし忘れるなどの問題がよく起こります。 こうした問題がこの強力な機能に対する不満を招き、一部のベータ テスト参加者に、これは使えないのではないかという印象を与えたことすらありました。 しかし、このような問題は、わずかな準備作業と調査によって回避できます。 このほか、サービス ブローカ サービスやディスパッチャのプロトコルなどのプロパティを指定する必要があるため、先に内部のしくみを把握しておけば、これらの用語を理解してから作業に入ることができるというメリットもあります。

ここで行うように ADO.NET でクエリ通知クライアントを作成する際には、OLE DB を使用することも、新しい HTTP Web サービス クライアントを使用することもできます。ただし、クエリ通知はクライアント側コードからしか利用できないという点に注意してください。 この機能を直接 T-SQL で使用したり、SqlServer データ プロバイダを使って SQL Server とやり取りする SQLCLR プロシージャ コードで使用したりすることはできません。

System.Data.dll アセンブリには、使用できるクラスが 2 つ含まれています。 SqlDependency と SqlNotificationRequest です。 SqlDependency は、ディスパッチャを使用した自動的な通知が必要な場合に使用します。 SqlNotificationRequest は、通知メッセージを自分で処理する場合に使用します。 以下でそれぞれの例について見ていきます。

SqlDependency の使用

SqlDependency を使用する手順は簡単です。 まず、クエリ通知が必要な SQL ステートメントを含む SqlCommand を作成し、 作成した SqlCommand を SqlDependency に関連付けます。 続いて、SqlDependency の OnChanged イベントに対するイベント ハンドラを登録します。 次に、SqlCommand を実行します。 その後、DataReader を処理し、DataReader を閉じることができます。関連付けられている SqlConnection も閉じてしまってかまいません。行セットが変更されると、ディスパッチャによって通知が送られてきます。 このオブジェクトのイベントは別々のスレッドで発生するため、コードがまだ実行されている間にイベントが発生しても対処できるようにしておく必要があります。 まだバッチの結果を処理している間にイベントが発生する場合も考えられます。 以下にコードを示します。

using System;
using System.Data;
using System.Data.SqlClient;

static void Main(string[] args)
{
  string connstring = GetConnectionStringFromConfig();
  using (SqlConnection conn = new SqlConnection(connstring))
  using (SqlCommand cmd = 
   // "SELECT * FROM ..." ではなく、2 部構成のテーブル名を使用します
   new SqlCommand("SELECT au_id, au_lname FROM dbo.authors", conn))
 {
  try
  {
    // cmd に関連付けられた依存関係を作成します
    SqlDependency depend = new SqlDependency(cmd);
    // ハンドラを登録します
    depend.OnChanged += new OnChangedEventHandler(MyOnChanged);

    conn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    // DataReader を処理します
    while (rdr.Read())
       Console.WriteLine(rdr[0]);
    rdr.Close();
    // 無効化されるのを待ちます
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
  }
  catch(Exception e)
   { Console.WriteLine(e.Message); }
 }   
}

static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
  Console.WriteLine("result has changed");
  Console.WriteLine("Source " + e.Source);
  Console.WriteLine("Type " + e.Type);
  Console.WriteLine("Info " + e.Info);
}

おなじみの WithEvents キーワードを SqlDependency と一緒に使用して、同じコードを Visual Basic .NET で記述することもできます。 なお、このプログラムでは、基になる結果が何度変更されようとも、受け取って処理される OnChanged イベントは 1 つだけです。 重要な用途に使用する場合は、いったん通知を受け取ったら新しい通知要求と共にコマンドを再送信し、その結果を使用してキャッシュを新しいデータで更新する、という処理が必要になります。 上の例の Main() のコードをルーチンに組み入れ名前を付けると、このコードは次のようになります。

static void Main(string[] args)
{
    GetAndProcessData(); 
    UpdateCache();
    // ユーザーがプログラムを終了するのを待ちます
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
}
static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
    GetAndProcessData(); 
    UpdateCache();
}

この後に示すように、ASP.NET の Cache クラスをデータ キャッシュとして使用することによって、ASP.NET 2.0 でもまったく同じことを行えます。

SqlDependency を使用する場合は、クライアントへの接続の作成や通知メッセージの送信を SQL Server 2005 内部のディスパッチャ コンポーネントに委ねることになります。 これは、SqlConnection を使用しない帯域外の通信になります。 この場合、クライアントが SQL Server から "ネットワークで到達可能" でなければならないため、ファイアウォールやネットワーク アドレス変換が障害となる可能性があります。 以降のベータ版では、ポート構成をより柔軟に制御できるようになって、ファイアウォールへの対応が強化されるようです。 SqlDependency のコンストラクタでパラメータを指定することによって、サーバーからクライアントへの通信の動作を完全に構成できます。 以下に例を示します。

SqlDependency depend = new SqlDependency(cmd,
    null,
    SqlNotificationAuthType.None,
    SqlNotificationEncryptionType.None,
    SqlNotificationTransports.Tcp,
    10000);

この SqlDependency のコンストラクタを使用すると、既定の動作とは異なる動作を選択できます。 中でも最も便利なのは、サーバーがクライアントへの接続に使用する利用ポートを変更できることです。 この例では SqlNotificationTransports.Tcp が使用されていますが、TCP または HTTP をサーバーで使用できます。 このパラメータの既定値は SqlNotificationTransports.Any で、使用するトランスポートをサーバーが "決定" できるようになっています。 Any が指定されている場合、サーバーは、クライアントのオペレーティング システムにカーネルモードの HTTP サポートが含まれている場合は HTTP を選択し、含まれていない場合は TCP を選択します。 Windows Server 2003 と Windows XP SP2 には、カーネルモードの HTTP サポートが含まれています。 このほか、メッセージはネットワークで送信されるため、使用する認証の種類も指定できます。 現在は EncryptionType というパラメータもありますが、以降のベータ版では取り除かれます。 どちらのパラメータも既定値は None です。 SqlNotificationAuthType は統合認証もサポートしています。 さらに、サブスクリプションのタイムアウト値や SQL Server のサービス ブローカのサービス名を明示的に指定することもできます。 サービス名は、上の例のように通常は null に設定されますが、組み込みサービスの SqlQueryNotificationService を明示的に指定することもできます。 この中でオーバーライドすることが多いと思われるパラメータは、SqlNotificationTransport とタイムアウトです。 なお、これらのパラメータはサーバー側のディスパッチャの動作を指定するものであるため、SqlDependency でしか使用できません。 SqlNotificationRequest ではディスパッチャは使用されません。

SqlNotificationRequest の使用

SqlNotificationRequest を使用する場合、セットアップ作業は SqlDependency よりほんの少し複雑なだけですが、メッセージをプログラムで処理する必要があります。 SqlDependency を使用すると、サーバー上の通知が MSDB の SqlQueryNotificationService に送信されて、メッセージが自動的に処理されます。 しかし、SqlNotificationRequest を使用する場合は、メッセージを自分で処理する必要があります。 SqlNotificationRequest と、先に定義したサービスを使用する簡単な例を以下に示します。

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

class Class1
{
  string connstring = null;
  SqlConnection conn = null;
  SqlDataReader rdr = null;

  static void Main(string[] args)
  {
    connstring = GetConnectionStringFromConfig();
    conn = new SqlConnection(connstring));
    Class1 c = new Class1();
    c.DoWork();  
  }

  void DoWork()
  {
    conn.Open();
    rdr = GetJobs(2);
    if (rdr != null)
    {
      rdr.Close();
      WaitForChanges();
    }
    conn.Dispose();
  }

  public SqlDataReader GetJobs(int JobId)
  {
    using (SqlCommand cmd = new SqlCommand(
        "Select job_id, job_desc from dbo. jobs where job_id = @id", 
        conn))
    {
      
    try
    {
      cmd.Parameters.AddWithValue("@id", JobId);
      SqlNotificationRequest not = new SqlNotificationRequest();
      not.Id = new Guid();

      // pubs データベースの MyService というサービスを指定する必要があります
      // このサービスは notificationqueue というキューに関連付けられています (以下を参照)
      // サービスは QueryNotifications コントラクトに従っている必要があります
      not.Service = "myservice";
      not.Timeout = 0; 
      // 通知要求をフックします
      cmd.Notification = not;

      rdr = cmd.ExecuteReader();
      while (rdr.Read())
        Console.WriteLine(rdr[0]);
      rdr.Close();
    }
    catch (Exception ex)
    { Console.WriteLine(ex.Message); }
    return rdr;
    }
  }

  public void WaitForChanges()
  {
    // 通知がキューに入れられるのを待って
    // 自分で読み取ります
    using (SqlCommand cmd = new SqlCommand(
     "WAITFOR (Receive convert(xml,message_body) from notificationqueue)",    
      conn))
    {
      object o = cmd.ExecuteScalar();
      // 通知メッセージを自由に処理できます
      Console.WriteLine(o); 
    }
  }

SqlNotificationRequest を使用する場合の (追加の作業にともなう) 強みは、通知を待って自分で処理しなければならない点にあります。 SqlDependency を使用する場合は、通知を受け取るまでデータベースに再接続する必要はありません。 SqlNotificationRequest を使用する場合も、実際にただ通知を待ち続ける必要はなく、ときどきキューをポーリングすることができます。 また、SqlNotificationRequest を使用すると、通知が発生したときに実行されていない場合もある特殊なアプリケーションを記述することもできます。 このアプリケーションでは、起動時にキューに接続して、前回の実行時に保存された "永続キャッシュ" の結果のうち、無効になっているものを特定できます。

アプリケーションが何時間も何日も通知を待ち続ける可能性を考えると、"データが変更されなかった場合、通知はいつ無効になるのか" という疑問が出てきます。 通知が無効になる (通知がデータベースのサブスクリプション テーブルから削除される) 場合は 2 つしかありません。通知が発生した場合と、通知が期限切れになった場合だけです。 通知サブスクリプションがいつまでも残っていると問題になるため (SQL リソースが使用されるほか、クエリや更新のオーバーヘッドも増加します)、データベース管理者が SQL Server の通知を手動で廃棄することもできます。 そのためには、まず、SQL Server 2005 の動的ビューでクエリを実行して、問題の通知サブスクリプションを見つけます。見つかったら、それを削除するコマンドを発行します。

-- 全てのサブスクリプションを調べます
  SELECT * FROM sys.dm_qn_subscriptions

  -- 任意のサブスクリプションの ID を指定します
  -- 終了したいサブスクリプションの ID が 42 であると仮定します
  KILL QUERY NOTIFICATION SUBSCRIPTION 42

ASP.NET での SqlCacheDependency の使用

通知は、ASP.NET の Cache クラスにも関連付けられます。 ASP.NET 2.0 では、CacheDependency クラスをサブクラス化できます。また、SqlDependency が SqlCacheDependency によってカプセル化され、ASP.NET の他の CacheDependency と同じように動作します。 SqlCacheDependency は SqlDependency と違って、SQL Server 2005 より前のバージョンの SQL Server でも動作します。 もちろん、SQL Server 2005 より前のバージョンに対してはまったく別の方法で実装されます。

以前のバージョンの SQL Server を使用する場合、SqlCacheDependency は、"ウォッチ" するテーブルのトリガを利用して機能します。 これらのトリガは、別の SQL Server テーブルに行を書き込みます。 その後、このテーブルがポーリングされます。 依存関係を有効にするテーブルやポーリング間隔の値は構成可能です。 SQL Server 2005 より前のバージョンの実装の詳細はこのアーティクルの範囲を超えています。「ASP.NET 2.0 でのキャッシングの改善」を参照してください。

SQL Server 2005 を使用する場合は、上で説明した ADO.NET の例と同様の SqlDependency のインスタンスが SqlCacheDependency によってカプセル化されます。 SqlCacheDependency の使用方法を示す短いコード例を以下に示します。

// Page.Load から呼び出されます
CreateSqlCacheDependency(SqlCommand cmd)
{
  SqlCacheDependency dep = new SqlCacheDepedency(cmd);
  Response.Cache.SetExpires(DateTime.Now.AddSeconds(60);
  Response.Cache.SetCacheability(HttpCacheability.Public);
  Response.Cache.SetValidUntilExpires(true);
  Response.AddCacheDependency(dep);
}

使いやすい便利な機能として、SqlCacheDependency は、ページやページの断片のキャッシュにも関連付けられます。 特定の ASP.NET OutputCache ディレクティブで宣言することによって、すべての SqlCommand を通知に対して有効にすることができます。 この場合、ページのすべての SqlCommand に対して同じ SqlDependency が使用されます。たとえば、SQL Server 2005 データベースを使用する場合は次のようになります。

<%OutputCache SqlDependency="CommandNotification" ... %>

ここで、CommandNotification は、"SQL Server 2005 と SqlDependency を使用する" という意味のキーワード値です。このディレクティブ パラメータの構文は、以前のバージョンの SQL Server を使用する場合にはまったく異なります。 また、キーワード値 CommandNotification は、特定のバージョンのオペレーティング システムで ASP.NET 2.0 を実行している場合にのみ有効です。

積極的な通知

SQL Server のクエリ通知は、通知が行われないよりは多すぎるくらいの方がよい、という設計方針に基づいています。 通知を受けるのは、別のユーザーが行に加えた変更によってキャッシュが無効になった場合がほとんどですが、必ずしもそればかりではありません。 たとえば、DBA によってデータベースがリサイクルされた場合にも通知が送られてきます。 また、クエリ内のテーブルに対して ALTER、DELETE、TRUNCATE などの操作が行われた場合も通知されます。 クエリ通知は SQL Server のリソースを圧迫するため、SQL Server でリソース不足が深刻になると、内部テーブルからクエリ通知が削除され始めます。このような場合にもクライアントに通知が送られます。 また、各通知要求にはタイムアウト値が含まれているため、サブスクリプションがタイムアウトになったときにも通知されます。

SqlDependency を使用する場合は、この情報がディスパッチャによって SqlNotificationEventArgs のインスタンスにラップされます。 このクラスには 3 つのプロパティが含まれています。 Info、Source、および Type の 3 つで、これらを使用して通知が発生した原因を特定できます。 SqlNotificationRequest を使用する場合は、キューのメッセージの message_body フィールドに含まれている XML ドキュメントに同じ情報が含まれています。ただし、この場合は、XPath や XQuery を使用してこの情報を自分で解析する必要があります。 前の ADO.NET の SqlNotificationRequest の例から生成された XML ドキュメントの例を以下に示します。

<qn:QueryNotification 
 xmlns:qn="https://schemas.microsoft.com/SQL/Notifications/QueryNotification" 
 id="2" type="change" source="data" info="update" 
 database_id="6" user_id="1">
<qn:Message>{CFD53DDB-A633-4490-95A8-8E837D771707}</qn:Message>
</qn:QueryNotification>

この通知は、job_id = 5 の行の job_desc 列の値を "new job" に変更することによって生成したものですが、message_body 自体にはこの情報は含まれていません。 このことは、通知プロセスの微妙な意味合いの問題につながります。 通知からわかるのは、SQL ステートメントによって、行セットを変更する可能性がある変更が加えられたということだけです。 UPDATE ステートメントが実際には行の値を変更していなくても、そこまではわかりません。 たとえば、行を job_desc = "new job" から job_desc = "new job" に変更した場合も通知は発生します。 また、クエリ通知は非同期で、コマンドやバッチを実行した瞬間に登録されるため、行セットの読み取りが終了する前に通知を受ける可能性もあります。 ルール (先に説明したインデックス付きビューのルール) に従っていないクエリを送信した場合にも即座に通知が送られてきます。

通知を使用すべきでない状況: 注意事項

クエリ通知がどのように機能するのかがわかれば、それをどのような場合に使用すればよいのかもすぐにわかります。 クエリ通知は、read-mostly ルックアップ テーブルに適しています。 通知行セットはそれぞれ SQL Server のリソースを消費するため、読み取り専用テーブルに対して使用するとリソースの無駄になります。 また、アドホック クエリに対して使用すると、同時に "ウォッチ" される行セットの数が多くなるため、これもお勧めできません。 知っておくと便利な内部の詳細として、SQL Server では、異なるパラメータ セットを使用するパラメータ クエリの通知リソースが 1 つにまとめられます。 常にパラメータ クエリを使用するようにすると (上の SqlNotificationRequest の例を参照) 、このしくみを利用してパフォーマンスを強化できます。 なお、心配になった方のために付け加えておきますが、このパフォーマンス機能を利用しても、適切な通知を受けられなくなることはありません。 たとえば、au_lname の値をパラメータとして、au_lname が A ～ M の著者をユーザー 1 が、au_lname が N ～ Z の著者をユーザー 2 がそれぞれウォッチする場合、各ユーザーには、それぞれのサブセットに対する "適切な" 通知のみが送られます。

最後の注意点として、通知アプリケーションと聞くと、大勢の株式仲買人が集まった部屋で、めまぐるしく変わる相場が画面に映し出されている様子を思い浮かべる人もいるでしょう。 これは、次の 2 つの理由から、この機能の利用方法としてはまったく不適切です。

  1. 行セットが絶えず変化し続けるため、クエリ通知とクエリ更新要求でネットワークが飽和してしまいます。
  2. 同じデータを大勢のユーザーが "ウォッチ" すると、通知が発生するたびに、多数のユーザーが同じ結果を求めて同時にクエリを再実行することになります。 その結果、同じデータに対する多数の要求によって SQL Server が飽和してしまいます。

プログラマによるこの機能の乱用を心配されている方には朗報があります。以降のベータ版では、動的な管理ビューを通じて DBA がこの機能を監視できるように、SQL Server からより多くの情報が提供されるようになるようです。 現段階では、これらのビューにはサブスクリプションしか表示されません。 ただし、既に説明したように、SQL Server 2005 では、通知によって消費されているリソースが多すぎると "判断" されると、通知が自動的に削除されます。

まとめ

クエリ通知は SQL Server 2005 に組み込まれた強力な新機能で、ADO.NET 2.0 および ASP.NET 2.0 から直接使用できます。 ADO.NET の機能 (SqlNotificationRequest と SqlDependency) は SQL Server 2005 データベースに対してしか機能しませんが、ASP.NET では、ポーリングを使用した別のメカニズムを通じて、この機能を以前のバージョンの SQL Server に対しても使用できます。 この機能を使用する際には、その意味や影響を考えて賢く活用するようにしてください。 この機能の最適な対象としてまず考えられるのは、ASP.NET によって使用される read-mostly ルックアップ テーブル (他のアプリケーションによって更新される可能性がある場合) です。また、Web アプリケーションにも適しています。 このシナリオでは、プログラマが何年も待ちわびたソリューションがクエリ通知によってもたらされます。

Bob Beauchemin 氏は、DevelopMentor の教官、学習コースの作成者、およびデータベース カリキュラム コースの連絡係です。 データ中心の分散システムのアーキテクト、プログラマ、および管理者としての 25 年を超える経験を持っています。 Microsoft Systems Journal、SQL Server Magazine、およびその他のものに ADO.NET、OLE DB、SQL Server に関する記事を執筆したことがあり、『A First Look at SQL Server 2005 for Developers Non-MS link』と『Essential ADO.NET Non-MS link』の著者でもあります。