I am receiving the next random error "Transaction (Process ID XX) was deadlocked on lock resources with \ another process and has been chosen as the deadlock victim. Rerun the transaction"

This code is sending a massive number of emails, and updating 2 tables (one setting the notification flag to 1, and the another one storing the email sent - for each employee).

In the code looks like the emails are being sent correctly, and the problem is in the two queries to update the tables above.

How can I keep the parallelization feature (needed for performance reason) without loss some records updated? It is admissible to loss some performance if it is needed.

The next example code is:

var options = new ParallelOptions();
options.MaxDegreeOfParallelism = 3;

Parallel.ForEach(listEmployees, options, item =>
{
       MailMessage mail = new MailMessage()
       {
            ExecutionTaskId = 2,
            From = [email protected],
            To = item.email,
            BodyDesc = template.Body,
            SubjectDesc = template.Subject,
            Status = 0,
            CreatedBy = item.persNbr,
            CreatedDate = DateTime.UtcNow,
        };

        SendMail(mail);
});

private static void SendMail(MailMessage item)
{
      System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();

      ........

      msg.To.Add("[email protected]");
      msg.Body = "body";
      msg.BodyEncoding = System.Text.Encoding.UTF8;
      msg.From = new System.Net.Mail.MailAddress([email protected]);

      item.BodyDesc = "body";
      item.SubjectDesc = "subject";
      
      using (var smtpClient = new System.Net.Mail.SmtpClient(SettingsRepository.GetSetting("WEB_SMTP")))
      {
            smtpClient.Send(msg);
            item.Status = 1;
            item.SentDate = DateTime.Now;

            if (item.ObjectTable.Contains("var_control"))
            {
                 psn.NotificationSent = 1;
                 MailRepository.UpdatePayslipNotification(psn);
                 MailRepository.Update(item);
            }
            else
            {
                  p6n.NotificationSent = 1;
                  MailRepository.UpdateP60Notification(p6n);
                  MailRepository.Update(item);
            }
      }
}

public static void UpdatePayslipNotification(var var1)
{
    .........
    builder.Clear();
    builder.AppendLine("Update [example].[table_example]");
    builder.AppendLine("SET [example].[table_example].[NotificationSent] = "   1);
    builder.AppendLine("WHERE [example].[table_example].[Id] = "   var1.Id);
    
    using (var sqlCommand = new SqlCommand())
    {
          sqlCommand.Connection = sqlConnection;
          sqlCommand.Transaction = sqlTransaction;
          sqlCommand.CommandTimeout = 0;
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = builder.ToString();

          sqlCommand.ExecuteNonQuery();
     }
     ........
}

public static void Update(MailMessage mail)
{
    .........
    builder.Clear();
    builder.AppendLine("delete from [example].[MailTemp]");

    using (var sqlCommand = new SqlCommand())
    {
          sqlCommand.Connection = sqlConnection;
          sqlCommand.Transaction = sqlTransaction;
          sqlCommand.CommandTimeout = 0;
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = builder.ToString();

          sqlCommand.ExecuteNonQuery();
    }

    using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection, 
    SqlBulkCopyOptions.Default, sqlTransaction))
    {
         sqlBulkCopy.DestinationTableName = "[example].[MailTemp]";
         sqlBulkCopy.BulkCopyTimeout = 0;

         sqlBulkCopy.WriteToServer(dataTable);
    }

    builder.Clear();
    builder.AppendLine("Update [MailMessage]");
    builder.AppendLine("SET [MailMessage].[To]=[example].[MailTemp]. 
    [To],[MailMessage].[Status]=[example].[MailTemp].[Status], 
    [MailMessage].[SentDate]=[example].[MailTemp].[SentDate], 
    [MailMessage].[ErrorMessage]=[example].[MailTemp].[ErrorMessage], 
    [MailMessage].[SubjectDesc]=[example].[MailTemp].[SubjectDesc], 
    [MailMessage].[BodyDesc]=[example].[MailTemp].[BodyDesc], 
    [MailMessage].[From]=[example].[MailTemp].[From]");
    
    builder.AppendLine("FROM [example].[MailMessage] INNER JOIN 
    [example].[MailTemp] ON [example].[MailMessage].[Id]= 
    [example].[MailTemp].[Id]");

    using (var sqlCommand = new SqlCommand())
    {
          sqlCommand.Connection = sqlConnection;
          sqlCommand.Transaction = sqlTransaction;
          sqlCommand.CommandTimeout = 0;
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = builder.ToString();

          sqlCommand.ExecuteNonQuery();
    }

    sqlTransaction.Commit();
}

CodePudding user response：

Since the time-consuming operation is sending the email, and updating the database is relatively fast, you could serialize the database-updating operations by using a lock:

object locker = new();
Parallel.ForEach(listEmployees, options, item =>
{
    MailMessage mail = new() { /*...*/ };
    SendMail(mail);
    lock (locker)
    {
        UpdateDataBase(mail);
    }
});

In the above example a dedicated object locker is used. You could also use as locker the listEmployees or the options, provided that you don't use the same instance anywhere else for unrelated synchronization purposes.