How to fix "Deadlock" issue?

-2

Here I am using Enterprise library and using "Transaction Scope" for rollback data if any issue happen during my execution. Now what is happening, when two processes (same console application) are running at same time. Deadlock issue is happening, I am not getting where is the issue. Below is the error that I am getting. Error is coming while two processing trying to update data in same table.

    using (TransactionScope scope = new TransactionScope())
    {
        /* Some code */

        DAL.UpdateStudentsDataInClassTable(classId,studentDetails);

        scope.Complete();
    }
    catch (Exception ex)
    {
        var msg = "Error occured while updating Student Details which have Id - {0}".Fill(studentDetail.StudentId);
        log.Error(msg, ex);                            
        continue;
    }
}

Public int UpdateStudentsDataInMasterTable(int classId,List<Student> studentDetails)
{
   foreach (var studentDetail in studentDetails)
   {
      //Here first we are re-allocating class number and Roll number
      db.ExecuteSqlNonQuery(@"UPDATE [dbo].[StudentDetails] SET [ClassId]= @p1,[RollNo] = @p2 WHERE masterclassId= @p3 AND RollNo = @p4", classId, studentDetail.SequenceNo, studentDetail.MasterClassId, studentDetail.SequenceNo);
   }

   //For divert primary key issue we are using starting number as 90001 and then we will change that numbering again
   db.ExecuteSqlNonQuery(@"UPDATE [dbo].[StudentDetails] SET  [RollNo] = ([RollNo]-90000)  WHERE ClassId= @p1", classId);//Here we are getting error when two process execute at same time.
}  
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 1102) was deadlocked on lock | communication buffer 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.SqlInternalConnection.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, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
   at Test.Service.Extensions.ExecuteSqlNonQuery(Database db, String sql, Object[] parameterValues)
   at Test.Service.DAL.UpdateStudentsDataInClassTable(Int32 classId, List`1 studentDetails)
   at Test.Service.TestJobMonitor.SubmitStudentData(Boolean isBundle, String className)
Error Number:1205,State:51,Class:13
c#
sql-server
console-application
deadlock
asked on Stack Overflow Feb 15, 2019 by Vijay Mungara • edited Feb 15, 2019 by GSerg

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0