title: Parallel.ForEach used with NHibernate resulting in SQL Server locks
body: <p>Firstly, I am not much of an expert in multi-threading and parallel programming.</p>
  
  <p>I am trying to optimize the performance of a legacy application (<code>.Net 4</code>, <code>NHibernate 2.1</code>).</p>
  
  <p>**So far, upgrading <code>NHibernate</code> is not a priority, but is in the pipeline.</p>
  
  <p>Over time, performance has become a nightmare with the growth of data. One item I have seen is a <code>Parallel.ForEach</code> statement that calls a method that fetches and updates a complex entity(with multiple relationships - propeties &amp; collections).</p>
  
  <p>The piece of code has the following <strong>form</strong> (simplified for clarity):</p>
  
  <pre><code>void SomeMethod(ICollection&lt;TheClass&gt; itemsToProcess)
  {
     Parallel.ForEach(itemsToProcess, item =&gt; ProcessItem(item);
  }
  
  TheClass ProcessItem(TheClass i)
  {
     var temp = NHibernateRepository.SomeFetchMethod(i);
     var result = NHibernateRepository.Update(temp);
     return result;
  }
  </code></pre>
  
  <p>SQL Server intermittently reports database lock errors with the following error:</p>
  
  <p><code>Transaction (Process ID 20) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction</code></p>
  
  <p>I suspect it is due to some race condition happening leading up to a deadlock, even if <code>ISessions</code> are separate.</p>
  
  <p>The <code>ICollection&lt;TheClass&gt;</code> can have up to 1000 items and each with properties and sub-collections that are processed, generating many <code>SELECT</code> and <code>UPDATE</code> statements (confirmed using 'NHibernate Profiler')</p>
  
  <p><strong>Is there a better way to handle this in a parallel way, or shall I refactor the code to a traditional loop?</strong></p>
  
  <p>I do know that I can alternatively implement my code using:</p>
  
  <ol>
  <li>A <code>foreach</code> loop in the same <code>ISession</code> context </li>
  <li>With a Stateless Session </li>
  <li>With <code>Environment.BatchSize</code> set to a reasonable value</li>
  </ol>
  
  <p>OR</p>
  
  <ol>
  <li>Using SQL BulkCopy</li>
  </ol>
  
  <p>I have also read quite a bit of good info about SQL Server deadlocks and  <code>Parallel.ForEach</code> being an easy pitfall:</p>
  
  <ol>
  <li><a href="https://stackoverflow.com/a/5391427/919426">SQL Transaction was deadlocked</a></li>
  <li><a href="https://stackoverflow.com/a/8636297/919426">Using SQL Bulk Copy as an alternative</a></li>
  <li><a href="http://Potential%20Pitfalls%20in%20Data%20and%20Task%20Parallelism" rel="nofollow noreferrer">Potential Pitfalls in Data and Task Parallelism</a></li>
  <li><a href="https://stackoverflow.com/a/10035988/919426">Multi threading C# application with SQL Server database calls</a></li>
  </ol>