SQL Server Concurrency Issue We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set based manner. I will try to illustrate an example here

Lets assume that the isolation level set by the AppServer over the JDBC connection is READ COMMITTED

DELETE FROM TargetAmountTable
/*whole bunch of JOINS on other tables*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountSetTable
SELECT /**whole bunch of JOINS**/
WHERE SourceAmountSetTable.isModified = 1

UPDATE TargetAmountSetTable
SET TargetAmountSetTable.isModified = 1
/*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountTable
SELECT /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

UPDATE SourceAmountSetTable
SET SourceAmountSetTable.isModified = 0
FROM /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1


So the problem we are facing is, when there are multiple processes trying to do the INSERT on the [TargetAmountTable], duplicate rows are added since the last UPDATE statement is not yet executed

Any ideas on how to resolve this?
In general what you want here is an explicit transaction around your code.

BEGIN TRANSACTION
... your code ....
COMMIT


However there are down sides to concurrency here as well.  While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction.  That means that the next set of code that needs to run will have to wait until this one is completed.  

Even worse you increase your odds of creating a deadlock.  A deadlock would occur when session 1 is holding a set of locks X.  Session 2 is holding a set of locks Y then needs to get a hold of some of the resources held by X.  If session 1 then needs resources in Y you get into a situation where neither session can ever complete.  This causes a deadlock and one of the sessions will be rolled back.

The solution to this is to make sure that your transactions take as little time as possible.  What worries me is the whole bunch of JOINs comments.  That tends to mean the commands will take a fair amount of time increasing your odds of blocking and deadlocking.  If I'm wrong and the whole piece of code is fast then you shouldn't have major issues (there is always a chance of deadlocking, speed just reduces it).

Because your example is so simplified it's going to be hard to go beyond such a general piece of advice.  If you'd like to post a better idea of the actual code we might be able to help you in other ways.

Additional information on blocking &amp; deadlocking here.