WebAPIの更新メソッド追加で既存アプリ更新メソッドと競合が起こらない記述方法

Question

既存アプリはデータベースへ更新機能をもっていまして、WEBAPI追加実装の場合、既存アプリ側で行う更新とWEBAPIによって呼ばれて実行された更新などでほぼ同タイミングで行われ、競合が起きないようにするにはどうしたらよいのでしょうか。

既存アプリ
クライアント側　　　→　　更新　　→　データベース

追加プロジェクト
WEBAPI　　　　　　→　　　更新　　→　データベース

実装資料など今いろいろ見ていますが、もともとのメソッドを囲い込むようにWEBAPIの実装を行う記述がなく、（新規実装を例にしているためだと思いますが）、別にWEBAPI側として検索更新メソッドを準備しているように見えました。

それとも既存アプリに対して新規でWEBAPI用のクラスを作り待ち受けるのではなく、まったく新規に作り変えていかなければいけないということなのでしょうか。

トランザクションの処理など入れて競合が起きないような実装をするには、どのような記述を行っていったらよいのでしょうか。

例として環境面は以下になります。

既存アプリ
windows7
VB.NET　　sqlSerever
.NET Framework 4.6
WebAPI ASP.NET

既存アプリの処理

　　　　　　　～一部省略～
            SqlConnectionStringBuilder dbConString = new SqlConnectionStringBuilder();

            dbConString.UserID = "●●●●●●";
            dbConString.Password = "●●●●●●●";
            dbConString.DataSource = "192.168.●●●.●●●,●●●●";
            dbConString.InitialCatalog = "TestDB";

            sql = "UPDATE USER_MASTER　SET  DEPT_NO = '0004'　WHERE  USER_ID = '0001' ";

            SqlConnection con = new SqlConnection(dbConString.ConnectionString);
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(sql, con);
                sqlReader = command.ExecuteReader();
　　　　　　　～一部省略～

追加プロジェクト

public class CustomersController : ApiController
{
　　public HttpResponseMessage PutCustomer(int id,Customer customer)
　　{
　　　　　　～一部省略～
　　　　　　 SqlConnectionStringBuilder dbConString = new SqlConnectionStringBuilder();

            dbConString.UserID = "●●●●●●";
            dbConString.Password = "●●●●●●●";
            dbConString.DataSource = "192.168.●●●.●●●,●●●●";
            dbConString.InitialCatalog = "TestDB";

            sql = "UPDATE USER_MASTER　SET  DEPT_NO = customer　WHERE  USER_ID = id ";

            SqlConnection con = new SqlConnection(dbConString.ConnectionString);
            con.Open();
 　　　　   // Start a local transaction.
　　　　    SqlTransaction sqlTran = connection.BeginTransaction();

　　　　    // Enlist a command in the current transaction.
　　　　    SqlCommand command = connection.CreateCommand();
　　　　    command.Transaction = sqlTran;
　　　　    try
　　　　    {
                SqlCommand command = new SqlCommand(sql, con);
                sqlReader = command.ExecuteReader();
　　　　　   }catch (Exception ex)
    　　　　　{
        　　　　　// Handle the exception if the transaction fails to commit.
        　　　　　Console.WriteLine(ex.Message);

        　　　　　try
        　　　　　{
            　　　　// Attempt to roll back the transaction.
            　　　　sqlTran.Rollback();
        　　　　　}
        　　　　　　catch (Exception exRollback)
        　　　　　　{
            　　　　　// Throws an InvalidOperationException if the connection 
            　　　　　// is closed or the transaction has already been rolled 
            　　　　　// back on the server.
          　  　　　　Console.WriteLine(exRollback.Message);
        　　　　　　}
    　　　　　　}
　　　　　　　～一部省略～

　　　　　　return Request.CreateResponse(HttpStatusCode,OK);
　　　}
}

以下実行
http://~/api/customers/?id=0001&customer=0004

そもそも基本的なことに立ち返りますが、既存側と追加WEBAPIプロジェクト側でトランザクション処理を行っていさえすれば、競合起こった場合などしっかりロールバックを行い、心配いらないということなのでしょうか。
もしそうなのでしたらお恥ずかしい限りですが、よろしくお願い致します。

Answer 1

既存アプリだけだったときもそういう問題は起こり得たはずですが？ web アプリですから複数ユーザーが同時にアクセスして DB を更新できますから。

そういう時は同時実行制御というのをかけます。

