none
ストアドプロシージャの排他制御 RRS feed

  • 質問

  • はじめまして。

    SQL Server 2008 を利用しています。

    現在、ストアドプロシージャを使って、Selectで存在を確認したのち、Update もしくは Insert か判断し実行したいと考えています。

     

    ただ、Select と Update/Insert の間に他のプログラムなどから、レコードが挿入された場合、一意違反にならないかと心配しています。

    可能性としては当然低いと思います。

     

    そこで、Select から Update/Insert までの間はテーブルロックを掛けようとも考えましたが、あまりスマートではありません。

    アドバイスをよろしくお願いします。

    (Select ... Update / Insert ... の代わりに別のクエリを使用すれば達成できるのであれば、そちらでも構いません。)

     

    よろしくお願いします。

     

    2011年11月28日 11:50

回答

  • Mergeステートメントを利用されてみてはいかがでしょうか?

    (参考)

    MERGE ステートメント(UPSERT)

    http://d.hatena.ne.jp/matu_tak/20100124/1264361341

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

     


    • 編集済み trapemiya 2011年11月28日 12:26
    • 回答の候補に設定 山本春海 2011年12月6日 8:18
    • 回答としてマーク 山本春海 2011年12月15日 4:44
    2011年11月28日 12:24
  • なるほど、テーブル値コンストラクターが使えるのですね。この中のサンプル「D. MERGE ステートメントで複数の行を派生ソース テーブルとして指定する」によると

    MERGE INTO fuga.テーブル1 AS Target
    USING (VALUES (@ID, @名前)) AS Source (NewID, NewName)
    ON Target.ID = Source.NewID
    WHEN MATCHED THEN
      UPDATE SET 名前 = Source.NewName
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (ID, 名前) VALUES (NewID, NewName);
    

    と書けるわけですか。

    # ついNewIDとか書いてしまう。

    • 回答の候補に設定 山本春海 2011年12月6日 8:18
    • 回答としてマーク 山本春海 2011年12月15日 4:44
    2011年11月29日 1:43

