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)

    update TestTable set myCount = myCount+1  where ID = @id

  insert into TestTable values (@id, 1)

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:

if exists (select * from TestTable with (updlock,serializable) where ID = @id)

begin

  update TestTable set myCount = myCount+1 where ID = @id

  insert TestTable (ID, myCount) values (@id, 1)

commit tran

Option 2:

begin

  insert TestTable (ID, myCount) values (@id,1)

commit tran

That's all.

Enjoy Rotem