3

I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
    insert into table1(id, value1,value2...)
    select (@id, @value1, @value2...)
 end 

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

13

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
  set value = @value
  where id = @id;

if (@@ROWCOUNT = 0)
begin
  insert dbo.table1(id, value1,value2...)
    values(@id, @value1, @value2...);
end 

commit transaction;
  • Appreciate your answers! How about using IF EXISTS (select top 1 1 from table1 where id @id) then update and else Insert? – blue Mar 20 at 13:11
  • 1
    @blue that pattern requires an extra access to the table (in the case where it exists). IF EXISTS essentially does the exact same thing the update does to try to locate the row - why do that twice? – Aaron Bertrand Mar 20 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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