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();
}
--- Partial Solution ---
(The original problem was solved using MaxDegreeOfParallelism = 2, but there are a few added problems because of parallel DB reading are returning fake values. Trying to lock the parallel reading the program returns an generic error about parallel situation:
One or more errors occurred.
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Task.Wait()
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally)
at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IList`1 list, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally)
object locker1 = new object();
object locker2 = new object();
var options = new ParallelOptions();
options.MaxDegreeOfParallelism = 2;
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, locker1, locker2);
});
private static void SendMail(MailMessage item, object locker1, object locker2)
{
System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
........
lock (locker1)
{
// Get Access to DB - Read a record
}
using (var smtpClient = new System.Net.Mail.SmtpClient(SettingsRepository.GetSetting("WEB_SMTP")))
{
smtpClient.Send(msg);
item.Status = 1;
item.SentDate = DateTime.Now;
lock (locker)
{
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);
}
}
}
}
How can avoid these fake results from DB? How can I lock DB access using TheodorZoulias solution provided?
Many Thanks
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.