none
1つのトランザクション内で対象の更新SQLを実行すると、データが更新されない。 RRS feed

  • 質問

  • いつもお世話になっております。
    SQL Server 2008、Windows Server 2008、Visual Studio 2008の環境で、
    業務アプリケーションを開発しています。

    あるプログラムの中で、
    同一トランザクションの中で、1万回を超える更新SQLを実行しています。
    (対象が1万行なのではなく、更新SQLが1万回を超えているのです。)

    その場合に、Exceptionも発生せずロールバックもしていないにも関わらず、
    更新がされないという現象が発生しています。

    更新SQLの際に利用しているExecuteNonQueryの戻り値(対象となった行数が戻り値として得られます。)は、
    「35」とか「103」などと戻ってきており、SQLの間違いによって更新対象がなかった
    ということはなさそうです。

    Windows Server 2008のメモリーは4GB用意されており、
    問題のトランザクションを実行したときにも、2.5GB程度の使用状況です。
    メモリー不足ということもなさそうです。

    更新SQLが少なくなるようにデータを調整して、
    再度同一のプログラムを実行すると正しく更新がされます。
    このことから1トランザクション内で実行しようとしている
    更新SQLの回数やその際に使用されるメモリー量などが影響しているのではないか
    と推測しています。

    しかし、ここで私の調査は手詰まりとなってしまいました。

    そこで質問なのですが、SQL Server 2008の設定で、
    トランザクション数の制限や、1トランザクションで利用できるメモリー量を制限しているのでしょうか?
    もしくは、.NETのプログラムからSQL Serverに接続する際に、
    今回の現象に関係するような何か設定が必要なのでしょうか?

    1トランザクション内で、1万回以上の更新SQLをなげなければならない
    設計をしてしまったことが、そもそもの問題なのかもしれませんが、
    SQL Serverなどの設定変更によって、この問題を回避したいと思っております。

    もし何か回避策などをご存知でしたら、ご教示頂けますと
    非常に助かります。

    ぜひよろしくお願い致します。

    2010年10月3日 5:26

