How to get rid of deadlock in SQL Server 2005 and C # application?

Question

I have code in C # for a Windows service, which is mainly responsible for updating records in a table in my database, but there are always a lot of errors in my log, all errors are related to a resource deadlock,

This is mistake:

System.Data.SqlClient.SqlException (0x80131904): The transaction (process ID 57) was locked with resource locking by another process and was selected as the victim of a deadlock. Restart the transaction. in 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, TaskCompletionSource 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 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, TaskCompletionSource Int32 ymout, Task & task, Boolean asyncWrite) with System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) with System.Dataqqlient () in WheelTrackListener.DataAccess.SQLDBA.ExecuteNQuery (SqlCommand cmd, Boolean isShowError, ConnectionStringType CountryCode, String deviceID, Int32 retry, String functionCallName) ClientConnectionId: e45e4cf1-a113-46b7-bb8-b9

Now, I want to ask if I can try or check if the resource is locked? and if blocked, how to wait for an update until it is released?

Here is my current 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 <= 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); } 

Answer 1

I don’t see any explicit transaction scope in your code, so I don’t know which locks are already set when you perform the update; It is also not clear what level of isolation you are using. But the most common scenario in this type of situation is that earlier in the same transaction you issued select (read lock) for the same rows that you are trying to update later. This will cause escalation of the lock and can lead to a deadlock if two transactions try to do the same thing:

1. Transaction A: Select with Read Lock
2. Transaction B: Select with Read Lock
3. Transaction A: update - wants to increase its read lock to write lock, but must wait until transaction B releases the read lock
4. Transaction B: update - wants to translate its read lock into a write lock, but must wait until transaction A releases the read lock.

Bingo! deadlock, as both A and B are waiting for each other to release their existing read locks before they can complete their update.

To prevent this, you will need an update hint, for example,

 select * from table with (updlock) where blah blah 

This ensures that your choice uses write locks instead of read locks, which will prevent the lock from escalating between concurrent transactions.

Answer 2

Although you can check the lock, you cannot guarantee that by the time the next statement exits, any other process has not removed the lock. Possible solutions in order of preference:

1) Always indicate the tables in the same order within transactions.

2) Reply @ShellShock

3) Trap blocking errors and handle it.