none
SqlClientConnectionで、SQLServerから正常なデータが返されない。 RRS feed

  • 質問

  • 原因不明な現象に悩まされており、お助け下さい。

    【環境】

    ・サーバー

     Windows2012

     SQLServer 2014

    ・クライアント

     WindowsCE Compact 7

    ・開発環境

     Visual Studio 2008 SP1

    【現象】

    100台近いWindowsCE端末で、日々在庫の管理をしています。

    在庫が移動した時に、元在庫の在庫数を0にして、移動先の在庫数を登録する処理を行っていますが、

    月に4,5回の頻度で元在庫の情報を取得するSQLの結果が、データベースの内容と一致しない事があります。

    一致しないと言うのは、データが存在するのに、DataAdapter.Fill()メソッドで取得されたDataTableの結果が0件になるのです。

    また、この現象が発生した時、プログラムで開始したBeginTransactionが、意図しない所で RollBack 又は、Commitが行われます。

    (正常にBeginTransactionが行われなかった可能性もあるかと・・)

    その為、明示的に記述した Commit処理で「SqlException:COMMIT TRANSACTION 要求に対応する BEGIN TRANSACTION がありません」が発生します。この時のInnerExceptionは「InvalidOperationException:This SqlTransaction has completed; it is no longer usable.」です。

    このエラーが発生した時、INSERT、UPDATEしたデータがSQLServerに登録されている事から、

    ①DBの接続のOpen時

    ②BeginTransactionを行った時

    ③INSERT等の前のSELECT文を発行した時

    のどこかのタイミングで、SQLServerへの接続に問題が発生していると思われます。

    ただ、Exceptionが発生しない為、プログラム側で対応が取れません。

    何か、情報をお持ちの方や、SQLServerで何を調べれば良いかなど、調査方法をご存じの方が居ましたら、アドバイスを頂きたいと思います。

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

    【プログラム(プログラムをそのまま載せれませんでしたので、簡略化してあります。)】

    Dim conn As New SqlClient.SqlConnection("Data Souce=・・・")
    Dim dc As System.Data.SqlClient.SqlCommand
    Dim da As New SqlClient.SqlDataAdapter()
    Dim trn as New SqlClient.SqlTransaction()

    Dim dt as New DataTable

    conn.Open()

    Try
        'トランザクションの開始
        trn.BeginTransaction()

        '-- 在庫状況情報出力ログ --
        sql = "SELECT * FROM E_在庫 WHERE 品目CD = 'AAAA'"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        da = New SqlClient.SqlDataAdapter(dc)
        da.Fill(dt)

        '-- 在庫の存在するデータの取得 --
        sql = "SELECT * FROM E_在庫 WHERE 品目CD = 'AAAA' AND 在庫数 > 0 ORDER BY 更新日時 DESC"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        da = New SqlClient.SqlDataAdapter(dc)
        da.Fill(dt)

    '出庫情報の更新
    sql = "UPDATE E_在庫 ・・・"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        dc.ExecuteNonQuery()
        dc.Dispose()

    '出庫履歴情報の登録
    sql = "INSERT INTO E_在庫履歴 ・・・"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        dc.ExecuteNonQuery()
        dc.Dispose()

    '入庫情報の登録
    sql = "INSERT INTO E_在庫 ・・・"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        dc.ExecuteNonQuery()
        dc.Dispose()

    '入庫履歴情報の登録
    sql = "INSERT INTO E_在庫履歴 ・・・"
        dc = New System.Data.SqlClient.SqlCommand(conn, sql, trn)
        dc.ExecuteNonQuery()
        dc.Dispose()

        'コミット
        trn.Commit()

    Catch ex As Exception

        'ロールバック
        trn.Rollback()

    Finally
        'オブジェクトの終了
        dc.Close()
    End Try

    2015年3月15日 5:20

回答

