我有一个窗口服务，在C＃代码它主要负责更新我的数据库表中的记录，但我总是得到了很多我的错误日志中，所有的错误是关于资源的僵局，
这是错误：
System.Data.SqlClient.SqlException（0x80131904）：事务（进程ID 57）中的死锁的锁资源与另一个进程和已被选作死锁牺牲品。 重新运行该事务。 在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，布尔breakConnection，动作
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& 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& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1完成后，的Int32超时，任务与任务 ，布尔asyncWrite）在System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（TaskCompletionSource`1完成，字符串方法名，布尔sendToPipe，的Int32超时，布尔asyncWrite）在System.Data.SqlClient.SqlCommand.ExecuteNonQuery（）在WheelTrackListener.DataAccess.SQLDBA .ExecuteNQuery（CMD的SqlCommand，布尔isShowError，ConnectionStringType COUNTRYCODE，字符串设备ID，重试的Int32，字符串functionCallName）ClientConnectionId：e45e4cf1-a113-46b7-b9b5-dc5ee8111406
现在，我想问一下，我可以做一个尝试或检查，如果资源被锁定？ 如果锁定如何等待更新直到它被释放？
这里是我当前的代码：
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 <= 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) && (Connection.con.State == ConnectionState.Open))
{
Connection.con.Close();
Connection.con.Dispose();
}
GC.Collect();
}
if (retry <= 0) return 0;
else return ExecuteNQuery(cmd, isShowError, CountryCode, deviceID, retry - 1, functionCallName);
}
