0

I have developed an application which runs with multiple instance simultaneously. In the application it connects to a SQL Server and fetches some data, while working on that data, it updates the row as picked so that no other instance of the application picks up the same record for research. While updating the row for the fetched record, sometime it gets deadlock issue and not updating the record as already picked.

Dim MyConnection As SqlConnection
Try
    MyConnection = New SqlConnection(connString)
    MyConnection.Open()
    Dim tableName As String = myTableName
    Dim sqlQuery As String = "Select Top 1 * from " + tableName + " where "<some condition>
    Dim MyCommand As SqlDataAdapter = New SqlDataAdapter(sqlQuery, MyConnection)
    Dim DS as DataSet = New DataSet
    MyCommand.Fill(DS, tableName)
    If DS.Tables(0).Rows.Count >= 1 Then
        sqlQuery = "UPDATE " + tableName + " SET Fld = @fld  where Cond1= '" + DS.Tables(0).Rows(0).Item("Cond1").ToString + "'"
        Dim cmd As New Data.SqlClient.SqlCommand(sqlQuery)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@fld", Data.SqlDbType.VarChar).Value = "Picked" 
        Try
            cmd.Connection = MyConnection
            cmd.ExecuteNonQuery() <---- Dead lock occurs here
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End If
Catch ex As Exception
    MsgBox(ex.ToString)
Finally
    MyConnection.Close()
End Try

While executing the update statement, deadlock occurs and it goes to catch block and then exits the sub without updating the required field in the table. I want it to try to update until the dead lock is removed.

Creating Stored Procedure is not possible as I do not have the admin rights to this DB.

How can I overcome the problem?

Mel Gerats
  • 2,234
  • 1
  • 16
  • 33
Meghdut Saha
  • 85
  • 1
  • 14
  • Just write some retry code which runs when the exception is a deadlock. Probably need to limit it to a specific number of times. – Dale K Jul 30 '19 at 08:03
  • You should catch deadlock graph and post it here – sepupic Jul 30 '19 at 09:55
  • @AndrewMorton "may default to 1" I wasn't aware of that. I think I have done this without size numerous times when feeling lazy. – Mary Jul 31 '19 at 00:01
  • The answers at [How to get efficient Sql Server deadlock handling in C# with ADO?](https://stackoverflow.com/q/320636/1115360) could be useful. – Andrew Morton Jul 31 '19 at 09:38
  • @Mary You're correct. It would be in a declaration in SQL that it would default to 1 e.g. `DECLARE @P VARCHAR`. – Andrew Morton Jul 31 '19 at 09:53

0 Answers0