すべての返信

  • 直接的な回答は持ち合わせていませんが…

    実際のコードは正しい前提でしょうか? というのもSqlTransactionはNewできませんし、trn.BeginTransaction()というメソッドも存在しません。というように挙げられている抜粋コードはちょくちょく間違っています。

    2015年3月15日 14:06
  • トランザクションの既定の分離レベルは把握されていますか? 感覚としては、デッドロックが怪しそうな気がします。デッドロックの調査の仕方はいくつかありますので、とりあえず調べられて、都合の良いものを採用して下さい。


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    • 回答の候補に設定 星 睦美 2015年3月17日 5:10
    • 回答としてマーク 星 睦美 2015年3月18日 6:51
    2015年3月15日 14:06
  • 佐祐理様、trapemiya様 ご返信ありがとうございます。

    佐祐理様
    ご指摘の部分は、現在のソースコードが直接表示出来ない為、
    こちらで変更を加えた部分になります。
    ご指摘の通り、SqlTransactionはNew出来ないですね。

    現在、日に2000件の在庫移動処理がされていますので、

    処理としては問題は無いと考えております。

    trapemiya様
    > トランザクションの既定の分離レベルは把握されていますか?
    > 感覚としては、デッドロックが怪しそうな気がします。
    > デッドロックの調査の仕方はいくつかありますので、とりあえず調べられて、都合の良いものを採用して下さい。

    トランザクションの分離レベルですが、把握していません。
    規定値のままセットアップしてありますので、確認をしてみます。

    デッドロックですが、こちらもログを拾えるようにして確認してみたいと思います。

    2015年3月16日 0:12
  • trapemiya様

    一つ質問がございます。

    MS - 文書番号: 832524を確認致しましたが、

    SQLServerがデッドロックを検知した時、「クライアントに送信する 1205 エラー メッセージを発行し、選択した SPID を終了します。」と記述されています。

    1205等のエラーが返されない事がある事もあるのでしょうか?

    ご経験がありましたら、教えて頂きたいと思います。


    2015年3月16日 0:53
  • 遅くなり、すみません。

    >1205等のエラーが返されない事がある事もあるのでしょうか?

    基本的に以下の文書を読む限り、無いんじゃないかと思います。1205が返される場合は、自動でロールバックされるので、その際にはコードで明にロールバックの処理を実行していないサンプルが以下に載っています。

    Microsoft .NET を使ったデータベース トランザクションの実装
    https://msdn.microsoft.com/ja-jp/library/ms954625.aspx

    また、以下も参考にしてみて下さい。

    (参考)
    デッドロックの検出と終了
    https://technet.microsoft.com/ja-jp/library/ms178104(v=sql.105).aspx

    デッドロックの処理
    https://technet.microsoft.com/ja-jp/library/ms177453(v=sql.105).aspx

    なお、デッドロックの調査に関して、いくつか検索してみましたのでご紹介しておきます。

    SQL Server でデッドロックを監視するには
    http://d.hatena.ne.jp/matu_tak/20091027/1256569336

    2009-10-29 SQL Server でデッドロックの監視 その2
    http://d.hatena.ne.jp/matu_tak/20091029/1256748649

    2009-10-30 SQL Server でデッドロックの監視 その3
    http://d.hatena.ne.jp/matu_tak/20091030

    SQL Server でトレース フラグ 1204 でデッドロックを監視する
    http://d.hatena.ne.jp/matu_tak/20091031/1256915952

    デッドロックの状態を取得する
    http://d.hatena.ne.jp/seki-moto/20101117/DeadLock

    2008-03-07 [DB]SQLServerProfilerでデッドロックの図示
    http://d.hatena.ne.jp/iad_otomamay/20080307

    ブロッキングとデッドロックを後追い確認する方法
    http://blog.engineer-memo.com/2012/08/30/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%81%A8%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF%E3%82%92%E5%BE%8C%E8%BF%BD%E3%81%84%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B%E6%96%B9/

    [SQLServer]デッドロックを発生させてログを拾ってみる
    http://t-wata.com/?p=221

    #さて、ここはフォーラムですので、質問がある場合は誰かに限定して尋ねるよりも、広く回答を募る方が迅速、かつ良質の回答が得やすいです。

    (例)
    trapemiya様からご回答をいただいて調査したのですが、一つ疑問が出て来ました。以下の質問に関して、おわかりの方がいらっしゃいましたら、ご回答の程、よろしくお願いいたします。

    MS - 文書番号: 832524を確認致しましたが、・・・・・(略)


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/


    • 編集済み trapemiya 2015年3月16日 14:39 誤字訂正
    • 回答としてマーク 星 睦美 2015年3月18日 6:52
    2015年3月16日 10:19
  • trapemiya様

    ありがとうございます。

    頂いた情報を確認してみます。

    現在、SQL_Traceでトランザクション、SQL等の状態を収取する様に致しました。

    何かわかりましたら、また質問させて頂きます。

    2015年3月18日 0:04
  • フォーラム オペレーターの星 睦美です。
    ryoukai8 さん、こんにちは。

    今回はフォーラム ユーザーからトラブルシューティングのための情報を得られたのではないかと思います。もし事象に関してさらに質問がありましたら、調査した情報をもとに新しいスレッドを作成いただければと思います。

    trapemiya さんからの参考情報をフォーラムで共有したいと思いますので、スレッドに私のほうで[回答としてマーク] させていただきました。今後ともフォーラムをお役立てください。


    フォーラム オペレーター 星 睦美 - MSDN Community Support

    2015年3月18日 6:59
  • 原因が色々と判明しましたので、ご報告になります。

    SQLServerのトレースを取って確認をした所、該当する現象が発生する時に「デッドロック」が発生しておりました。

    その為、デッドロック発生直後にSQLServerにより「ROLLBACK」がされておりました。

    これにより、CommitメソッドでSqlException:COMMIT TRANSACTION が発生した様です。

    デッドロックが発生しても例外にならなかった件ですが、「Windows Embedded Version 7.00(ビルド 2806)」に限った事かもしれませんが、SqlClient.SqlDataAdapter.Fillメソッドで、デッドロックが例外にならない事が判明しました。

    Windowsアプリでは、デッドロックは例外になり、WindowsCEのSqlClient.SqlCommand.ExecuteNonQueryメソッドでもデッドロックが例外になりました。よって、WindowsCEのFrameworkのバグか何かかと思います。

    皆様、色々ありがとうございました。

    • 回答としてマーク ryoukai8 2015年3月20日 7:01
    2015年3月20日 7:01