|
|
Deadlock recovery / Transaction In Doubt problems |
|
Author |
Message |
gamename
|
SQL Server >> Deadlock recovery / Transaction In Doubt problems
I've gotten to a point in my ASP.Net 2.0 web application, where a
certain (somewhat time intensive) set of queries can result in a
deadlock when run simultaneously by multiple users.
I believe, given the current state/situation of my application, that
the best solution is for the user who got deadlocked to try to run
their query again. I've read on other sites/forums that this is a valid
solution to deadlock issues. So in order to do that, I implemented the
following:
Dim blTry As Boolean = True
Dim blHaveTried As Boolean = False
While blTry
Try
'execute my sql command here
blTry = False
Catch ex As SqlClient.SqlException
If ex.Number = 1205 AndAlso blHaveTried = False Then
blTry = True
blHaveTried = True
Else
Throw
End If
End Try
End While
The overall purpose of that code is to attempt the query, and catch a
deadlock error (ex.Number = 1205) when it occurs. It then attempts to
run the query again, at most one extra time. (I've set it up like this,
just in case I decide in the future that I want to actually try more
than one extra time.) When the command successfully executes, blTry
will become false, and the while loop will finish.
This seems to capture the deadlock correctly, and also seems to attempt
the query again correctly. However, when I attempt to open my database
connection the second time, I get: [TransactionInDoubtException: The
transaction is in doubt.] from
System.Data.SqlClient.SqlConnection.Open().
Is there a way to force the transaction to proceed? Or am I supposed to
ditch the entire transaction and start again?
SQL Server139
|
|
|
|
|
|
|