title: In Update Sentences - Transaction (Process ID 64) was deadlocked
body: <p>I am receiving the next random error &quot;<em>Transaction (Process ID XX) was deadlocked on lock resources with \ another process and has been chosen as the deadlock victim. Rerun the transaction</em>&quot;</p>
  <p>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).</p>
  <p>In the code looks like the emails are being sent correctly, and the problem is in the two queries to update the tables above.</p>
  <p>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.</p>
  <p>The next example code is:</p>
  <pre><code>var options = new ParallelOptions();
  options.MaxDegreeOfParallelism = 3;
  
  Parallel.ForEach(listEmployees, options, item =&gt;
  {
         MailMessage mail = new MailMessage()
         {
              ExecutionTaskId = 2,
              From = example@example.com,
              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(&quot;to@example.com&quot;);
        msg.Body = &quot;body&quot;;
        msg.BodyEncoding = System.Text.Encoding.UTF8;
        msg.From = new System.Net.Mail.MailAddress(example@example.com);
  
        item.BodyDesc = &quot;body&quot;;
        item.SubjectDesc = &quot;subject&quot;;
        
        using (var smtpClient = new System.Net.Mail.SmtpClient(SettingsRepository.GetSetting(&quot;WEB_SMTP&quot;)))
        {
              smtpClient.Send(msg);
              item.Status = 1;
              item.SentDate = DateTime.Now;
  
              if (item.ObjectTable.Contains(&quot;var_control&quot;))
              {
                   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(&quot;Update [example].[table_example]&quot;);
      builder.AppendLine(&quot;SET [example].[table_example].[NotificationSent] = &quot; + 1);
      builder.AppendLine(&quot;WHERE [example].[table_example].[Id] = &quot; + 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(&quot;delete from [example].[MailTemp]&quot;);
  
      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 = &quot;[example].[MailTemp]&quot;;
           sqlBulkCopy.BulkCopyTimeout = 0;
  
           sqlBulkCopy.WriteToServer(dataTable);
      }
  
      builder.Clear();
      builder.AppendLine(&quot;Update [MailMessage]&quot;);
      builder.AppendLine(&quot;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]&quot;);
      
      builder.AppendLine(&quot;FROM [example].[MailMessage] INNER JOIN 
      [example].[MailTemp] ON [example].[MailMessage].[Id]= 
      [example].[MailTemp].[Id]&quot;);
  
      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();
  }
  </code></pre>
  <p><strong>--- Partial Solution ---</strong></p>
  <p>(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:</p>
  <pre class="lang-none prettyprint-override"><code>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)
  </code></pre>
  <pre><code>object locker1 = new object();
  object locker2 = new object();
  
  var options = new ParallelOptions();
  options.MaxDegreeOfParallelism = 2;
  
  Parallel.ForEach(listEmployees, options, item =&gt; 
  {
        MailMessage mail = new MailMessage()
        {
            ExecutionTaskId = 2,
            From = example@example.com,
            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(&quot;WEB_SMTP&quot;)))
        {
              smtpClient.Send(msg);
              item.Status = 1;
              item.SentDate = DateTime.Now;
  
              lock (locker)
              {
  
                  if (item.ObjectTable.Contains(&quot;var_control&quot;))
                  {
                       psn.NotificationSent = 1;
                       MailRepository.UpdatePayslipNotification(psn);
                       MailRepository.Update(item);
                  }
                  else
                  {
                       p6n.NotificationSent = 1;
                       MailRepository.UpdateP60Notification(p6n);
                       MailRepository.Update(item);
                  }
              }
        }
  }
  </code></pre>
  <p><strong>How can avoid these fake results from DB? How can I lock DB access using TheodorZoulias solution provided?</strong></p>
  <p>Many Thanks</p>