title: sql server deadlock case
body: <p>I have a deadlock problem between 2 processes that insert data in the same table
  These 2 processes run exactly the same SQL orders on a table with a primary key (identity) and a unique index.</p>
  
  <p>the sequence of SQL order is the following, for each process in an explicit transaction :</p>
  
  <pre><code>begin trans
  
  select CUSTID from CUSTOMERS where CUSTNUMBER='unique value'
  
  ------- the row is never found in this case so... insert the data
  insert into CUST(CUSTNUMBER) values('unique value')
  
  ------- then we must read the value generated for the pk
  select CUSTID from CUSTOMERS where CUSTNUMBER='unique value'
  
  commit
  </code></pre>
  
  <p>each process work on a distinct data set and have no common values for "CUSTNUMBER"</p>
  
  <p>the deadlock occurs in this case :</p>
  
  <p>spid 1 : select custid... for unique value 1</p>
  
  <p>spid 2 : select custid... for unique value 2</p>
  
  <p>spid 1 : insert unique value 1</p>
  
  <p>spid 2 : insert unique value 2</p>
  
  <p>spid 2 : select custid again for value 2   &lt;--- Deadlock Victim !</p>
  
  <p>spid 1 : select custid again for value 1</p>
  
  <p>The deadlock graph show that the problem occurs on the unique index on CUSTNUMBER</p>
  
  <p>The killed process had a lock OwnerMode:X and was RequestMode:S on the unique index for the same HoBt ID.
  The winner process was OnwerMode:X and RequestMode:S for the same HoBt ID</p>
  
  <p>I have no idea to explain that, maybe someone can help me ?</p>