回答

  •  あくまで可能性の話ですが…。

     何かの要因でトランザクションがネストして、ネストしたトランザクションに対してCOMMITされていないということはないでしょうか?
     例えば

    BEGIN TRANSACTION
    INSET INTO table VALUES ('foo')
    BEGIN TRANSACTION
    INSET INTO table VALUES ('bar')
    COMMIT TRANSACTION

    のようにBEGIN TRANSACTIONとCOMMIT TRANSACTIONが対になっていない場合、barをINSERTするトランザクションは成功しますが、'foo'をINSERTするトランザクションは未完のままとなってしまいます。

     トランザクションの入れ子の数は、@@TANCOUNT関数に保存されていますので、デバッグ時に@@TRANCOUNTの値を監視してみてはどうでしょうか?


     ちなみに、夜間処理で何万件も更新するトランザクションを実行している事例もありますので(しかも昔のサーバーですので、現在よりメモリー等は少ない)、SQL Serverのメモリーや設定に関してはあまり問題はないかと思います。
     あと、これだけの件数を一トランザクション内で処理しているため、メモリーというよりはトランザクションログファイルのサイズが足りていれば問題ないかと思われます。(メモリーが足りなくなれば、仮想メモリーもありますし)

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月5日 4:23
  •  これも可能性の話ですが、Webサービス、もしくはデータベースへのアクセスでタイムアウトが起きているという事はないでしょうか?
     

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月7日 7:11
  • >【確認方法について】

    すみません、完全に成功しているにもかかわらず「更新されていない」という判断に至った理由は何でしょうか?という意味です。

    おそらく、処理後に期待している値があるはずで、そうなっていないという事ではないでしょうか。例えば、ある列のあるレコードが「10」になっているはずが「5」になっている、などです。


    >【分離レベルについて】

    READ COMMITTED は、文字通り「コミット後の値を読み取る」という事を保証はしますが、例えば反復読み取りは保証しません。

    例えばコードが、
    ---------------------------------------------
    トランザクション開始
    for(int i = 0; i <= 10000; i++)
    {
    「テーブル1から特定行のSELECT」--- (1)
    「なにかの処理」
    「テーブル1から特定行のSELECT」--- (2)
    「読み取った値を元にUPDATE」--- (2)
    }
    トランザクションコミット
    ---------------------------------------------
    だとします。READ COMMITTED していない場合、 (1)と(2)が同一レコードを読み取っても値が同じは限りませんよね?

    また、分離レベルだけでなく UPDATE LOCK するべき箇所で、そうしていないかもしれません。

    上記理由によりデータに矛盾が生じて、「更新されていない」という判断になっている可能性もあります。
    • 回答としてマーク コンドル 2010年10月19日 13:11
    2010年10月3日 12:31
  • SELECTもトランザクションに参加していれば、さすがに最新値を読みだせるのでは?

    勝手な想像ですが、コンドルさんのプログラムはトランザクションが1本しかなく、トランザクション外の操作もないような。なので分離レベルはオーバーヘッドの差はあれど、トランザクション内=プログラム見えに違いはないかな。あくまで勝手な想像です。

    それからコンドルさんへ
    Visual Studioを使ってデバッグとありますが、デバッグ対象はSQLでなく.NETプログラムですよね? それは.NETプログラムが正しいことを前提にした確認方法で、プログラムが間違っていたら何の意味もありません。
    具体的には上の勝手な想像の通りだとすると、例えば意図せずトランザクション外でSELECTを実行していた、とか。
    そういった場合、T-Yokooさんの指摘しているような、分離レベルによって取得できるレコードの値が変わってきます。

    • 回答としてマーク コンドル 2010年10月19日 13:11
    2010年10月3日 22:37
  • 少なくとも3段階
    SQL(Server)
    プログラム
    WEBサービス
    のどこか、に的を絞った方が良いのでは。


    まずはSQL(Server)
    単体でUPDATE1万回実行してみてはどうでしょう。

    実行するSQLは、今のプログラムから吐き出せますよね。
    SQLを実行せずに、ファイル出力する等。
    (同じ意味のSQLを手作りしてもいいでしょう)

    それをストアド(かSSMS上)でTRAN等をいれる。
    ループ等のロジックは入れない、と。
    て一気に実行。

    これでうまくいけば、SQL(Server)の問題ではなく、プログラム以降の問題かと。

    うまくいかなければ、SQL Serverよりも、まずは
    SQL(条件や分離レベルも含む)を疑ったほうが良いかもしれません。
    シンプルにしていけば問題がなくなるポイントが見つかるかも、です。

    ご存知かと思いますが、念のため、分離レベル等の設定と動きも
    どのときにどうなるか、を今一度確認しておいた方がいいかもしれません。

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月13日 13:52

