SQL Management経由だと成功するUPDATEがAPPからだとタイムアウトになる

環境/言語:[Windows XP,7、VB.NET、NET Framework 4.0]
分類:[.NET]

サーバは、OS：Windows2008、SQL Server 2008 R2を利用しています。

監視用Table：HYPAIに更新FLGが1のレコードがある時、
マスタTable：TPAIに反映させる
ということをしたいのですが、
コミット直前のTpaiCommand.ExecuteNonQuery()
のところで
【エラー】
予期せぬエラー発生System.Data.SqlClient.SqlException (0x80131904): Timeout に達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。ステートメントは終了されました。

となり、失敗します。
しかし、実際に発行されているUPDATE文を「SQL Management」等から発行すると、すぐに更新されます。
ロジックに問題があるのでしょうか。
なお、本subは定期的に実行しています。
よろしくお願いします。

********************
Public Sub SetTPAI()
Dim strCol, strValue, strMsg, strSQL As String
Dim HitCount, RetryCount As Integer

'初期値設定
Dim OkCount As Integer = 0
Dim NgCount As Integer = 0
Dim TotalCount As Integer = 0

'YoyakuDBへのConnectionオープン
Using HypaiConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)
Dim HypaiCommand As SqlClient.SqlCommand = HypaiConnection.CreateCommand
HypaiCommand.CommandTimeout = 60
'Hypaiコネクションを開く
HypaiConnection.Open()

'HYPAIに更新対象があるか調査するSQL
HypaiCommand.CommandText = "SELECT * FROM HYPAI WHERE 更新ＦＬＧ='1'"
'データリーダからのデータの呼び出し
Dim HypaiDR As SqlClient.SqlDataReader = HypaiCommand.ExecuteReader()

'HYPAIに更新対象がある時
Do While HypaiDR.Read
Using TPAIConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)
Dim TpaiCommand As SqlClient.SqlCommand = TPAIConnection.CreateCommand
TpaiCommand.CommandTimeout = 60
'TPAIコネクションを開く
TPAIConnection.Open()
'TPAIに同一IDがあるか調べるSQL
TpaiCommand.CommandText = "SELECT COUNT(*) as HitCount FROM TPAI WHERE ID='" & Convert.ToString(HypaiDR("ID")) & "'"

HitCount = Convert.ToInt32(TpaiCommand.ExecuteScalar.ToString)
TotalCount = TotalCount + 1
Try
TpaiCommand.Transaction = TPAIConnection.BeginTransaction
'TPAIに同一IDがない時、Insert
If HitCount = 0 Then
'*******************************************
'TPAIにINSERT
'*******************************************
TpaiCommand.CommandText = "INSERT INTO TPAI (ID,データ) VALUES (" & HypaiDR("ID") & "," & HypaiDR("データ") & ")"
TpaiCommand.ExecuteNonQuery()
Else
'TPAIに同一IDがある時、Update
'*******************************************
'TPAIをUPDATE
'*******************************************
strSQL = "UPDATE TPAI SET データ='" & HypaiDR("データ") & "' WHERE ID='" & Convert.ToString(HypaiDR("ID)) & "'"

TpaiCommand.CommandText = strSQL
TpaiCommand.ExecuteNonQuery()
End If
'*******************************************
'HYPAIをUPDATE
'*******************************************
'更新用SQLの取得
RetryCount = Convert.ToInt32(HypaiDR("リトライ回数")) + 1
strSQL = "UPDATE HYPAI SET 更新ＦＬＧ='0',リトライ回数='" & RetryCount & "',更新日時='" & Now & "'" & _
" WHERE ID='" & Convert.ToString(HypaiDR("ID")) & "'"
TpaiCommand.CommandText = strSQL
TpaiCommand.ExecuteNonQuery() ←ここでエラー

'トランザクションのコミット
TpaiCommand.Transaction.Commit()
OkCount = OkCount + 1
TPAIConnection.Close()

Catch ex As Exception
'*******************************************
'ロールバック
'*******************************************
TpaiCommand.Transaction.Rollback()
NgCount = NgCount + 1
TPAIConnection.Close()

Exit Do
End Try
End Using
Loop
'HyPAIへのConnectionクローズ
HypaiConnection.Close()
'HyPAIDRのクローズ
HypaiDR.Close()
End Using
End Sub

気になる点が。

> Using HypaiConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)

> Using TPAIConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)

