Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having concurrency issues with my inserts in a stored procedure. The relevant part of the procedure is this:

select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
    insert into table1 (othervalue) values (@_othervalue)
    select @_id = Id from table1 where othervalue = @_othervalue
END

When we run 3 or 4 of these stored proc's concurrently we get multiple inserts on occasion.

I am planning on fixing this like so:

insert into table1 (othervalue) 
    select TOP(1) @_othervalue as othervalue from table1 WITH(UPDLOCK) 
    where NOT EXISTS ( select * from table1 where othervalue = @_othervalue )

select @_id = Id from table1 where othervalue = @_othervalue

The question is, is that how to concurrently insert without duplicates in sql server? The fact that i have to use TOP to only insert once disturbs me.

share|improve this question
1  
You don't have to use TOP. Remove the FROM table reference from the SELECT statement. –  ErikE Feb 18 '12 at 7:31
    
Possible duplicate of stackoverflow.com/questions/3407857/… –  GSerg Feb 19 '12 at 1:30
    
    
@GSerg I think you are correct. –  Chris Feb 21 '12 at 14:12

3 Answers 3

Use a unique constraint like @StanleyJohns recommends. Then use BEGIN TRY END TRY around your insert statement.

select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
    BEGIN TRY
        insert into table1 (othervalue) values (@_othervalue)
        select @_id = Id from table1 where othervalue = @_othervalue        
    END TRY
    BEGIN CATCH
        select @_id = Id from table1 where othervalue = @_othervalue        
    END CATCH
END
share|improve this answer

You could use a merge statement with serializable hint.

merge table1 with (serializable) as T 
using (select @_othervalue as othervalue) as S
on T.othervalue = S.othervalue
when not matched then
  insert (othervalue) values (othervalue);
share|improve this answer
    
Did you stress test your approach from two or more connections? –  AlexKuznetsov Feb 18 '12 at 22:46
1  
@AlexKuznetsov - I did it a while ago for another question on SO. I used two tabs in SSMS. First tested the insert ... where not exist ... pattern and found that you can get deadlocks and key violations so there it was necessary to use updlock and serializable. I then tested the merge statement and thought it would handle things a bit better and it did because there where no deadlocks but I still had to use serializable to not have key violations. –  Mikael Eriksson Feb 18 '12 at 23:18

If you do not want duplicates on the 'othervalue' column, you can do so by creating a unique constraint on that column. The query would be:

 ALTER TABLE table1
 ADD CONSTRAINT unique_c_othervalue UNIQUE(othervalue)

This would throw back an error if a query tried to insert a duplicate value into the 'othervalue' column.

share|improve this answer
    
How would that work if the unique constraint is a two row tuple? –  Chris Feb 18 '12 at 21:05
1  
@Chris How do you have a unique constraint that spans rows? –  Aaron Bertrand Feb 19 '12 at 0:04
    
@Aaron I probably have my terminology off, but we have two rows that together need to be unique. I don't think it is enforced in our schema. –  Chris Feb 21 '12 at 14:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.