The Upsert concurrency situation solved by lock:

My web method (many instances are running at once (load balanced)) gets a unique name and a price for it. It must upsert it in the database. It goes like this:

UpsertItems(Name, Cost)

{

ExecNonQuery("

exec sp_getapplock @Resource='Lck{0}', @LockOwner='Session';

if Exists (select 1 from tbl where Name='{0}') Update tbl set Price={1} where Name='{0}';

else insert Into tbl (Name,Price) Values ('{0}',{1});

exec sp_releaseapplock @Resource='Lck{0}', @LockOwner='Session';

", Name, Cost);

}

Well, this basic beautiful things simply doesn't work ! sp_getapplock is not working! It grant the lock all the time. I think I need @LockScope = DBSERVER since I have only one db server.