楽観的と悲観的という 2 種類が一般的で、ほとんど起こらない場合は前者、可能性が高く厳重に制御したい場合は後者の手段を用います。

それらについて疑問がある場合は聞いてください。(できれば、ある程度ググるなどしてご自分で調べた上で)

【追伸】

ひょっとして「既存アプリ」は Web アプリではなくて、Windows Forms アプリとかで、ある特定の時間内には決まった一人だけが使うもので、同時実行違反は起こり得ないものですか？　（もしそうならそうと質問の最初に書いておいてほしかったです）

そうであったとすると、Web アプリ追加後は、例えば楽観的同時実行制御を行う場合、「既存アプリ」を書き直してその機能を実装する必要があります。

Web アプリの方にも、もちろん楽観的同時実行制御の機能を実装する必要があります。

Windows Forms, ASP.NET Web Froms アプリの場合は、Visual Studio のウィザードで楽観的同時実行制御の機能を実装できますので、参考にそれを調べてみてはいかがですか？

ちなみに、トランザクションの件ですが、Windows Forms アプリの場合は、Visual Studio 2008 以降で TableAdapterManager を利用する場合は、ウィザードで自動的にその機能が実装されます。

Comments

[dendenmushi]

>SurferOnWwwさん
回答ありがとうございます。
『楽観的と悲観的という 2 種類が一般的で、ほとんど起こらない場合は前者、可能性が高く厳重に制御したい場合は後者の手段を用います』
悲観的同時実行制御を行おうと考えています。私の中で3パターンありまして、
1）
もともとの既存アプリが同時実行制御じたい行う必要性（場面）がなく行っていなかった場合に、
追加WEBAPI側に悲観的同時実行制御を行う。

この場合は、既存アプリ側のプロジェクトの改修が必要になる。
2)
もともとの既存アプリが楽観的同時実行制御を行っていた場合に、
追加WEBAPI側に悲観的同時実行制御を行う。

この場合は、既存アプリ側のプロジェクトの改修が必要になる。
3)
もともとの既存アプリが悲観的同時実行制御を行っていた場合に、
追加WEBAPI側に悲観的同時実行制御を行う。

この場合は、既存アプリ側のプロジェクトの改修が不要になる。

既存アプリについてはVB.NETでasmx拡張子によるプロジェクトでsqlServerへの接続を行っていました。いろいろと関連情報について調べさせて頂けました。ありがとうございました。もしよろしければ、両方で悲観的同時実行制御の参考となるコード資料など教授頂けるサイトなど教えて頂けないでしょうか。よろしくお願い致します。

[退会済みユーザー]

どこか勘違いがあって簡単に考えすぎているような気がしますが、それはちょっと置いといて・・・

> 両方で悲観的同時実行制御の参考となるコード資料など教授頂けるサイトなど教えて頂けないでしょうか。

「Microsoft Visual Studio 2005 による Web アプリケーション構築技法」という本（アマゾンなどで検索してみてください）の「第 12 章 業務排他制御による対話型トランザクション処理の開発」というセクションに詳細な記述があるので読んでください。

質問者さんの言う「両方」ではなくて Web アプリの例で、そのまま質問者さんのケースには適用できないでしょうが、勉強になると思います。

[dendenmushi]

まず質問の時点でWindowsホームアプリで利用者は数人おり、クラサバでしたが、余り同時実行という場面はないという状況でした。お伝え不足申し訳ありませんでした。

あと間違えました。楽観的制御の方で充分なのでそちらで制御していこうと思います。

既存の方はかなり昔に作りましてサーバープロジェクトとしてIISを介してsqlserverに繋いでおります。
そこのasmxファイルに楽観的同時実行制御のトランザクションを追記し、追加WebAPI側にも同様の実装を行おうと思います。

ただ言われている通り、これから作るWebAPI側は作る際は、TableAdapterManagerを利用すれば、その実装が行えるとのことでそのコードを参考に既存の方も実装していこうと思っています。
ありがとうございます。早速その書籍も見てみようと思っています。

[退会済みユーザー]

> これから作るWebAPI側は作る際は、TableAdapterManagerを利用すれば

回答をきちんと読んでもらってない気がしますが・・・

回答には、

"トランザクションの件ですが、Windows Forms アプリの場合は、Visual Studio 2008 以降で TableAdapterManager を利用する場合は、ウィザードで自動的にその機能が実装されます"

と書きました。TableAdapterManager が使えるのは Windows Forms アプリの場合です。Web アプリには使えません。

