2

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 ?

Improve this question
1
  • 1
    I agree that using the OUTPUT clause will probably solve this but what isolation level are your transactions running at? What type of locks are being taken out? row locks or page locks? What is your table definition including indexes and foreign keys? Can you post the XML deadlock graph? Commented Aug 30, 2011 at 17:01

4 Answers 4

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

5 Comments

why MERGE? OP's code makes no mention of an UPDATE, only INSERT?
@KM - Well the merge statement does not do an update. It just fakes an update to get the CUSTID in the INSERTED table. And second I used my crystal ball to try to figure out what the OP's code actually does. I did not see any reason for the first select statement unless it was there to find a row that might exist. The comment of the next statement also suggests that OP's problem is when there is no match with the first select statement. So this merge does it all in one go. If there is no match add a row and return the ID otherwise fake an update and return the "found" ID.
I agree it is difficult to understand the OP's intent. The way I see the OP's code, it selects from CUSTOMERS but inserts into CUST. I'm not sure why, possible the insert is meant to be the same table as the select.
@KM - I did not notice the different table names. If it is not the same table both the code and the deadlock is even more confusing.
hello, i'm sorry but it is the same table (I cannot publish the exact table name and I made a mistake when changing the names!)
2

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.

Improve this answer

Comments

0

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

Improve this answer

1 Comment

i will try this, it requires minor changes in the source code ! thanks a lot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.