darrenwright
asked on
catching and handling SQL deadlocks from vb.net
I'm using vb.net 2005 and SQL 2005. Very occasionally I am getting a SQL deadlock - "Transaction (Process ID 258) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
- how do I check for the deadlock exception in my catch block?
- I am handling the transaction from .NET so I do a trans.commit or trans.rollback. However, in some situations SQL does the rollback itself - in deadlock situations for example. How can I check that my transaction is still 'active' before I attempt to rollback?
- how do I check for the deadlock exception in my catch block?
- I am handling the transaction from .NET so I do a trans.commit or trans.rollback. However, in some situations SQL does the rollback itself - in deadlock situations for example. How can I check that my transaction is still 'active' before I attempt to rollback?
Try
cn.startUPConnection()
Using tran As SqlTransaction = cn.appConnection.BeginTransaction(IsolationLevel.ReadCommitted)
Try
If objtransMasterList.IsDirty Then
Dim objTransMaster As SQLTransMaster
For Each objTransMaster In objtransMasterList
If objTransMaster.IsNew Then
Dim cmd As New SqlCommand
cmd.CommandText = SQLInsertStoredProc
cmd.Connection = cn.appConnection
cmd.Transaction = tran
cmd.CommandType = CommandType.StoredProcedure
saveTransMasterParamsFromObj(cmd, CMDTYPES.INSERT, objTransMaster)
cmd.ExecuteNonQuery()
End If
Next
End If
tran.Commit()
Catch a As Exception
tran.Rollback()
success = False
objSQLErrorList.processError(a)
unLockIt()
GoTo finally_Renamed
End Try
End Using
Catch b As Exception
success = False
objSQLErrorList.processError(b)
unLockIt()
Finally
If Not cn.appConnection Is Nothing Then
If cn.appConnection.State <> ConnectionState.Closed Then cn.CloseConnection()
End If
End Try
ASKER
in my catch block I process the error via objSQLErrorList.processErr or which writes the error to a log file.
of my two part question, I now think I've sorted the first one - I'm just testing it now. I've changed so I catch the sqlexception and check for number 1205. What I need to work out now is second part of question. How can I check whether I need to do a trans.rollback or whether it's already been rolled back by SQL?
Catch sqlEx As SqlException
success = False
If sqlEx.Number = 1205 Then 'deadlock
mustRetry = True
retryCount = retryCount + 1S
End If
of my two part question, I now think I've sorted the first one - I'm just testing it now. I've changed so I catch the sqlexception and check for number 1205. What I need to work out now is second part of question. How can I check whether I need to do a trans.rollback or whether it's already been rolled back by SQL?
Catch sqlEx As SqlException
success = False
If sqlEx.Number = 1205 Then 'deadlock
mustRetry = True
retryCount = retryCount + 1S
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
was that in the event log? or can you see this in the exception thrown?