Concurrency Conflict

Hi

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')
Begin
Return
End
Else
Begin
insert/update statements
End

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.
Wishes

I would avoid that at all costs - the potential side effects are unacceptable IMHO.

Can you incorporate your EXISTS into the INSERT / UPDATE instead?

DECLARE @intRowCount int
BEGIN TRANSACTION

INSERT INTO  MySchema.MyCasesTable(CaseID, CaseStatusID, Col1, Col2, ...)
SELECT @ParameterCaseID, 'FinishStatusID', @Param1, @Param2, ...
WHERE NOT EXISTS (select 1 from MySchema.MyCasesTable where CaseID=@ParameterCaseID)
SELECT @intRowCount = @@ROWCOUNT

IF @intRowCount = 0
BEGIN
   UPDATE U
   SET CaseStatusID = 'FinishStatusID',
       Col1 = @Param1,
       Col2 = @Param2,
       ...
   FROM MySchema.MyCasesTable
   WHERE CaseID=@ParameterCaseID and CaseStatusID<>'FinishStatusID'
   SELECT @intRowCount = @@ROWCOUNT
END

END TRANSACTION

If @intRowCount = 0 at the end then no insert / update took place (so the record, with the correct CaseStatusID, SHOULD!! already exist.)

Probably slight inefficient in that there are two statements, might be possible to do this in a single statement with MERGE, but I don't use that so don't have reliable advice for it.