Deadlock Patterns....

[Robinson]

Apologies for the cross post, but I'm not too sure which group this belongs
in. At least I didn't get responses in the MSDE groups yet. Anyway what I
want to do is create a simple pattern for dealing with deadlock situations
in my VB.NET program. So far, I've come up with something like this
(below). The idea is simply to detect the deadlock exception and iterate
the operation until it finally succeeds (or we've tried it 3 times). Am I
missing something really important here, or is this an acceptable way to
deal with it? Alternatively I could just ignore the dead lock, signal to
the user as usual that an error occurred and carry on. I'm not sure what
the best way of dealing with these situations is.

Thanks.

Robin

' DEADLOCK PREFIX

Dim bDeadlocked As Boolean = True, nDeadlocks As Integer = 0

While bDeadlocked

Try
.....
DataReader = Command.ExecuteReader()
.....

' No exception on execute, so we were not deadlocked

bDeadlocked = False

Catch Ex As Exception

If Ex.Message.Contains("Rerun the transaction") Then

' We failed through deadlock.

nDeadlocks += 1

If nDeadlocks = 3 Then

' We deadlocked 3 times before, so give up now.

Return New DataError(Ex,
DataError.Errors.SourceGeneralException)

End If

End If

Return New DataError(Ex, DataError.Errors.SourceGeneralException)

Finally

.....

End Try

End While

[Robinson]

.... and another interesting question, where will the deadlock exception
occur, if I have, say, one transaction block and execute two (for example)
stored procedures on it? i.e.:

Try

.... theConnection.BeginTransaction ()

.... Result = Foo ( theConnection )

.... Result = Bar ( theConnection )

.... theConnection.Commit ()

Catch sqlEx As Exception

End Try
Presumably either of Foo and Bar can fail due to resource deadlock on the
DBMS. This being the case, do they handle the exception and re-try their
part of the transaction, or do I have to unroll the entire transaction and
start again? In other words, what is the granularity of resource deadlocks?
Might I have to write:

while bDeadlocked
Try

.... theConnection.BeginTransaction ()

.... Result = Foo ( theConnection )
If Result = MyErrorType.Deadlocked Then
bDeadlocked += 1
throw new Exception ( ""Rerun the transaction")
End If

.... Result = Bar ( theConnection )
If Result = MyErrorType.DeadLocked Then

bDeadlocked += 1

.... unwind any work that Foo has done...

throw new Exception ( ""Rerun the transaction")

End If

.... theConnection.Commit ()
Catch sqlEx As Exception
End Try

...
End While

>


' DEADLOCK PREFIX

Dim bDeadlocked As Boolean = True, nDeadlocks As Integer = 0

While bDeadlocked

Try
.....
DataReader = Command.ExecuteReader()
.....

' No exception on execute, so we were not deadlocked

bDeadlocked = False

Catch Ex As Exception

If Ex.Message.Contains("Rerun the transaction") Then

' We failed through deadlock.

nDeadlocks += 1

If nDeadlocks = 3 Then

' We deadlocked 3 times before, so give up now.

Return New DataError(Ex,
DataError.Errors.SourceGeneralException)

End If

End If

Return New DataError(Ex, DataError.Errors.SourceGeneralException)

Finally

.....

End Try

End While