これは同じコネクションですよね？
同じコネクションを２つ用意する必要はないのでは？
（あるいは2つコネクションを用意しようとして、結果として例外になっているのでは）

■No29968に返信(ルナさんの記事)

> TpaiCommand.ExecuteNonQuery() ←ここでエラー
デバッグ実行でこの行で止めた状態でSQL ManagementでUpdate文実行出来ますか？
出来ないとしたらズッカさんも指摘していますがコネクションを分けているのが原因だと思います。おそらくReader用にSelectしたときに更新ロックされているのでしょう。

■No29973に返信(ズッカさんの記事)
> 気になる点が。
>
>>Using HypaiConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)
>
>>Using TPAIConnection As New SqlClient.SqlConnection(My.Settings.yoyakuDBConnectionString)
>
> これは同じコネクションですよね？
> 同じコネクションを２つ用意する必要はないのでは？
> （あるいは2つコネクションを用意しようとして、結果として例外になっているのでは）
>
ズッカ様
ご指摘ありがとうございます。

本当はそうしたかったのですが、うまくいきませんでした。
HYPAIから取得した内容をDataReaderにセットし、TPAIへのUPDATE文を作成しようとしていますが、
HitCount = Convert.ToInt32(TpaiCommand.ExecuteScalar.ToString)
のところで
「このCommandに関連付けられているDataReaderが既に開かれています。このコマンドを最初に閉じる必要があります。」
とエラーになるためにコネクションを２つ作成しました。

これだとエラーにならなかったというだけで、このやり方でよいのか自信ありません。
一人独学で業務ソフトを作っているので、とても参考になりました。
やっぱり違和感ありますよね、、、



問い合わせの件については、自己解決しました。
"SELECT * FROM HYPAI WHERE 更新ＦＬＧ='1'"
でHitするレコードが1万を超えていたことが原因でTimeoutとなっていました。
"SELECT TOP 200 * FROM HYPAI WHERE 更新ＦＬＧ='1'"
等にすることでTimeoutを回避できるようになりました。

> デバッグ実行でこの行で止めた状態でSQL ManagementでUpdate文実行出来ますか？
> 出来ないとしたらズッカさんも指摘していますがコネクションを分けているのが原因だと思います。おそらくReader用にSelectしたときに更新ロックされているのでしょう。

投稿してから拝見しました。
そのとおり、UPDATE文実行できませんでした。
やっぱり根本的にロジックに問題あるのですね。。。。
見直してみます。

> HYPAIから取得した内容をDataReaderにセットし、TPAIへのUPDATE文を作成しようとしていますが、
> HitCount = Convert.ToInt32(TpaiCommand.ExecuteScalar.ToString)
> のところで
> 「このCommandに関連付けられているDataReaderが既に開かれています。このコマンドを最初に閉じる必要があります。」
> とエラーになるためにコネクションを２つ作成しました。

なるほど。おそらく、

> 'データリーダからのデータの呼び出し
> Dim HypaiDR As SqlClient.SqlDataReader = HypaiCommand.ExecuteReader()

でDataReaderを使っているからですね。
DataReaderは接続を占有してしまうので、ExecuteScalar出来なかったということだと思います。
その他、DataReader使用時は、ExecuteNonQueryも使えません（以下サイト参照のこと）。

http://homepage1.nifty.com/rucio/commu/ThreadDetail_ThreadId_8993.htm

その場合は、今回貴方が試したような、2つのコネクションを用意する方法の他、上記サイトにも
記載がありますが、DataReaderを使わず、DataTableを使って一気にデータをメモリ上に取得する方法もあります。
（1万件超のデータがヒットするとありますが、必要に応じて読み込む項目を絞れば、
大したデータ量ではないと思います。）

コネクションをひとつだけにすることが、根本の問題が解決するかどうかは分かりませんが、
少なくとも、膨大な処理の間、DataReaderが接続を占有することによる何らかの影響は避けられるかも
知れませんね。

対策1
　接続を一つにまとめて
　接続文字列に MultipleActiveResultSets=true; を追加する
　(詳しくはMultipleActiveResultSetsで検索してください)
対策2
　TransactionのIsolationLevelを変更する
　(試してないので有効かわかりません＾＾；)

ズッカ様
DataTableを使用する方法でうまくいきました。

参考ページもとてもわかりやすく大変助かりました。
ありがとうございました。