title: Sql Sever 2008 Select and Delete deadlock on the same index
body: <p>I am experiencing an unclear sql server 2008 deadlock.</p>
  
  <p>I have two tables:</p>
  
  <pre><code>T1: ID – bigint (PK), JOBID – bigint, X- bigint
  T2: ID – bigint (PK), JOBID – bigint, X- bigint
  </code></pre>
  
  <p>The sql server error log describes the following:</p>
  
  <pre><code>Spid 233 is running this query: 
      SELECT TOP(500) FROM T1.X INNER JOIN T2 ON T1.X = T2.X WHERE T2.ID&gt;-1 AND T2.ID&lt;=10000
  Spid 234 is running this query: 
      DELETE FROM T2 WHERE (X=1) OR (X=7) OR ( X=1233) OR X(=7878) OR (X=33) OR (X=776) OR (X=112)
  Spid 233 is waiting for a Shared KEY lock on index t2.PK_T2.  (Spid 234 holds a conflicting X lock.)
  Spid 234 is waiting for an eXclusive KEY lock on index t2.PK_T2.  (Spid 233 holds a conflicting S lock.)
  </code></pre>
  
  <p>PK_X is a Clustered ascending index on the identity column (ID which is auto increment column).
  T2 also has another index which is a non clustered index on JOBID and X.</p>
  
  <p>How come both processes are owners of a lock on the same object (T2.PK_T2)?</p>
  
  <p>Please help,
  Doron</p>