すべての返信

  • >その場合に、Exceptionも発生せずロールバックもしていないにも関わらず、
    >更新がされないという現象が発生しています。

    上記はどのように確認したのでしょうか?

    データに矛盾が生じているということなら、もしかしたら分離レベルが弱く、他のトランザクションが更新結果を上書きしてしまっているのでは?

    どのような分離レベルで、どのようなSQLを書いているのでしょうか?
    2010年10月3日 7:17
  • T-Yokoo様

    ご回答ありがとうございます。
    回答させて頂きます。

    【確認方法について】
    確認方法は、2つの方法で行いました。
    1つ目はVisual Studioでデバッグしながら、確認しました。
    Exceptionが発生していれば、プログラムが止まりますが、
    それが確認されることはなかったです。

    またロールバックのほうは、ロールバックのプログラムが書かれている部分に
    ブレークポイントを設定して、当該プログラムが動作していないことを確認しました。


    2つ目は、プログラムからログ出力をして確認しました。
    Exceptionは1か所でCatchしてログ出力しています。
    そのログが出力されていなかったため、Exceptionは発生していないと判断しました。

    またロールバックのプログラムの前後にもログ出力するように
    プログラムしまして、ロールバックが行われていないことを確認しました。


    【分離レベルについて】
    分離レベルは「READ COMMITTED」を使用しています。
    そのため、他のトランザクションが更新結果を上書きしていると
    いうことは考えにくいです。

    念のため、他のプログラムなどによって
    トランザクションが発生しないように、ローカルに環境を用意して、
    同じプログラムを動作させてみましたが結果は同じでした。


    【書いているSQLについて】
    SELECT文、INSERT文、UPDATE文という一般的なDMLしか使っていません。
    SQLの実行件数こそ多いですが、SQL自体は特別なSQLを実行していないと
    思っています。


    何か他にも考えられることがございましたら、
    教えて頂けましたら、非常に助かります。

    ぜひよろしくお願い致します。

    2010年10月3日 11:47
  • >【確認方法について】

    すみません、完全に成功しているにもかかわらず「更新されていない」という判断に至った理由は何でしょうか?という意味です。

    おそらく、処理後に期待している値があるはずで、そうなっていないという事ではないでしょうか。例えば、ある列のあるレコードが「10」になっているはずが「5」になっている、などです。


    >【分離レベルについて】

    READ COMMITTED は、文字通り「コミット後の値を読み取る」という事を保証はしますが、例えば反復読み取りは保証しません。

    例えばコードが、
    ---------------------------------------------
    トランザクション開始
    for(int i = 0; i <= 10000; i++)
    {
    「テーブル1から特定行のSELECT」--- (1)
    「なにかの処理」
    「テーブル1から特定行のSELECT」--- (2)
    「読み取った値を元にUPDATE」--- (2)
    }
    トランザクションコミット
    ---------------------------------------------
    だとします。READ COMMITTED していない場合、 (1)と(2)が同一レコードを読み取っても値が同じは限りませんよね?

    また、分離レベルだけでなく UPDATE LOCK するべき箇所で、そうしていないかもしれません。

    上記理由によりデータに矛盾が生じて、「更新されていない」という判断になっている可能性もあります。
    • 回答としてマーク コンドル 2010年10月19日 13:11
    2010年10月3日 12:31
  • SELECTもトランザクションに参加していれば、さすがに最新値を読みだせるのでは?

    勝手な想像ですが、コンドルさんのプログラムはトランザクションが1本しかなく、トランザクション外の操作もないような。なので分離レベルはオーバーヘッドの差はあれど、トランザクション内=プログラム見えに違いはないかな。あくまで勝手な想像です。

    それからコンドルさんへ
    Visual Studioを使ってデバッグとありますが、デバッグ対象はSQLでなく.NETプログラムですよね? それは.NETプログラムが正しいことを前提にした確認方法で、プログラムが間違っていたら何の意味もありません。
    具体的には上の勝手な想像の通りだとすると、例えば意図せずトランザクション外でSELECTを実行していた、とか。
    そういった場合、T-Yokooさんの指摘しているような、分離レベルによって取得できるレコードの値が変わってきます。

    • 回答としてマーク コンドル 2010年10月19日 13:11
    2010年10月3日 22:37
  • >SELECTもトランザクションに参加していれば、さすがに最新値を読みだせるのでは?

    最新値を読み出してはいけない場合もあるはずですよね?そのために REPEATABLE READ 等があるのですから。
    2010年10月4日 3:20
  • > すみません、完全に成功しているにもかかわらず「更新されていない」という判断に至った理由は何でしょうか?という意味です。
    >
    > おそらく、処理後に期待している値があるはずで、そうなっていないという事ではないでしょうか。例えば、ある列のあるレコードが「10」になっているはずが「5」になっている、などです。

    8テーブルに対してINSERT、UPDATEしているのですが、
    いずれのテーブルも全く更新されていないのです。

    この現象から確認する限り、トランザクションが何らかの理由で、
    ロールバックされているように思えます。
    しかしロールバックのメソッドは呼ばれていませんし、コミットのメソッドは呼ばれているのです。

    またこの処理の中でINSERTしているテーブルがあるのですが、
    このテーブルは、このトランザクション以外では変更されるはずのないテーブルなのです。


    > Visual Studioを使ってデバッグとありますが、デバッグ対象はSQLでなく.NETプログラムですよね? それは.NETプログラムが正しいことを前提にした確認方法で、プログラムが間違っていたら何の意味もありません。
    確かにそうですね。
    プロファイラなどでSQLを監視するべきだ、ということでしょうか?
    うまく理解できませんでしたので、もう少し詳しくご説明頂けませんか?

     


    微妙に数件更新されていないようだ。
    というレベルではなく、全く更新が反映されていない。。。
    という状態です。
    動きとしてはロールバックされているように思えます。


    何か考えられることがございましたら、
    教えて頂けますと、非常に助かります。
    ぜひよろしくお願い致します。

    2010年10月5日 2:34
  •  あくまで可能性の話ですが…。

     何かの要因でトランザクションがネストして、ネストしたトランザクションに対してCOMMITされていないということはないでしょうか?
     例えば

    BEGIN TRANSACTION
    INSET INTO table VALUES ('foo')
    BEGIN TRANSACTION
    INSET INTO table VALUES ('bar')
    COMMIT TRANSACTION

    のようにBEGIN TRANSACTIONとCOMMIT TRANSACTIONが対になっていない場合、barをINSERTするトランザクションは成功しますが、'foo'をINSERTするトランザクションは未完のままとなってしまいます。

     トランザクションの入れ子の数は、@@TANCOUNT関数に保存されていますので、デバッグ時に@@TRANCOUNTの値を監視してみてはどうでしょうか?


     ちなみに、夜間処理で何万件も更新するトランザクションを実行している事例もありますので(しかも昔のサーバーですので、現在よりメモリー等は少ない)、SQL Serverのメモリーや設定に関してはあまり問題はないかと思います。
     あと、これだけの件数を一トランザクション内で処理しているため、メモリーというよりはトランザクションログファイルのサイズが足りていれば問題ないかと思われます。(メモリーが足りなくなれば、仮想メモリーもありますし)

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月5日 4:23
  • CatTail様

    ご回答ありがとうございます。

    トランザクションがネストしていると、
    発生しますね。
    この考えはなかったので、非常に参考になりました。

    プログラムを確認する限り可能性が小さい気もしますが、
    藁にもすがる思いですので、@@TRANCOUNTの値は監視させて頂きます。

     

    解決の手がかりになればと思いますので、
    プログラムのことをもう少し詳しく説明させて頂きます。

    データベース処理はサーバーで行っており、
    Webサービスを利用しています。
    その中でWebMethodのTransactionOptionとして「RequiresNew」を指定することで
    トランザクション管理をしています。

    実際のプログラムからの抜粋↓(※変数名などは変更しています)

            [WebMethod(
                Description = "xxx",
                TransactionOption = TransactionOption.RequiresNew,
                EnableSession = false)]
            public int Regist(int yyy, string zzz)
            {
                try
                {
                    //省略
                    connection.Open();
                    //省略
                }
                catch (Exception e)
                {
                    // ロールバックしています。
                    ContextUtil.SetAbort();
                }
            }

    私にWebサービスを利用した経験が乏しいため、
    TransactionOptionを使ってトランザクション制御しているところが、
    問題ないのだろうかと、少し気になっています。

    TransactionOptionを使ったWebメソッドは他にも多く(300メソッド程度)利用しており、
    いずれも正しく動いているため、問題なさそうなのですが。。。
    (※Webメソッドの属性はいずれも同じにしています。)

     

    またテストを続けていると次のことが分かりました。
    ※テスト中は他のプログラムはすべて停止させてテストしました。

    データベースのバックアップを取り、
    同じデータ状態で、同じプログラムで問題の処理を何度も実行しました。
    12回検証して、うち2回は正常に登録されたのです。
    2回はすべてのテーブルが意図した更新がされています。
    残りの10回は、すべてのテーブルが全く更新されていませんでした。
    とても不思議に感じております。

    また失敗する場合に次の例外が発生することがありました。
    (多くの場合は、例外が発生せずに終了します。)
    ---------------------------------------------------------------------------------
    System.NullReferenceException
    オブジェクト参照がオブジェクト インスタンスに設定されていません。
    ---------------------------------------------------------------------------------

    データもプログラムも全く同じだと考えると、
    場合によってこの例外が発生する理由も不思議です。
    (当然データとプログラム以外にも関係する要因はあると思いますので、
     それが影響しているのだと思うのですが・・・)

     

    更に次のことも試してみました。
    プログラムを少し変えて、処理の途中でコミットしてみる。
    今までは10000件程度の更新SQLを実行したあとで最後にコミットしていたのですが、
    検証のために50件ごとにコミットするようにしてみました。
    (あくまで検証用です。)

    すると、必ずデータが更新されていました。
    プログラムを多少変更していますので、その影響もあるかもしれませんが、
    一見プログラムは正しいのかなと思ってしまうような結果が得られます。
    (もちろんトランザクションを分けてコミットしては問題になる理由があるため、
     困っているのですが。。。)

     

    教えて頂いた@@TRANCOUNTを中心に、
    調査を続けたいと思っております。

    可能性は小さくても構いませんので、何か考えられることがございましたら、
    教えて頂けましたら、非常に助かります。

    ぜひよろしくお願い致します。

     

    2010年10月7日 3:47
  •  これも可能性の話ですが、Webサービス、もしくはデータベースへのアクセスでタイムアウトが起きているという事はないでしょうか?
     

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月7日 7:11
  • CatTail様

    ご回答ありがとうございます。

    検証してみましたが、
    Webサービスのタイムアウト、データベースのタイムアウトは
    いずれも発生していないようです。

    どちらもタイムアウトの時間を30分に延ばしてみたのですが、
    変化はありませんでした。
    (そもそも1分程度で終わるはずの処理なんですよね。。。)

    @@TRANCOUNTの値の監視は、
    まだできておりません。
    すいません。。。
    様々なポイントに仕込んで、調査をしてみたいと思っております。

    ちょっとした問題なのだろうと思っているのですが、
    なかなか解決の糸口が見付からず困っております。

    また何か考えられることがございましたら、
    ご教示頂けますと、非常に助かります。
    引き続き、調査を続けてまいりますので、
    調査次第報告させて頂きます。

    よろしくお願い致します。

     

    2010年10月11日 13:21
  • 少なくとも3段階
    SQL(Server)
    プログラム
    WEBサービス
    のどこか、に的を絞った方が良いのでは。


    まずはSQL(Server)
    単体でUPDATE1万回実行してみてはどうでしょう。

    実行するSQLは、今のプログラムから吐き出せますよね。
    SQLを実行せずに、ファイル出力する等。
    (同じ意味のSQLを手作りしてもいいでしょう)

    それをストアド(かSSMS上)でTRAN等をいれる。
    ループ等のロジックは入れない、と。
    て一気に実行。

    これでうまくいけば、SQL(Server)の問題ではなく、プログラム以降の問題かと。

    うまくいかなければ、SQL Serverよりも、まずは
    SQL(条件や分離レベルも含む)を疑ったほうが良いかもしれません。
    シンプルにしていけば問題がなくなるポイントが見つかるかも、です。

    ご存知かと思いますが、念のため、分離レベル等の設定と動きも
    どのときにどうなるか、を今一度確認しておいた方がいいかもしれません。

    • 回答としてマーク コンドル 2010年10月19日 13:10
    2010年10月13日 13:52
  • pomodoro様

    ご回答ありがとうございました。

    > まずはSQL(Server)
    > 単体でUPDATE1万回実行してみてはどうでしょう。
    ちょっと大変だったのですが、
    試してみました。
    結果としては、問題なく成功しました。

    この結果からみても、SQL Server側には問題なさそうだと思います。

    > うまくいかなければ、SQL Serverよりも、まずは
    > SQL(条件や分離レベルも含む)を疑ったほうが良いかもしれません。
    > シンプルにしていけば問題がなくなるポイントが見つかるかも、です。
    確かにそうですね。
    トランザクションも3つに分けられないことがないので、
    分離してみることで、原因の特定につながるかもしれません。


    問題はプログラム側の可能性が高くなってきました。
    一度質問はクローズさせて頂きまして、必要であれば、
    Visual Studio側で質問させて頂こうと思います。

    非常に参考になりました。
    まだ解決できておりませんが、ありがとうございました。

    進展があり次第、報告させて頂ければと思っております。

    2010年10月19日 12:57