sql server deadlock case

Question

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 ?

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? — Martin Smith, Aug 30 '11 at 17:01

score 3 · Answer 1 · answered Aug 30 '11 at 15:54

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

score 2 · Answer 2 · answered Aug 30 '11 at 16:06

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.

answered Aug 30 '11 at 16:06

Mikael Eriksson

136,425
22
210
281

why MERGE? OP's code makes no mention of an UPDATE, only INSERT? – KM. Aug 30 '11 at 17:31
@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. – Mikael Eriksson Aug 30 '11 at 17:56
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. Aug 30 '11 at 18:11
@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. – Mikael Eriksson Aug 30 '11 at 18:19
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!) – Chris Aug 31 '11 at 07:14

score 2 · Answer 3 · answered Aug 30 '11 at 20:17

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.

score 0 · Answer 4 · answered Aug 30 '11 at 16:10

0

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

answered Aug 30 '11 at 16:10

Pent Ploompuu

5,364
1
27
47

i will try this, it requires minor changes in the source code ! thanks a lot – Chris Aug 31 '11 at 07:16

sql server deadlock case

4 Answers4

Linked