連載：Visual Studio 2005でいってみようDBプログラミング 第12回　データベース・プログラミング 7つのヒント − 同時実行制御からASP.NET AJAXまで − 山田 祥寛（http://www.wings.msn.to/） 2007/01/27

Page1 Page2 Page3 Page4

　これまで11回にわたって、Visual Studio 2005（以降、VS 2005）を利用したデータベース・プログラミングの基本、そしてASP.NET 2.0と連携した具体的なアプリケーション構築の手順について学習してきました。

　いよいよ最終回となる今回は、これまでに紹介しきれなかったデータベース・プログラミングにおける7つのテクニックを紹介し、締めくくりにしたいと思います。

今回作成するサンプル・プログラムのダウンロード（vs2005db_12.zip）

Hint 1. 同時更新による競合を検出するには？

　Webアプリケーションでは、複数のユーザーが同時に同一のデータにアクセスし、更新を試みるような状況がよく発生します。例えば、ユーザーA、Bが同一のデータXを読み込み、表示した状況を考えてみましょう。

　この状態で、ユーザーAはX→Yに、ユーザーBはX→Zのように更新したとしたらどうでしょう。ユーザーA、またはBのいずれか最初に行った変更は、結果的に無視されることになります。このような現象のことを「競合」といいます。

　SqlDataSourceコントロールでは、このような競合が生じた場合の処理をConflictDetectionプロパティで指定することができます。

［Note］オプティミスティック同時実行制御 　 ConflictDetectionプロパティの値は、プロパティ・ウィンドウのほか、［データ構成ウィザード］の［SQL生成の詳細オプション］ダイアログから［オプティミスティック同時実行制御］項目で指定することも可能です。 　「オプティミスティック（楽観的）同時実行制御」とは、データの競合は「原則として起こらない」ことを前提とし、データ取得時にはロックは行わず、更新時にほかのユーザーによる同時更新を検出する同時実行制御の考え方のことをいいます。ちなみに、競合が起こることを前提に、データ取得時から厳密にロックを制御する方式のことを「ペシミスティック（悲観的）同時実行制御」といいます。

　ConflictDetectionプロパティの値がデフォルトの「OverwriteChanges」である場合、SqlDataSourceコントロールは主キーのみを使用して更新対象データを特定します。つまり、競合の有無にかかわらず、常に最後に行われた更新が有効になるというわけです。

　一方、「CompareAllValues」を指定した場合、SqlDataSourceコントロールは更新前のオリジナル値を内部的に保持し、すべての列情報でもって更新対象行を特定します。つまり、更新前に取得したデータと更新時に取得したデータとが異なる場合（ほかのユーザーによって先に更新が行われた場合）、SqlDataSourceコントロールはこれを競合と見なし、更新を行いません。

　説明のみではやや分かりにくいという方は、OverwriteChanges／CompareAllValues指定それぞれの場合で、自動的に生成される更新コマンド（UpdateQueryプロパティの文字列値）を比較してみるとよいでしょう。

UPDATE [books] SET [title] = @title, [price] = @price, [publish] = @publish, [publishDate] = @publishDate WHERE [isbn] = @isbn

リスト1　ConflictDetectionプロパティがOverwriteChangesの場合のUPDATE

UPDATE [books] SET [title] = @title, [price] = @price, [publish] = @publish, [publishDate] = @publishDate WHERE [isbn] = @original_isbn AND [title] = @original_title AND [price] = @original_price AND [publish] = @original_publish AND [publishDate] = @original_publishDate

リスト2　ConflictDetectionプロパティがCompareAllValuesの場合のUPDATE

　後者では、WHERE句にすべてのフィールド値が渡されていることが確認できるはずです。オリジナル値のパラメータ名は、「@original_＜フィールド名＞」の形式です。パラメータ名を変更したい場合には、OldValuesParameterFormatStringプロパティから名前書式を変更する必要があります。

　なお、CompareAllValuesで競合が検出された場合の処理（例えば、ユーザーへのメッセージ表示など）を記述するためには、SqlDataSourceコントロールのUpdatedイベントを利用します。Updatedイベントは、SqlDataSourceコントロールによる更新処理が完了したタイミングで発生するイベントです。

' SqlDataSourceコントロールのUpdateイベント・ハンドラ Protected Sub sds_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)   If e.AffectedRows = 0 Then     Response.Write("データ競合が発生しました")   End If End Sub

リスト3　更新時の競合を検出するためのコード（GridViewEdit.aspx）

