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:
ifexists (select * from TestTable where ID = @id)
update TestTable set myCount = myCount+1 where ID = @idend
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:begin tran
ifexists (select * from TestTable with (updlock,serializable) where ID = @id)
update TestTable set myCount = myCount+1 where ID = @id
insert TestTable (ID, myCount) values (@id, 1)
begintran update TestTable with (serializable) set myCount = myCount + 1 where ID = @id if @@rowcount = 0
insert TestTable (ID, myCount) values (@id,1)end