Sql-server – Insert if not exists, concurrently

concurrencysql serversql-server-2008

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.

Best 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);
Related Question