　Updatedイベント・ハンドラは、第2パラメータとしてデータベースへの更新情報を保持するSqlDataSourceStatusEventArgsオブジェクト（System.Web.UI.WebControls名前空間）を受け取ります。ここでは、そのAffectedRowsプロパティで実際に更新された行数を取得し、0であった場合にエラー・メッセージを表示しているというわけです。

　それではさっそく、実際に動作を確認してみましょう。

　配布サンプルに含まれるGridViewEdit.aspxを2つのブラウザで同時に開き、双方とも編集モードにしたうえで、順番に更新処理を行ってみましょう。すると、後から更新処理を行った方のブラウザでは、以下のようにエラー・メッセージが表示されるはずです。

図1　競合を検出し、ブラウザにエラーを表示

Hint 2. 標準的なオプティミスティック同時実行制御の問題点とその解決方法

　Hint 1でも見たように、ASP.NETではごくシンプルなプロパティ設定だけで、オプティミスティック同時実行制御を実装することが可能です。しかし、その利用に当たっては、注意しなければならない点もあります。

（1）処理対象のレコードにNull値が含まれている場合

　処理対象のレコードにNull値が含まれている場合には、標準のオプティミスティック同時実行制御は正しく動作しません（更新／削除処理が正しく動作しません）。

　この問題を回避するためには、SqlDataSourceコントロールのUpdateCommand／DeleteCommandプロパティに自動生成されたSQL命令（のWHERE句）を以下のように修正する必要があります。これによって、Null値を正しく検出できるようになり、更新／削除処理も正しく実行されます（IS NULL演算子による条件の追加は、該当するすべてのフィールドについて行う必要があります）。

WHERE   [isbn] = @original_isbn AND   ([title] = @original_title OR [title] IS NULL) AND ……

リスト4　Null値に対応するよう修正したSQL命令

ここではtitleフィールドがNullであってもオプティミスティック同時実行制御が正しく実行されるようにIS NULL演算子による条件を追加している。 【2007/10/15 追記】ただし、このコードでも完全ではなく、同時実行しているユーザが非NULL→NULLに変更 した場合には更新できてしまう。オプティミスティック同時実行制御を行う場合には、後述のTIMESTAMP列を利用した方法がシンプルで確実だろう。

（2）数値／日付型の値を文字列整形した場合

　例えば、以下のサンプルの例のように、GridViewコントロール（BoundField列）上で数値や日付型のデータをDataFormatStringプロパティで整形するようなケースは多くあります。

