title: How to get rid of deadlock in a SQL Server 2005 and C# application?
body: <p>I have a code in C# for windows service which is mainly responsible to update records in a table in my database, but I get always a lot of errors in my log, all errors are about deadlock of resource,</p>
  
  <p>This is the error:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException (0x80131904): Transaction (Process
    ID 57) was deadlocked on lock resources with another process and has
    been chosen as the deadlock victim. Rerun the transaction.    at
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
    Boolean breakConnection, Action<code>1 wrapCloseInAction)    at
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
    stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at
    System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
    SqlCommand cmdHandler, SqlDataReader dataStream,
    BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
    stateObj, Boolean&amp; dataReady)    at
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
    RunBehavior runBehavior, String resetOptionsString)    at
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
    async, Int32 timeout, Task&amp; task, Boolean asyncWrite)    at
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
    method, TaskCompletionSource</code>1 completion, Int32 timeout, Task&amp; task,
    Boolean asyncWrite)    at
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
    completion, String methodName, Boolean sendToPipe, Int32 timeout,
    Boolean asyncWrite)    at
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at
    WheelTrackListener.DataAccess.SQLDBA.ExecuteNQuery(SqlCommand cmd,
    Boolean isShowError, ConnectionStringType CountryCode, String
    deviceID, Int32 retry, String functionCallName)
    ClientConnectionId:e45e4cf1-a113-46b7-b9b5-dc5ee8111406</p>
  </blockquote>
  
  <p>Now, I want to ask, can I make a try or check if resource is locked? and if locked how to wait update until it is released?</p>
  
  <p>Here is my current code: </p>
  
  <pre><code>public static int updateVehicleLastPosition(string UTCDate, string UTC_Time, 
                string NS_Indicator, string Latitude, string EWIndicator, 
                string Longtitude, string Speed, string Processed, 
                string Near_ByLocation, string Near_ByLocation_AR, 
                string Gis_dataID, string address, string ar_adress, string Device_ID)
  {
          SQLMethods sql = new SQLMethods();
          SqlCommand cmd = sql.cmdUpdateVehicleLastPosition(UTCDate, UTC_Time, NS_Indicator, Latitude, EWIndicator, Longtitude, Speed, Processed, Near_ByLocation, Near_ByLocation_AR, Gis_dataID, address, ar_adress, Device_ID);
          SQLDBA sqlDBA = new SQLDBA();
          return sqlDBA.ExecuteNQuery(cmd, true, ConnectionStringType.OMN, Device_ID, 10, "updateVehicleLastPosition");
  }
  
  public SqlCommand cmdUpdateVehicleLastPosition(string UTCDate, string UTC_Time, 
                        string NS_Indicator, string Latitude, string EWIndicator, 
                        string Longtitude, string Speed, string Processed, 
                        string Near_ByLocation, string Near_ByLocation_AR, 
                        string Gis_dataID, string address, string ar_adress, 
                        string Device_ID)
   {
          string sql = "UPDATE CTS_VehicleLastPosition SET [UTCDate] = @UTCDate, [UTC_Time] = @UTC_Time, [NS_Indicator] = @NS_Indicator, [Latitude] = @Latitude, [EWIndicator] = @EWIndicator, [Longtitude] = @Longtitude, [Speed] = @Speed, [Processed] = @Processed, [Near_ByLocation] = @Near_ByLocation, [Near_ByLocation_AR] = @Near_ByLocation_AR, [Gis_dataID] = @Gis_dataID, [address] = @address, [ar_adress] = @ar_adress WHERE [Device_ID] = @Device_ID";
  
          SqlCommand cmd = new SqlCommand(sql);
          cmd.Parameters.AddWithValue("@UTCDate", UTCDate);
          cmd.Parameters.AddWithValue("@UTC_Time", UTC_Time);
          cmd.Parameters.AddWithValue("@NS_Indicator", NS_Indicator);
          cmd.Parameters.AddWithValue("@Latitude", Latitude);
          cmd.Parameters.AddWithValue("@EWIndicator", EWIndicator);
          cmd.Parameters.AddWithValue("@Longtitude", Longtitude);
          cmd.Parameters.AddWithValue("@Speed", Speed);
          cmd.Parameters.AddWithValue("@Processed", Processed);
          cmd.Parameters.AddWithValue("@Near_ByLocation", Near_ByLocation);
          cmd.Parameters.AddWithValue("@Near_ByLocation_AR", Near_ByLocation_AR);
          cmd.Parameters.AddWithValue("@Gis_dataID", Gis_dataID);
          cmd.Parameters.AddWithValue("@address", address);
          cmd.Parameters.AddWithValue("@ar_adress", ar_adress);
          cmd.Parameters.AddWithValue("@Device_ID", Device_ID);
          return cmd;
  }
  
  public int ExecuteNQuery(SqlCommand cmd, bool isShowError, 
                      DataAccess.ConnectionStringType CountryCode, string deviceID, 
                      int retry, string functionCallName)
  {
          ConnectionManager Connection = new ConnectionManager();
          try
          {
              Connection.GetConnection(CountryCode);
              if ((Connection.con == null) || (Connection.con.State != ConnectionState.Open))
              {
                  if (retry &lt;= 0) return 0;
                  else return ExecuteNQuery(cmd, isShowError, CountryCode, deviceID, retry - 1, functionCallName);
              }
              int rowsAffected = 0;
              cmd.Connection = Connection.con;
              rowsAffected = cmd.ExecuteNonQuery();
              return rowsAffected;
          }
          catch (SqlException sqlexception)
          {
              if (isShowError)
                  LEAMRALogger.Logger.WriteByDate("Logs\\SQLDBA\\" + functionCallName + "\\" + String.Format("{0:dd-MM-yyyy}", DateTime.Now), "SQLDBA", "SQLDBA_ERROR", "ExecuteNQuery Function: [deviceID: " + deviceID + " | retry: " + retry + "] " + sqlexception.ToString());
          }
          catch (Exception ex)
          {
              if (isShowError)
                  LEAMRALogger.Logger.WriteByDate("Logs\\SQLDBA\\" + functionCallName + "\\" + String.Format("{0:dd-MM-yyyy}", DateTime.Now), "SQLDBA", "SQLDBA_ERROR", "ExecuteNQuery Function: [deviceID: " + deviceID + " | retry: " + retry + "] " + ex.ToString());
          }
          finally
          {
              if ((Connection.con != null) &amp;&amp; (Connection.con.State == ConnectionState.Open))
              {
                  Connection.con.Close();
                  Connection.con.Dispose();
              }
  
              GC.Collect();
          }
          if (retry &lt;= 0) return 0;
          else return ExecuteNQuery(cmd, isShowError, CountryCode, deviceID, retry - 1, functionCallName);
  }
  </code></pre>