title: Avoid deadlock for concurrent delete
body: <p>I have a table called <code>Products</code> with many columns. It is a temporary table used for reporting purposes. The data will be processed to this table concurrently by multiple user requests. There are separate stored procedures to make DML operations to this table.</p>
  
  <p>Table Structure:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Products (
    instance uniqueidentifier,
    inserted datetime,
    col1, 
    col2,
    ...
  )
  </code></pre>
  
  <p>The <code>inserted</code> column will be populated with <code>GETDATE()</code> to contain the time when each row was inserted and the <code>instance</code> column will contain the value from <code>newid()</code>. One user request will have one unique id but may have million rows. The below are the queries which will be executed concurrently, which causing the deadlock. Please advise me</p>
  
  <p>Query 1:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
  DELETE P 
  FROM Products (NOLOCK) 
  WHERE instance = 'XXXX-xxx-xxx-xx'
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
  DELETE P 
  FROM Products (NOLOCK) 
  WHERE inserted &lt;= DATEADD(hh, -10, GETDATE())
  </code></pre>
  
  <p>Note: The nonclustered index is created on instance column.</p>
  
  <p>Please advice me which lock I can use in this scenario. </p>
  
  <p>Note I couldnt able to primary key as it is consuming time when I insert 10 million rows to the table (this for one transaction; there are 20 concurrent transations). 
  The report should be generated sooner. And my procedure has multiple 35 DML statments, there are around 15 DELETE statements for instance column with other columns( <code>DELETE FROM table WHERE instance = @instance AND col1 = @col1</code>).</p>