Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Question

I have developed a Monitoring application. So there i have used a Timer function in order to check some values in a SQL Table.

though there are so many function it gives an following error for one 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 the implementation of the function

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

Your query deadlocked with another query. The other query is most likely an insert, update or delete query, since select alone 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)

That will cause your query not to place locks. A query without locks will not cause deadlocks. The downside is that it might return inconsistent data, when it runs at the same time as a modification query.

Answer 2

You may want to refer to this question for some more helpful suggestions.

I use the following pattern for database retries; in this instance, we return a DataTable but the pattern is the same regardless; you detect a SqlDeadlock or Timeout based on the SqlException Number, and retry, up to a maximum number of 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);
        }
    }