A transaction (process identifier 84) was blocked when resources were locked by another process and was selected as a victim of a deadlock

Question

I developed a monitoring application. So I used the timer function to check some values ​​in the SQL table.

although there are so many functions, it gives the following error for a single function called getLogEntry ()

message>Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</message> <innerMessage> </innerMessage> <source>.Net SqlClient Data Provider</source> <stackTrace>at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at ShiftAlertSystem.DBAccess.getLogEntry(Int32 nEventLogIdn, connections cn)</stackTrace> <createdAt>2012/06/18 13:10:47</createdAt> 

This is a function implementation

 public LogEntry getLogEntry(int nEventLogIdn, connections cn) { lock (_objLock) { LogEntry lgEntObj = new LogEntry(); SqlConnection NewCon3 = new SqlConnection(); SqlCommand newCmd2 = null; SqlDataReader dr = null; try { string connectString; // Configuration config = ConfigurationManager.u string DataSource = cryptIT.Decrypt(cn.DataSource_bio); string initialCatalog = cryptIT.Decrypt(cn.InitialCatalog_bio); string user = cryptIT.Decrypt(cn.user_bio); string password = cryptIT.Decrypt(cn.password_bio); bool intergratedSecurity = cn.IntegratedSecurity_bio; if (intergratedSecurity) { connectString = "Data Source=" + DataSource + ";Initial Catalog=" + initialCatalog + ";Integrated Security=True"; } else { connectString = "Data Source=" + DataSource + ";Initial Catalog=" + initialCatalog + ";User ID=" + user + ";Password=" + password; } NewCon3 = new SqlConnection(connectString); NewCon3.Open(); newCmd2 = NewCon3.CreateCommand(); newCmd2.Connection = NewCon3; newCmd2.CommandType = CommandType.Text; newCmd2.CommandText = @" SELECT [nUserID] ,[sUserName] ,dateadd(s,[nDateTime],'1970/1/1') AS LogDateTime ,[nEventIdn] ,[nTNAEvent] ,[TB_READER].[nReaderIdn] ,[sName] FROM [TB_EVENT_LOG] ,[TB_USER] ,[TB_READER] WHERE [nEventLogIdn] = " + nEventLogIdn + @" AND [TB_EVENT_LOG].[nUserID] = [TB_USER].[sUserID] AND [nFlag]= 1 AND [TB_EVENT_LOG].[nReaderIdn]=[TB_READER].[nReaderIdn]" ; dr = newCmd2.ExecuteReader(); if (dr != null && dr.Read()) { lgEntObj.nUserID = dr.GetInt32(0); lgEntObj.nUserName = dr.GetString(1); lgEntObj.LogDateTime = dr.GetDateTime(2); lgEntObj.nEventIdn = dr.GetInt32(3); lgEntObj.nTNAEvent = dr.GetInt16(4); lgEntObj.nReaderIdn = dr.GetInt32(5); lgEntObj.sName = dr.GetString(6); } dr.Close(); newCmd2.Dispose(); // NewCon.Close(); NewCon3.Close(); return lgEntObj; } catch (Exception exc) { CenUtility.ErrorLog.CreateLog(exc); return null; } finally { if (dr != null) dr.Close(); if(newCmd2 != null) newCmd2.Dispose(); NewCon3.Close(); } } } 

Thanks in advance

Answer 1

You can refer to this question for some useful suggestions.

I use the following pattern to retry the database; in this case, we return a DataTable, but the template is the same regardless; you find a SqlDeadlock or Timeout based on a SqlException Number and try again up to a maximum number n times.

  public DataTable DoSomeSql(int retryCount = 1) { try { //Run Stored Proc/Adhoc SQL here } catch (SqlException sqlEx) { if (retryCount == MAX_RETRY_COUNT) //5, 7, Whatever { log.Error("Unable to DoSomeSql, reached maximum number of retries."); throw; } switch (sqlEx.Number) { case DBConstants.SQL_DEADLOCK_ERROR_CODE: //1205 log.Warn("DoSomeSql was deadlocked, will try again."); break; case DBConstants.SQL_TIMEOUT_ERROR_CODE: //-2 log.Warn("DoSomeSql was timedout, will try again."); break; default: log.WarnFormat(buf.ToString(), sqlEx); break; } System.Threading.Thread.Sleep(1000); //Can also use Math.Rand for a random interval of time return DoSomeSql(asOfDate, ++retryCount); } } 

Answer 2

Your request has stalled with another request. Another request is most likely an insert , update or delete request, since select itself does not tend to deadlock.

If you don't care too much about consistency, you can use the with (nolock) hint:

 FROM [TB_EVENT_LOG] with (nolock) ,[TB_USER] with (nolock) ,[TB_READER] with (nolock) 

This will cause your request to not place locks. A request without locks will not result in locks. The disadvantage is that it can return inconsistent data when it starts at the same time as the change request.