Insert and Update pattern for multiple threads application with SQL Server
Hi,
Have you ever try to insert a row in to a SQL table if the key does not exist and update a row if a key exists. Well this is very common scenario.
Usually you would achieve this goal by writing SQL like:
if
exists (select * from TestTable where ID = @id)
begin
update TestTable set myCount = myCount+1 where ID = @idend
else
begin
insert into TestTable values (@id, 1)
end
This code will work fine for single threaded applications but will not work for multiple threads application. To solve this issue you need to handle primary key violations and fix up the transactional integrity of the batch.
So now let's see the solutions:
Option 1:
begin tran
if
exists (select * from TestTable with (updlock,serializable) where ID = @id)
begin
update TestTable set myCount = myCount+1 where ID = @id
end
else
begin
insert TestTable (ID, myCount) values (@id, 1)
end
commit
tran
Option 2:
begin
tran update TestTable with (serializable) set myCount = myCount + 1 where ID = @id if @@rowcount = 0
begin
insert TestTable (ID, myCount) values (@id,1)end
commit
tran
That's all.
Enjoy Rotem