Mr.Tの場所

Mr.Tです、こんにちは。

ちょー基本で簡単に、SQLServerでトランザクションの効果を確認する:
http://blogs.wankuma.com/mrt/archive/2007/10/12/101615.aspx

ちょー基本で簡単に、SQLServerでトランザクションの効果を確認する２
http://blogs.wankuma.com/mrt/archive/2007/10/14/101944.aspx

リトライする方法は？というので、従来よく使われていたと思われる手法をとってみます。

リトライするのは、難しく考えなければ方法としては次のようになります。

1)DBへの接続を開く

2)更新用のストアドを実行

3)例外の内容でリトライするかどうかを判断

4)リトライする場合は、再度、1)に戻る

※3)の例外では、処理がずーっと待たされてしまうので結局タイムアウトの例外になります。

 

前回と同じように、Factoryテーブルを対象にしてみます。

 まずは、トランザクションをかけて、テーブルを更新し、それに対してSelectした場合です。

 まだ、コミットされていないので、データは確定していません。

 これに対して、ちょっとしたプログラムで確認します。（ソースは、一番下についてます）

上記のまま、このプログラムを実行すると、3回リトライしたのちに、

 

では、確認で、上記プログラムを実行中に、先ほどのSQLでの更新をロールバックします。

とすると、トランザクションが解除されたので、

 

きちんと更新できました。

ソース中では、一部、タイムアウトを判断するのに、こういうルーチンを使っています。

Private Function IsTimeOutErrorOccured(ByVal ex As SqlException) As Boolean
    If ex.ErrorCode = -2146232060 _
        AndAlso ex.State = 0 _
        AndAlso ex.Number = -2 _
        AndAlso ex.Message.Contains("タイムアウト") = True Then

        Return True
    Else
        Return False
    End If
End Function

実は、私も色々しらべたのですがタイムアウトによる例外が、ErrorCodeだけでも、Stateだけでも、Numberだけでも
判断できないようなのです。
また、その3つを判定したとしても、タイムアウトであるという判断の元になるのは、実行してみての経験則でしかありません。
そもそも、例外ではタイムアウトを判断するのは、メッセージが確実そうだったのでメッセージ中の文字も、判断に加えています。

※もしきっちりと判断できる方法があるなら、教えていただきたいのです。

さて、こうやってリトライの方法を考えてみました。

しかし、ここでさらに考えたいのは、「そもそも自動リトライする必要があるのか？」です。

結論的には、「業務系では、自動リトライは必須でも、それほど必要なケースも多くない」です。

リトライという場合ですから、DBで他のトランザクションの解除を待つ必要がある場合だけです。
リトライしない場合は、ユーザに再更新してもらうために、アクション（ボタンを押す等）が必要です。
また、非同期実行でない限り、2)の実行結果は、タイムアウトしないと制御が戻ってきません。

タイムアウトがX秒だとすると、3回リトライした場合、X秒 * 3回だけ、ユーザは待たされる可能性があります。

最初からタイムアウトを3*X秒としたとしても、リトライ中である等のメッセージを表示するなどの通知ができる、という点を除き、
ユーザに処理を待たせるという点では、等価です。

そして、もうひとつ問題があります。CommandTimeoutだけでは、トランザクション中によるブロックなのか
判断できないできない、ということです。
たいていの場合、タイムアウトした場合、そのタイムアウトした原因を無視してリトライしてしまいます。
制御系はわかりませんが、業務系の場合ではネットワークの遅延があった、そんなことでもタイムアウトは発生してしまいます。

SQLCommand.CommandTimeoutプロパティには、以下のようにあります。

http://msdn2.microsoft.com/ja-jp/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

このプロパティは、コマンドの実行または結果処理中のすべてのネットワーク読み取りに対する累積タイムアウトを示します。タイムアウトは、最初の行が返された後でも発生します。ユーザー処理時間は計算されず、ネットワーク読み取り時間だけが考慮されます。

SQLServerでは、既定がページロックです。つまり、実際に更新されていないデータもロックされている可能性があります。
その場合でも、待たされてタイムアウトは発生します。
では、それを行ロックにして、処理したいデータがロックされている場合にリトライしたらよいだろう、ということになります。
仮に、どのデータがロックされているのかを判断したとして、それでもできることは「リトライ」であり、ユーザにとっては「待つこと」だけです。

この労力のわりに得られるものは、それほど多くないように思いませんか？

それなら、エラーだとすぐわかった時点でメッセージを出し、もう一回ボタンを押してもらうなり、

管理者に連絡するなりしたほうが、良いように思うのです。

 ----ソース、ここから

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1

Private Const ConnectionString As String = "Data Source=***;Initial Catalog=組織;User ID=*****;Password=*****"
Private Const targetQuery As String = "dbo.UpdateTable"
Private Const MaxRetryCount As Integer = 3
Private Const SleepTime As Integer = 3000
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim retryCount As Integer = 1
        Try
            While retryCount <= MaxRetryCount
                If UpdateTable() = True Then
                    SetMessage("更新成功")
                    Exit While
                End If

                If retryCount < MaxRetryCount Then
                    SetMessage(Replace("更新を$@count@$回失敗しました。ロックが解除されるのを待ちます。", "$@count@$", retryCount.ToString))
                    System.Threading.Thread.Sleep(SleepTime)
                End If
                retryCount += 1
            End While
            If retryCount > MaxRetryCount Then
                SetMessage("更新は失敗でした")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
End Sub


Private Function UpdateTable() As Boolean

    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using command As SqlCommand = New SqlCommand
            command.Connection = con
            command.CommandText = targetQuery
            command.CommandTimeout = 5
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add("@FactoryCD", SqlDbType.NVarChar, 10)
            command.Parameters.Add("@NewFactoryName", SqlDbType.NVarChar, 30)
            command.Parameters.Add("@ReturnValue", SqlDbType.Int)

            command.Parameters.Item("@FactoryCD").Value = "10"
            command.Parameters.Item("@NewFactoryName").Value = "姫路工場"

            command.Parameters.Item("@ReturnValue").Direction = ParameterDirection.Output
            command.Parameters.Item("@FactoryCD").Direction = ParameterDirection.Input
            command.Parameters.Item("@NewFactoryName").Direction = ParameterDirection.Input

            Try
                SetMessage("更新中")

                command.Connection.Open()
                command.ExecuteNonQuery()

                Dim returnValue As Integer = 0

                '@RetrunValueが-1の場合は、ストアド内でロールバックされているときである。
                returnValue = DirectCast(command.Parameters.Item("@ReturnValue").Value, Integer)
                If returnValue = -1 Then
                    Return False
                End If

                SetMessage("終了")

            Catch ex As SqlException
                If IsTimeOutErrorOccured(ex) = True Then
                    Return False
                Else
                    Throw
                End If
            Finally
                If command.Connection.State <> ConnectionState.Closed Then
                    command.Connection.Close()
                End If
            End Try
        End Using
    End Using


    Return True
End Function

Private Sub SetMessage(ByVal mes As String)
    Label1.Text = mes
    Label1.Refresh()
End Sub

Private Function IsTimeOutErrorOccured(ByVal ex As SqlException) As Boolean

    If ex.ErrorCode = -2146232060 _
        AndAlso ex.State = 0 _
        AndAlso ex.Number = -2 _
        AndAlso ex.Message.Contains("タイムアウト") = True Then

        Return True
    Else
        Return False
    End If
End Function

End Class