如何摆脱SQL Server 2005和C＃应用程序中的死锁？ [英] How to get rid of deadlock in a SQL Server 2005 and C# application?
问题描述
我在Windows服务的C＃中有一个代码，主要负责更新数据库表中的记录，但是我的日志中总是出现很多错误，所有错误都与资源死锁有关，
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,
这是错误：
System.Data.SqlClient.SqlException（0x80131904）：事务（进程
ID 57）被另一个进程锁定在锁资源上，并已选择
作为死锁受害者。重新运行事务。在
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（
System.Data.SqlClient.SqlConnection.OnError（SqlException异常，
布尔值breakConnection，Action1 wrapCloseInAction） TdsParserStateObject1完成，Int32超时，Task&任务，
stateObj，
处的布尔值callerHasConnectionLock，布尔值asyncClose）System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，
SqlCommand cmdHandler，SqlDataReader dataStream，
BulkCopySimpleResultSet bulkCopyHandler，Tds在
处的b $ b stateObj，Boolean&dataReady）System.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，
RunBehavior runBehavior，String resetOptionsString）在
System.Data.SqlClient.SqlCommand中。 RunExecuteReaderTds（CommandBehavior
cmdBehavior，RunBehavior runBehavior，布尔returnStream，布尔
异步，Int32超时，Task& task，布尔asyncWrite）位于
System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior
厘米dBehavior，RunBehavior runBehavior，布尔值returnStream，字符串
方法，TaskCompletionSource
处的
布尔asyncWrite）System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（TaskCompletionSource`1
完成，字符串methodName，布尔sendToPipe，Int32超时，
布尔asyncWrite）
System.Data.SqlClient.SqlCommand.ExecuteNonQuery（）at
WheelTrackListener.DataAccess.SQLDBA.ExecuteNQuery（SqlCommand cmd，
Boolean isShowError，ConnectionStringType CountryCode，字符串
deviceID，Int32重试，String functionCallName）
ClientConnectionId：e45e4cf1-a113-46b7-b9b5-dc5ee8111406
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
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 completion, Int32 timeout, Task& 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
现在，我想问一下，可以我尝试还是检查资源是否被锁定？以及如果锁定了如何等待更新，直到发布？
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?
这是我当前的代码：
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);
}
推荐答案
我看不到代码中的显式事务作用域，因此我不知道在进行更新时已经使用了哪些锁；也不清楚您正在使用什么隔离级别。但是，在这种情况下最常见的情况是，在同一事务中，较早时您已在要稍后更新的同一行上发出了选择（读锁）。如果两个事务试图做同一件事，这将导致锁升级，并可能导致死锁：
I can't see any explicit transaction scope in your code, so I do not know what locks are already in place when you are doing your update; also it is not clear what isolation level you are using. But the most common scenario in this type of situation is that earlier in the same transaction you have issued a select (read lock) on the same rows that you are trying to update later. This will cause a lock escalation, and can result in a deadlock if two transactions are trying to do the same thing:
- 事务A：选择具有读取锁定
- 事务B：选择具有读取
锁定 - 事务A：更新-希望将其读取锁定升级为
写入锁定，但必须等待事务B释放其读取的
锁定 - 事务B：更新-希望将其读取锁定升级为
写锁，但必须等待事务A释放读取的
锁。
- Transaction A: select with read lock
- Transaction B: select with read lock
- Transaction A: update - wants to escalate its read lock to a write lock, but has to wait for transaction B to release its read lock
- Transaction B: update - wants to escalate its read lock to a write lock, but has to wait for transaction A to release its read lock.
宾果！死锁，因为A和B都在互相等待以释放其现有的读锁，然后才能进行更新。
Bingo! deadlock as both A and B are waiting on each other to release their existing read locks before they can do their update.
为防止这种情况，您需要在选择的内容中添加上锁提示，例如，
To prevent this you need an updlock hint in your select, e.g.,
select * from table with (updlock) where blah blah
这将确保您的选择使用写锁而不是读锁，这将防止并发事务之间的锁升级。
This will ensure your select uses a write lock instead of a read lock, which will prevent lock escalation between concurrent transactions.
这篇关于如何摆脱SQL Server 2005和C＃应用程序中的死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
