insert and update if a record exists else not , race condition

Question

if exists (select itemcode from item where itemcode=1120)
update item 
set itemname = 'laptop'
where itemcode = 1120

else 
insert into item (itemcode,itemname)
values (1120,'laptop')

it will be used by multiple users. will this query give a race condition. if yes, then how?what shall i use in place of this query?

possible duplicate of [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Apr 14 '11 at 11:38
also read http://technet.microsoft.com/en-us/library/bb522522.aspx — Mat, Apr 14 '11 at 11:41
Actually the duplicate I linked to isn't a true duplicate. I see you are doing an `upsert`. Yes there will be a race condition. In SQL Server 2008 you would use `Merge`. For 2005 see http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005 — Martin Smith, Apr 14 '11 at 11:53
@AndersUP - Yes I discovered that since too and link to that article in [the possible dupe](http://stackoverflow.com/a/3408211/73226) — Martin Smith, Mar 22 '12 at 10:54

DarkRob · Answer 1 · 2019-03-18T03:46:02.363

You may use transaction for this. Make sure to lock all your required tables in single transaction then release them.

begin transaction

begin try

    if exists (select itemcode from item where itemcode=1120)
    BEGIN
    update item 
    set itemname = 'laptop'
    where itemcode = 1120
    END

    else 
    BEGIN
    insert into item (itemcode,itemname)
    values (1120,'laptop')
    END

    commit transaction

end try

begin catch
  raiserror('Message here', 16, 1)
  rollback transaction
end catch

You can also give name to your transaction if you have multiple.

insert and update if a record exists else not , race condition

1 Answers1