none
Commitの要否について RRS feed

  • 質問

  • SQL Server 2000 -> 2008 へ変更した以降だと思うのですが

    ループ処理中でのUpdate、Insertがおかしくなりました。

    Table1の内容をTable2に存在すればUpdate、無ければInsertしています。 ※Table1ID, Table2IDはそれぞれユニークキーです。

    Table2の内容はTable1IDで一意になるはずですが2件または3件存在する場合があります。

    以下のようなSQL文です。

    -----------------------------------------------------------------------------------------

    DECLARE Loop1 CURSOR LOCAL READ_ONLY FOR 
    SELECT DISTINCT
     Table1ID ,
     Value1
    FROM Table1
    ORDER BY
     Table1ID

    OPEN Loop1

    FETCH NEXT FROM Loop1 INTO @L1_Table1ID, @L1_Value1

    WHILE @@FETCH_STATUS = 0
    BEGIN
     IF EXISTS( SELECT * FROM dbo.Table2 WHERE Table1ID = @L1_Table1ID )
     BEGIN
      UPDATE dbo.Table2
      SET
       Value2 = Value1
      FROM dbo.Table2
      WHERE
       Table1ID = @L1_Table1ID
     END
     ELSE
     BEGIN
      INSERT INTO dbo.Table2
       (
       Table1ID,
       Value2
       )
      SELECT
       @L1_Table1ID,
       @L1_Value1
      
     END

     FETCH NEXT FROM Loop1 INTO @L1_Table1ID, @L1_Value1
    END
    CLOSE Loop1

    -----------------------------------------------------------------------------------------

    2008以降は明示的にCommitでもしなければループ中のUpdateは反映されないのでしょうか?

    2015年1月15日 8:36

回答

  • 質問が最初に戻りますが

    SELECTでID列が2回でることもありえないし、さらにEXISTSが判定されないはずもないので質問したわけです。

    SQL2008自体があまり詳しくないので2000との変化点を気にしてました。

    Commitどうこう関係ないのであれば違う個所に問題があるはずなので調査しています。

    • 回答としてマーク fsp6 2015年1月16日 8:23
    2015年1月16日 8:20
  • しばらく間が空きましたがようやく解決しました。

    Windows Updateを実行したらその後、この現象は消えました。

    ですのでやはりコードのミスではなく、何かのパッチが適用されていないのが原因と思われます。

    協力していただいた方ありがとうございました。

    • 回答としてマーク fsp6 2015年2月11日 23:56
    2015年2月11日 23:56