図2　GridViewコントロール上で数値を「{0:#,###円}」の形式に整形

　この状態で、GridViewコントロールからレコードを削除すると、以下のようなエラーが発生することを確認できるはずです。

図3　削除時にエラーが発生

　これは、Hint 1でも見たように、ASP.NET標準のオプティミスティック同時実行制御では、削除時に「すべてのフィールド値を引き渡してデータの存在をチェックしている」ためです。このため、例えば「2,620円」のような文字列をINT型のフィールドと比較しようとすると、データ型の変換が行えないためにエラーが発生するというわけです。

　もちろん、GridViewコントロール上での文字列整形をやめればエラーは解消できますが、これはあまりに本末転倒な話です。また、SqlDataSourceコントロールのDeletingイベント（削除処理前に発生する）を利用して、整形された数値を元の形式に再変換するという方法もあるかもしれませんが、こんなことのためにわざわざコードを記述しなければならないというのもおかしな話でしょう。

■TIMESTAMP列を利用したオプティミスティック同時実行制御

　そこで登場する解決方法が、データベースの更新をチェックするためのフィールドを追加することです。

　繰り返しですが、ASP.NET標準のオプティミスティック同時実行制御は、対象レコードがほかのユーザーによって更新されたかどうかを、すべてのフィールド値を比較することによって行います。実は上に挙げた（1）（2）の問題はいずれも、この「すべてのフィールド値を比較している」がために発生している問題なのです。であれば、変更の検出を専用の列を使って行えるようにしてやればよいのです。

　具体的には、対象のテーブルに「TIMESTAMP列」を追加して、この値がデータ取得時から変更されているかどうかを確認します。

　TIMESTAMP列とは、名前のイメージとはやや異なり、いわゆる日付／時刻を表すものではありません。SQL Server内部で管理されているデータベースに一意のタイムスタンプ値を表すもので、該当のレコードが挿入／更新されるタイミングでデータベースのタイムスタンプ値が更新されると、連動して対応するレコードのTIMESTAMP列も更新されます。TIMESTAMP列を利用することで、該当のレコードが以前の取得タイミングから更新されたかどうかを確認することができるというわけです*1。TIMESTAMP列は、いうなれば「バージョン管理」列といってもよいかもしれません。

*1 TIMESTAMP列を提供しないデータベースを利用している場合には、同様の操作を「最終更新時刻」列を設けることで実現できます。その場合には、最終更新時刻を更新時に自分で更新する必要がありますが、それ以外はTIMESTAMP列とほとんど同じ手順で実装できるはずです。

　それでは、以下に具体的にTIMESTAMP列を用いたオプティミスティック同時実行制御を実装する方法を見ていくことにしましょう。

［1］対象のテーブルにTIMESTAMP列を追加する

　例えば、ここでは第2回で作成したbooksテーブルに、TIMESTAMP型のupdatedフィールドを追加してみます。

　修正後のbooksテーブルのフィールド・レイアウトは、以下のようになります（太字が修正個所）。

列名 データ型 概要 isbn VARCHAR(30) ISBNコード（主キー） title VARCHAR(100） 書名 price INT 価格 publish VARCHAR(50) 出版社 publishDate DATETIME 配本日 updated TIMESTAMP タイムスタンプ値

表1　booksテーブルのフィールド・レイアウト

［2］TIMESTAMP列に対応したGridViewTimestamp.aspxを作成する

　次に、第3回で作成したサンプルをコピーして、GridViewTimestamp.aspxを作成しておきます。以降、こちらを修正して、TIMESTAMP列によるオプティミスティック同時実行制御を実装してみることにします。

　次の表2に、修正するべきプロパティをまとめます（太字が修正個所）。

コントロール プロパティ 設定値 GridView（grid） DataKeyNames isbn ,updated SqlDataSource （sds） ConflictDetection OverwriteChanges SelectQuery SELECT [isbn], [title], [price], [publish], [publishDate], [updated] FROM [books] WHERE ([publish] = @publish) UpdateQuery UPDATE [books] SET [title] = @title, [price] = @price, [publish] = @publish, [publishDate] = @publishDate WHERE [isbn] = @original_isbn AND [updated] = @original_updated publishDate DATETIME 配本日 updated TIMESTAMP タイムスタンプ値

表2　GridViewTimestamp.aspxのプロパティ情報

　GridViewコントロールのDataKeyNamesプロパティには、GridViewコントロール上で主キー項目として扱われるフィールド名を設定します。キーとして複数のフィールドが存在する場合は、本サンプルの例のようにカンマ区切りで指定してください。

　DataKeyNamesプロパティで指定されたフィールドの値は、データソース・コントロールが更新／削除処理を行う場合に、対象レコードを特定するために引き渡されます。これに合わせて、対応するSqlDataSourceコントロール（sds）のSelectQueryプロパティでもupdated列をデータベースから取得するように改めておきましょう。

　あとは、UpdateQueryプロパティでupdated列をオリジナルのフィールド値と比較する条件句を追加するだけです。これによって、ほかのユーザーによって該当するレコードが更新された（タイムスタンプ値が変更された）場合には、条件式がマッチしないことになります。

［3］データ競合時の処理を定義する

　最後に、競合が検出された場合の処理をSqlDataSourceコントロールのUpdatedイベント・ハンドラに記述しておきましょう。処理の内容は、Hint 1で紹介したとおりですので、ここでは割愛します。SqlDataSourceStatusEventArgsオブジェクトのAffectedRowsプロパティで更新された行数を取得し、0であった場合に競合が発生したと見なして、エラー・メッセージを表示するというわけです。

　以上を理解したら、さっそく、実際に動作を確認してみましょう。

　GridViewTimestamp.aspxを2つのブラウザで開き、双方とも編集モードにしたうえで、順番に更新処理を行ってみましょう。すると、後から更新処理を行った方のブラウザでは、競合エラーが確かに発生することが確認できるはずです。

　また、レコードにNull値が含まれている場合にも更新が正しく行われることを、さらには、レコード削除時にエラーが発生しないことも確認してみてください。

INDEX
	Visual Studio 2005でいってみようDBプログラミング
	第12回　データベース・プログラミング 7つのヒント − 同時実行制御からASP.NET AJAXまで −
	1．Hint 1.競合の検出／Hint 2. 楽観的同時実行制御の問題点とその解決方法
	2．Hint 3. GridViewページング・ソート処理の高速化／Hint 4. 「ASP.NET AJAX」の利用
	3．Hint 5. UpdatePanelコントロールによる高速化／Hint 6. Timerコントロールによる更新
	4．Hint 7. データベース・キャッシングの活用

　

「Visual Studio 2005でいってみようDBプログラミング」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）