紹介した書籍には Web アプリでの楽観的同時実行制御のことも詳しく書いてあるので、本屋にあれば立ち読みとかして、気に入ったら買って読んでみてはいかがですか？

[dendenmushi]

ありがとうございます。Windowsフォームアプリの場合ではその方法参考にし、Webアプリは書籍見てみます。
感覚的には今ある接続実装に記述を2,3列増やすだけのイメージですが、聞いているとタイムスタンプなどフィールド追加するなどDBじたいも変えなければいけないということのような気がして少し心配しています。書籍は購入してしまおうと思っています。

[退会済みユーザー]

楽観的同時実行制御で良いなら既存の DB には手を加える必要はありません。それがメリットの一つでもあります。(timestamp 列を追加できるならその方が面倒がないってことはありますが)

windows forms アプリは Visual Studio のウィザードを使って、型付 DataSet + TableAdapter + TableAdapterManager を自動生成させて、それを利用することをお勧めします。楽観的同時実行制御に必要なコードも自動生成されますので。

Answer 2

そもそも基本的なことに立ち返りますが、既存側と追加WEBAPIプロジェクト側でトランザクション処理を行っていさえすれば、競合起こった場合などしっかりロールバックを行い、心配いらないということなのでしょうか。

--誤解を招く表現だったので修正/追記--
ご質問にある「競合」がどこまでを指しているか次第ではありますが、
トランザクション機能を備えたRDBMSを使用したシステムの場合で
同時アクセスが想定されるケースの場合、
一般的にはトランザクション分離レベルを適切に設定し、
クライアント側（既存アプリ、web-API）でトランザクション処理を行うことで、
ロックの取得及び問題発生（RDBMSの把握する問題及び、アプリケーションが問題と判断する場合）時にロールバックを行う事が可能です。

適切なトランザクション分離レベルや、トランザクションに含めるべき範囲、
そもそもRDBMSの機能で実現可能なのかの判断については何をもって「競合」とするかの定義次第になるので、
まずはどういった競合が発生し、
競合発生時にアプリケーションの仕様として正しい挙動はどういうものかを定義するところからアプローチするところからスタートかなと思います。
（例えば、ほぼ同時に更新があった場合、後から更新した方はエラーが発生してほしいのか、先行の処理終了後のデータを元に処理を実施してほしいのかetc...）

「今回抜粋されたソースコードの部分だけ」を見ると、最低限、既存アプリを追加プロジェクトと同じ粒度・処理のトランザクションをかけるように処理を追加する必要があるように見えます。

---

完全に別のアプローチとして
追加プロジェクトの方は元々webアプリケーションとして開発されている（=同時アクセスに対する競合防止処理が実装されている）事が保証されているのであれば、既存アプリから直接DBにアクセスするのをやめて、全て追加プロジェクトのweb-API経由でデータ更新を行うように修正するというのも一つの手だと思います。
オーバーヘッドが増えてパフォーマンス的には不利になりますし、セキュリティ的に考えることが増え、既存アプリの作りによってはほぼ作り直しのような状態になる可能性があると思いますが、
同じ処理が二つの処理系にそれぞれ記述されているという永久に続く二重メンテナンスから解放されるという大きなメリットがあるので、個人的には好きな方法です。

Comments

[退会済みユーザー]

ASP.NET アプリでその方法は一般的なのでしょうか？ ASP.NET アプリでの適用例を紹介する記事などがありましたら教えていただけると幸いです。

[tanat]

> SurferOnWwwさん
すみません、「一般的に」と書いたのは、ASP.NETアプリに限らず
一般的にRDBMSのサーバとクライアントの役割分担の話として今回のケースだとトランザクション処理を行えば回避できるケースを指して「一般的」と記述しました。
*記述としては確かに微妙なので修正します。

トランザクション分離レベルを設定し、アプリ側でトランザクション処理を行うのははSurferOnWwwさんのご回答にある同時実行制御の一つの方法だと認識していますが、この点に認識の違いがあればご教示頂ければ幸いです。

[退会済みユーザー]

何を「一般的」というかは議論の分かれるところと思いますが、Visual Studio のデザイナ・ウィザードに自動生成できるようなメニューがある場合は「一般的」と言っていいと思いますので、それを例にとります。

ASP.NET Web Forms アプリの場合、Visual Studio のデザイナ・ウィザードを使って DB を更新するアプリを作ると、一回の操作で一レコードのみしか更新できないコードになります。