すべての返信

  • 回答ではありませんが、SQL Server 2008以降ということでしたらMERGE文で済みます。

    MERGE Table2
    USING Table1
        ON (Table2.Table1ID = Table1.Table1ID)
    WHEN MATCHED THEN 
        UPDATE SET Value2 = Table1.Value1
    WHEN NOT MATCHED THEN	
        INSERT (Table1ID, Value2)
        VALUES (Table1.Table1ID, Table1.Value1);
    

    また

    > Table2の内容はTable1IDで一意になるはずですが2件または3件存在する場合があります。

    の文意が読み取れませんでした。Table2に重複行が存在する、つまりEXISTS部分が正しく判定できていないということでしょうか? Table1に於いてTable1IDが一意であればループ処理も各Table1IDごとに1回しか実行されないはずで「2008以降は明示的にCommitでもしなければループ中のUpdateは反映されないのでしょうか?」とは関係ない問題に読み取れます。

    2015年1月15日 9:04
  • 分かりにくい表現となりすみません。

    ご指摘の通り、EXISTS部分が正しく判定できていないと思われます。

    レコード毎にデータ作成日時(GETDATE)が入るようにしています

    Table2の中には同じTable1IDでデータ作成日時だけ僅かな差があるレコードが存在します。

    2015年1月15日 9:18
  • > Table2の内容はTable1IDで一意になるはずですが2件または3件存在する場合があります。

    は同一の一意キーのレコードが複数できてしまう、ということでしょうか?

    一意キーをちゃんと指定していればINSERT時にエラーになりませんか・・・?

    2015年1月15日 9:32
  • Halloween Protection だったかの絡みで類似の不具合が SP で修正されていたと思いますので、先ずは SP4 を適用してください。
    SP4 を適用すると Build 番号が 10.00.6000.XXX もしくはそれ以上になります。


    MCITP(Database Developer/Database Administrator)

    2015年1月15日 10:06
  • ご指摘の通り、EXISTS部分が正しく判定できていないと思われます。

    レコード毎にデータ作成日時(GETDATE)が入るようにしています

    Table2の中には同じTable1IDでデータ作成日時だけ僅かな差があるレコードが存在します。

    なぜ「GETDATE」や「僅かな差があるレコードが存在」という説明が出てくるのか、疑問に感じますし、問題はこの辺りに存在したりしませんか? 例えばループ内でGETDATE()を実行しているとか。
    2015年1月15日 10:20
  • 分かりにくい表現となりすみません。

    ご指摘の通り、EXISTS部分が正しく判定できていないと思われます。

    レコード毎にデータ作成日時(GETDATE)が入るようにしています

    Table2の中には同じTable1IDでデータ作成日時だけ僅かな差があるレコードが存在します。

    これは、Table1のレコードのループ処理で、Table2にレコードをINSERTする際に、実際のコードではその時点のGETDATE結果もTable2の新しい行の別の列に入れている、という意味でしょうか?

    わずかな差とは、この一連のループ処理でINSERTされたはず、という程度に、わずかな差、という意味でしょうか?

    佐祐理さんが書かれているように、そもそもTable1IDがTable1のユニークキーなら、ループ内で重複すること自体があり得ないはずです。
    DISTINCTもついていますが、そもそも必要ないはずですし、なぜつけているんでしょう?

    何か根本的な勘違いやコードミスなどがないですかね?

    あと、ハロウィン問題は、なんとなくちょっと違う気がするのですが、修正内容確認していないので実際のところはわからないです。

    2015年1月16日 1:22
  • 回答ありがとうございます。補足させていただきます。

    Table2にデータ作成日時という項目があり、
    初期値がGetdate()になるようにしてます。

    僅かな差とはこの値の事です。
    重複しているレコードでこの値だけが異なっています。
    Insertされたタイミングが同じであれば同じ値が入ると思うのですが数ミリ秒違いがあります。
    ですのでEXISTSが正しく機能していないのでは?と感じています。


    Table1はTable1IDが
    Table2にはTable2IDがユニークキーです。

    Table2にはTable1のどのレコードからInsertされたのかを
    追跡できるようにTable1IDを持たせています。

    DISTINCTについては意味が無いですが
    入れても支障はないはずですので入れてます。

    なおこのストアドは5年以上前に作られたもので
    2008にアップデートする前は正常に機能していました。

    2015年1月16日 4:05
  • 未だに質問文が理解できません。

    > 重複しているレコードでこの値だけが異なっています。Insertされたタイミングが同じであれば同じ値が入ると思うのですが数ミリ秒違いがあります。

    「この値だけが異なってい」る、すなわちTable2に於いて、Table1IDやTable2IDの重複したレコードが存在するということでしょうか? puniさんが指摘されるように一意制約をかけていないのでしょうか?
    またループで回っていれば時刻が進むのは当たり前ですが…。ですから最初にコメントしたように一括で処理することが重要なのですが…。
    ともあれ、複数回INSERTが行われているということになると、元になるTable1にも重複があったという別の問題も浮上してきませんか?

    EXISTSを疑うのでしたら、まず根本的なところとして、Table1.Table1IDとTable2.Table1IDと@L1_Table1ID変数の型は一致しているのでしょうか? 異なる場合、型変換の過程で正しく比較できないこともあるかと。

    2015年1月16日 4:45
  • 説明不足でした。すみません。

    Table1ID、Table2IDは一意制約があり、自動でIDが連番付与されます。

    そのためTable2の問題となるレコードはデータ作成日時だけでなく、Table2IDも異なります。(自動で振られる番号なので)

    データ作成日時が異なるということは時刻が進むためループ処理の中でも違う回にInsertされたと思います。

    そのためにEXISTSが判定されていないと思いました。

    ちなみにBuild 番号は10.0.6000.29でした。

    もう少し違う観点で調べてみます。

    2015年1月16日 5:26
  • 説明不足でした。すみません。

    Table1ID、Table2IDは一意制約があり、自動でIDが連番付与されます。

    そのためTable2の問題となるレコードはデータ作成日時だけでなく、Table2IDも異なります。(自動で振られる番号なので)

    データ作成日時が異なるということは時刻が進むためループ処理の中でも違う回にInsertされたと思います。

    そのためにEXISTSが判定されていないと思いました。

    Table1IDがTable1のユニークキーなら、ループ内で同じTable1IDが出ること自体がないはずだ、と言っているのです。

    同一ループ内で2回出ること自体があり得ないはずなので、何か勘違いがあるのではないか、ということです。

    2015年1月16日 7:04
  • 質問が最初に戻りますが

    SELECTでID列が2回でることもありえないし、さらにEXISTSが判定されないはずもないので質問したわけです。

    SQL2008自体があまり詳しくないので2000との変化点を気にしてました。

    Commitどうこう関係ないのであれば違う個所に問題があるはずなので調査しています。

    • 回答としてマーク fsp6 2015年1月16日 8:23
    2015年1月16日 8:20
  • いああ、ずっとEXISTSを疑っている感じ(というかEXISTSが原因という前提)の書き方っぽかったので、そう判断しているのだと思い込んでいました。

    それはさておき、最初に示されたコードはどの程度正確(実際のコードに近い)ですか?
    ※TransactSQLをきっちり理解してないというのが大きいんですが、どうも変に見える箇所があったりするので

    Table2が空の状態で実行しても、同じ現象は発生しますか?

    2015年1月16日 8:51
  • 返信ありがとうございます。

    実際のコードはかなり長いコードなので記載できませんが基本的な動きは同じです。

    本番環境なのでTable2が空の状態では試してませんが

    ストアドが動くタイミングでテスト用のテーブルへログを書き込むような工夫をして追跡したいと思います。

    2015年1月19日 7:30
  • しばらく間が空きましたがようやく解決しました。

    Windows Updateを実行したらその後、この現象は消えました。

    ですのでやはりコードのミスではなく、何かのパッチが適用されていないのが原因と思われます。

    協力していただいた方ありがとうございました。

    • 回答としてマーク fsp6 2015年2月11日 23:56
    2015年2月11日 23:56