sql server deadlock case

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.

the sequence of SQL order is the following, for each process in an explicit transaction :

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

each process work on a distinct data set and have no common values for "CUSTNUMBER"

the deadlock occurs in this case :

spid 1 : select custid... for unique value 1

spid 2 : select custid... for unique value 2

spid 1 : insert unique value 1

spid 2 : insert unique value 2

spid 2 : select custid again for value 2 <--- Deadlock Victim !

spid 1 : select custid again for value 1

The deadlock graph show that the problem occurs on the unique index on CUSTNUMBER

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

I have no idea to explain that, maybe someone can help me ?

try using OUTPUT to get rid of the final SELECT:

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) OUTPUT INSERTED.CUSTID values('unique value')
                            --^^^^^^^^^^^^^^^ will return a result set of CUSTIDs

commit

OR

DECLARE @x table (CUSTID  int)
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) OUTPUT INSERTED.CUSTID INTO @x values('unique valu')
                            --^^^^^^^^^^^^^^^^^^^^^^ will store a set of CUSTIDs
                            --                       into the @x table variable

commit

I have no explanation to the deadlock only another way of doing what you are doing using merge and output. It requires that you use SQL Server 2008 (or higher). Perhaps it will take care of your deadlock issue.

declare @dummy int;

merge CUSTOMERS as T
using (select 'unique value') as S(CUSTNUMBER)
on T.CUSTNUMBER = S.CUSTNUMBER
when not matched then
  insert (CUSTNUMBER) values(S.CUSTNUMBER)
when matched then
   update set @dummy = 1
output INSERTED.CUSTID;

This will return the newly created CUSTID if there was no match and the already existing CUSTID if there where a match for CUSTNUMBER.

It would be best if you post the actual deadlock graph (the .xml file, not the picture!). W/o that noone can be sure, but is likely that you see a case of the read-write deadlock that occurs due to the order of using vs. applying updates to the secondary indexes. I cannot reommend a solution w/o seeing the deadlock graph and the exact table schema (clustered index and all non-clustered indexes).

On a separate note the SELECT->if not exists->INSERT pattern is always wrong under concurrency, there isn't anything to prevent two threads from trying to insert the same row. A much better patter is to simply insert always and catch the duplicate key violation exception that occurs (is also more performant). As for your second SELECT, use OUTPUT clause as other have already suggested. so basically this whole ordeal can be reduced an insert int a try/catch block. MERGE will also work.

An alternative to using output is replacing the last select with a select scope_identity() if the CUSTID column is an identity column.