title: Preventing deadlocks in SQL Server
body: <p>I have an application connected to a SQL Server 2014 database that combines several rows into one. There are no other connections to this database while the application is running.</p>
  
  <p>First, select a chunk of rows within a specific time span. This query uses a non-clustered seek (TIME column) merged with a clustered lookup.</p>
  
  <pre><code>select ...
  from FOO
  where TIME &gt;= @from and TIME &lt; @to and ...
  </code></pre>
  
  <p>Then, we process these rows in c# and write changes as a single update and multiple deletes, this happens many times per chunk. These also use non-clustered index seeks.</p>
  
  <pre><code>begin tran
  
  update FOO set ...
  where NON_CLUSTERED_ID = @id
  
  delete FOO where NON_CLUSTERED_ID in (@id1, @id2, @id3, ...)
  
  commit
  </code></pre>
  
  <p>I am getting deadlocks when running this with multiple parallel chunks. I tried using <code>ROWLOCK</code> for the <code>update</code> and <code>delete</code> but that caused even more deadlocks than before for some reason, even though there are no overlaps between chunks.</p>
  
  <p>Then I tried <code>TABLOCKX, HOLDLOCK</code> on the <code>update</code>, but that means I can't perform my <code>select</code> in parallel so I'm losing the advantages of parallelism.</p>
  
  <p>Any idea how I can avoid deadlocks but still process multiple parallel chunks?</p>
  
  <p>Would it be safe to use <code>NOLOCK</code> on my <code>select</code> in this case, given there is no row overlap between chunks? Then <code>TABLOCKX, HOLDLOCK</code> would only block the <code>update</code> and <code>delete</code>, correct?</p>
  
  <p>Or should I just accept that deadlocks will happen and retry the query in my application?</p>
  
  <p><strong>UPDATE</strong> (additional information): All deadlocks so far have happened in the <code>update</code> and <code>delete</code> phase, none in the <code>select</code>. I'll try to get some deadlock logs up if I can't get this solved today (the correct trace flags weren't enabled before).</p>
  
  <p><strong>UPDATE</strong>: These are the two arrangements of deadlocks that occur with <code>ROWLOCK</code>, they both refer only to the <code>delete</code> statement and the non-clustered index it uses. I'm not sure if these are the same as the deadlocks that occur without any table hints as I wasn't able to reproduce any of those.</p>
  
  <p><a href="https://i.stack.imgur.com/HSX00.png" rel="noreferrer"><img src="https://i.stack.imgur.com/HSX00.png" alt="Deadlock 1"></a>
  <a href="https://i.stack.imgur.com/Q3hWr.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Q3hWr.png" alt="Deadlock 2"></a></p>
  
  <p>Ask if there's anything else needed from the .xdl, I'm a bit weary of attaching the whole thing.</p>