それに楽観的同時実行制御を追加すると UPDATE, DELETE クエリの WHERE 句にその機能が追加され、同時実行の場合 WHERE 句の条件が満たされず更新はかかりませんん。

なのでトランザクションに束ねて失敗した場合はロールバックするという操作は不要で、実際、自動生成されるコードにもトランザクションは含まれていません。

悲観的同時実行制御の場合は、ウィザードベースでは実装できず、自力でコードを書いて実装することになりますので、実装次第ですが、楽観的同時実行制御と同様に一回の操作で一レコード＆失敗した場合は更新がかからないようにすれば、トランザクションに束ねてというのは不要になると思います。

[dendenmushi]

>tanatさん
『はい。一般的にはトランザクション分離レベルを適切に設定し、クライアント側（既存アプリ、web-API）でトランザクション処理を行えば競合は回避できます。』

回答ありがとうございます。sqlServerでトランザクション分離レベルREAD COMMITTEDでファジーリードが起こりうる状態、既存アプリ側と追加WEBAPIプロジェクト側で悲観的トランザクションを行おうと思います。ありがとうございます。

[dendenmushi]

>tanatさん

回答追記してくれたのですね。ありがとうございます。
はい。競合の定義ですが、更新を行った際に、『別の方が更新中です。（実際はこの文言ではないですが）』というように、再度処理を（ボタン押すだけ）行って頂くようにしようと考えています。

別のアプローチ方法も私も考えていましたが、今回の回収追加箇所をなるべく少なくするというの考えがありますので、おそらく追加プロジェクトとして既存アプリをあまりいじらずにという方向性になるかと思います。ありがとうございます。

Answer 3

更新系に関しては他の回答者様が回答してらっしゃるので、別の点で回答。

1,まずSQL SERVERの場合、SELECTでもロックのエスカレーションが発生したら参照ロックがかかっちゃいますがその点は大丈夫なのでしょうか。

2,SqlConnection、SqlTransaction、SqlCommand この3クラスはユーザー（開発者に）直接使用させないでください。
必ず上記クラスの操作をラッピングしたクラスを作成して使用させてください。
コネクションタイムアウト、トランザクションタイムアウト、発行したSQLをログ出力などなどあとでデータベース・アクセス部分に対して、一括設定する物が多いです。

3, SqlConnection、SqlTransaction、SqlCommand はIDisposableを実装しているので、usingで囲ってください。

4,command.ExecuteReaderでUPDATE文を発行してますが、command.ExecuteNonQuery ();です。

5,この箇所SqlCommand command = new SqlCommand(sql, con);してますが、 command.CommandText = sql;で済む話ではないでしょうか。