すべての返信

  • Mergeステートメントを利用されてみてはいかがでしょうか?

    (参考)

    MERGE ステートメント(UPSERT)

    http://d.hatena.ne.jp/matu_tak/20100124/1264361341

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

     


    • 編集済み trapemiya 2011年11月28日 12:26
    • 回答の候補に設定 山本春海 2011年12月6日 8:18
    • 回答としてマーク 山本春海 2011年12月15日 4:44
    2011年11月28日 12:24
  • 特にロックを行うことがスマートではないとは思いませんが…。

    たとえば、対象テーブルが Key と Value で構成されるとして、存在しない Key1 に対して Value1 と Value2 の2つの呼び出しが平行した場合に、双方で Insert が発生して制約違反になるということですよね?

    この場合に、格納されたい値は何でしょうか? また、双方の呼び出しを成功したように見せたいのか、片一方を失敗したように見せたいのかにもよりますよね。

    ロックを取得する場合、どちらか一方の呼び出しがロックを取得することになり、その時の順序は(時間差がどれだけあるかにもよりますが)保証されないので保存される値が Value1 になるか Value2 になるか不明です。

    そういった実行結果をどうしたいかもあれば、アドバイスを得やすいかもしれません。

     

    2011年11月28日 23:50
  • MERGEステートメントは比較対象となるtable_sourceがテーブル形式をしている必要があり、これがちょっと面倒です。素直にロックした方がいいかなと思います。

    K. Takaokaさんへ

    ロック・排他制御は順序が保証されない(どちらかが失敗する)わけではありません。実行した順序が保たれる(どちらも実行する)機構です。
    そして格納されたい値は、後に実行したValue2です。質問者さんは存在を確認し、存在するなら後の値でUPDATEすると言われています。

    先に実行したValue1を格納したいのなら、存在確認せず無条件にINSERTすればいいだけです。(後から実行したValue2は制約違反で失敗する。)

    2011年11月29日 0:47
  • MERGEステートメントは比較対象となるtable_sourceがテーブル形式をしている必要があり、これがちょっと面倒です。素直にロックした方がいいかなと思います。

    select文を使えばそんなに面倒ではないと思いますよ。

    CREATE PROCEDURE fuga.ストアドプロシージャ1
         @ID       int,
        @名前      nvarchar(50)
    AS
    BEGIN
    
        SET NOCOUNT ON;
    
        merge into fuga.テーブル1 T
        using (select @ID) as D (ID) on T.ID = D.ID
        when matched then
                update set 名前 = @名前
        when not matched then
                insert (ID, 名前) values (@ID, @名前);
    
    END
    

    #上記のコードは空で書いているので動作未検証です。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2011年11月29日 1:20
  • なるほど、テーブル値コンストラクターが使えるのですね。この中のサンプル「D. MERGE ステートメントで複数の行を派生ソース テーブルとして指定する」によると

    MERGE INTO fuga.テーブル1 AS Target
    USING (VALUES (@ID, @名前)) AS Source (NewID, NewName)
    ON Target.ID = Source.NewID
    WHEN MATCHED THEN
      UPDATE SET 名前 = Source.NewName
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (ID, 名前) VALUES (NewID, NewName);
    

    と書けるわけですか。

    # ついNewIDとか書いてしまう。

    • 回答の候補に設定 山本春海 2011年12月6日 8:18
    • 回答としてマーク 山本春海 2011年12月15日 4:44
    2011年11月29日 1:43
  • なるほど、テーブル値コンストラクターが使えるのですね。この中のサンプル「D. MERGE ステートメントで複数の行を派生ソース テーブルとして指定する」によると

    いつもselect文で書いていたので気が付きませんでしたが、こっちの方がスマートですね。勉強になりました。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2011年11月29日 2:14
  • > ロック・排他制御は順序が保証されない(どちらかが失敗する)わけではありません。
    > 実行した順序が保たれる(どちらも実行する)機構です。

    ロックは実行順序は保証されませんよ。(そもそも、同時にロックしようとしているものに対して順序なんてものはないのですが) 同時に実行されようとした2つの処理を順番に実行できるだけで、どちらが先になるかは不明です。

    > 質問者さんは存在を確認し、存在するなら後の値でUPDATEすると言われています。

    それが確認できない場合にどうすればよいか?という質問ですよね。

    Key=1, Value=10 という呼び出しと Key=1, Value=20 という呼び出しが、Key1 が存在しない場合に同時に発生してしまった場合、質問者は Value に対して 10 と 20 のどちらが格納したいのか? が見えないと適切な回答は得られませんよ、ということです。

    テーブルをロックした場合、この2つの処理の順序は保証されないので、10 または 20 が格納されて UPDATE によって他方へと変更されますが、結果として格納されている Value が 10 になるか 20 になるかはわかりません。(最初の投稿に書いているように、概ね時間差で決まりますが)

    仕様として累積していくような値であるため、順不動で格納できるものであるならば問題にはならないでしょう。データベースの更新は、動くかどうかではなく正しいデータが格納されているかどうかが重要かと思います。

    (MERGE がないころは、JOIN した結果を UPDATE して INSERT と同等の結果を得る…とかあった気はします)

    2011年11月29日 2:50
  • ロックは実行順序は保証されませんよ。
    ロックは先に実行した側が取得できると考えていました。後から実行した側が先に取得できる場合があるのでしょうか?
    (そもそも、同時にロックしようとしているものに対して順序なんてものはないのですが) 同時に実行されようとした2つの処理を順番に実行できるだけで、どちらが先になるかは不明です。
    完全に同時(CPUクロックレベルで同時)の場合、元々どちらも正しい値なのでどちらが格納されようが構わないと思います。
    # この点については私の勝手な判断で質問では言及されていませんでした。 
    2011年11月29日 4:22
  • ロックは実行順序は保証されませんよ。
    ロックは先に実行した側が取得できると考えていました。後から実行した側が先に取得できる場合があるのでしょうか?

    SQL Server の各ロックがどのようなアルゴリズムや実装を用いているかは不明です。

    たとえば Windows の OS が提供する一般的なカーネルリソースに対するロックは、シングルコア環境では FIFO (取得を試みた順に解決される)なのですが、2コア以上の状態で複数のスレッドからアクセスされるとスピンなどの仕組みで違う順序で取得されることが多々あります。こういった実装依存の不確かな部分に実行順序を求めた実装を行うのは間違いかと思います。

     

    2011年11月29日 4:49
  • trapemiya さん、K. Takaoka さん、佐祐理さん

     

    お返事ありがとうございます。

    MERGEやロックの考えなど、大変勉強になりました。

     

    今回、私のケースでは、Select を行ったクライアントがその後、Update / Insert で一意違反にならないことが重要でしたので、

    MERGEを利用すれば解決しそうです。

     

    横道で申し訳ないのですが、http://msdn.microsoft.com/ja-jp/library/bb510625.aspx にある、merge_hint ですが、

    これで rowlock を指定した場合、Update / Insert のときにロックがかかるのでしょうか?

    ex) MERGE INTO hoge.table1 with(rowlock) as Target

     

     

    よろしくお願いします。

    2011年11月29日 10:24