SQL Server 2012
db server receives insert/update calls from multiple application servers at exactly same time.
Sp took 1 sec to complete one call, so multiple executions of same sp starts before completion of 1st sp call.
Therefore my following concurrency check (i.e. not change case status if once finished) fails.
Part of my sp code as under:
IF EXISTS (select 1 from MySchema.MyCasesTable where CaseID=@ParameterCaseID and CaseStatusID='FinishStatusID')
I have tried nolock in IF block, transaction in else block but nothing work.
If db server got the calls of Finish status with another status at exactly the same time, then some time my cases status update to a pervious status even after getting finish status.