　　　　    SqlCommand command = connection.CreateCommand();
　　　　    command.Transaction = sqlTran;
　　　　    try
　　　　    {
                SqlCommand command = new SqlCommand(sql, con);
                sqlReader = command.ExecuteReader();

6,Console.WriteLine(exRollback.Message); WEB APIなのでConsoleに出力するより、ファイルにログ情報を出力やメール出力などを行ったほうがいいです。

7,番号を発行する処理に関してはDBのストアドプロシージャにして、それをAP側で呼び出す形にしたほうがあと後の事を考えるといいと思います。

8,SQLに値を設定する時はSqlParameterクラスを最低でも使用してください。

---

2017/11/19追記
1,更新要件によるのですが、例えば標準単価マスタ(例）の値を参照(SELECT)して、明細（トランザクションデータ）にUPDATEする時です。
WEBAPIとのことなので、SQLトランザクションの時間がそこまで長くなるとはあまり思えませんが、念のための情報です。

2,まず開発の要件定義/要求仕様とCustomersController側がどういうSQLアクセスが必要かによるのですが、疑似コードを書きます。

◆SqlConnectionを生成しオプション値を設定するためだけのクラス。

using System.Collections.Generic;
class DBHelper {

  private readonly string ConnectionString;
  private readonly string id;
  private readonly int ConnectionTimeout;
  
  public DBHelper(string id) {
    this.id = id;
    //SqlConnectionStringBuilderを使ったConnectionString作成処理
    this.ConnectionString = dbConString.ConnectionString;
    //設定ファイル等からtimeout値を格納
    this.ConnectionTimeout = 1000;
  }
  public SqlConnection getSqlConnection() {
    SqlConnection con = new SqlConnection(this.ConnectionString);
    con.ConnectionTimeout = this.ConnectionTimeout;
    return con;
  }
}

◆CustomersController#PutCustomer側

DBHelper db = new DBHelper("exsample");
using(SqlConnection con = db.getSqlConnection()) {
	//やりたいこと。
};

これでConnectionString作成処理とConnectionTimeoutの設定処理はDBHelper側で管理して、CustomersController#PutCustomer側は関知しないという形に変更できます。
関知しないというのがポイントです、PutCustomer側が主として行いたい事はデーターベースの更新処理です。ConnectionString、ConnectionTimeoutの設定ではありません。
getSqlCommandやgetSqlTransactionを追加することで、実行するSQLをファイルに書き出したりもできるようになります。
DBHelperに渡すID値の管理はSQLトランザクションの要件（コネクションタイムアウト値、トランザクションタイムアウト値、トランザクション分離レベル(isolation level)）によって異なるので、それに合わせた形がいいと思います。
windows formならリフレクションで実行のメソッド名が取れた記憶がありますが。
難易度が上がりますが、TransactionScopeクラス、拡張メソッドを使った書き方やイベントを使った書き方もあるので、時間がある時に試行錯誤してみてくださいな。

3,using SqlConnectionで検索してみてください。

using (SqlConnection con = new SqlConnection(dbConString.ConnectionString)){
	// 行いたい処理

}

SqlTransactionはusingで囲むとデフォルト処理がrollbackなので注意です。
コミット処理 sqlTran.Commit();を入れてください。

7，まず「ストアドプロシージャ 利点　欠点」で検索してみてください。
番号を発行する処理はSQLサーバーのsequence(シーケンスオブジェクト)に任せた方がいいのですが。
質問文のソースコードの書き方を見る限りでは使ってなさそうに見えたので。

◆取引番号を付番する処理を考えた時、
1,番号を発行し、その値を使用する。
1-1,番号発行.
1-2,発行された番号をプログラムで使用。

2,発行されている番号を使用し、次の番号を発行する。
2-1,発行されている番号をプログラムで使用。
2-2,次の番号を予約発行.

この2パターンがあります、設計者、開発者間で確実に周知されている必要があり、開発規模によっては容易に伝達ミスが発生します。
一般的に発行した番号は会社外部に出ることが多く番号が間違ってた時や管理が大変です。
この点をストアドプロシージャを使うことでDB側で付番を管理し統一した形で処理が管理を行えます。

8 ，質問文の sql = "UPDATE USER_MASTER　SET DEPT_NO = '0004'　WHERE USER_ID = '0001' ";
UPDATE文でDEPT_NOとUSER_ID の値の割当を行ってますが、この部分の話です。
追加プロジェクトはPutCustomerの引数から割り当てる方なりますが、文字列連結 + でSQLを組み立てるとSQLインジェクションが発生します。
SQLインジェクションを防止するために「SqlParameter 使い方」で検索してみてください。

Comments

[dendenmushi]

ありがとうございます。今長文のお礼と質問合わせて書いたのですが消えてしまいました…帰宅後に落ち着いて再度書きます…。

[dendenmushi]

詳細なアドバイスありがとうございます。
１については、READ_COMMITTED_SNAPSHOT オプションがONにすれば解決できるのではないでしょうか。参照ロックという言葉の意味合いの認識の齟齬があるとしたらすいません。
また、エスカレーションについては、対象行がある表をまるごとロックする方が効率的と判断するsqlserverの判断基準について私が理解できておらず、実際どのようなリスクがあるのかイメージが実際わかないのが実情です。

6について私も想定しておりました。ありがとうございます。
4，5ご指摘ありがとうございます。

2、3について申し訳ないです。意味はある程度解釈できたのですが実際にどのようなコード記載にするか実現するには何か書籍か参考サイトなどあればできぞうです。もしよろしければ教えて下さい。

7，8　番号を発行するとき、SQLに値を設定するときについて勉強不足でどのような状況か理解できませんでした。もしよければ参考資料など書籍かサイトなど教えて頂けないでしょうか。
よろしくお願い致します。

[dendenmushi]

みなさんの回答に感謝いたします。どれも参考になりベストアンサーを選ぶのが心苦しいですが、詳細内容書いて頂いたこともあり、こちらにさせて頂きたいと思ったのもありますが、更新系の質問であったこともあり、もう一人の方に今回させて下さい。一番詳しく、こちらの質問も非常にアドバイスとして勉強させて頂きました。ありがとうございました。実装途中また万が一お聞きしてしまうことがあるかもしれませんが、どうぞよろしくお願い致します。