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 =
3Private Const SleepTime
As Integer =
3000Private 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 TryEnd SubPrivate 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 TrueEnd FunctionPrivate Sub SetMessage(
ByVal mes
As String)
Label1.Text = mes
Label1.Refresh()
End SubPrivate 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 IfEnd FunctionEnd Class