I have a Store Procedure being called to fill one table whenever we receive incoming files. Sometimes we receive more than one file and the procedure will be called simultaneously. Inside the Procedure the statements are quite simple as given below
IF NOT EXISTS (SELECT.... WHERE A=1 B=2) INSERT ... ELSE UPDATE ... WHERE A=1 and B=2 END
doing this I started getting a duplicate records error, I assume that 2 same records tried to INSERT. To avoid this I put these staements inside a Transaction with SERIALIZABLE ISOLATION LEVEL.
things got even worse and I started getting deadlock error!!!
I tried with the default isolation level (Read Committed) before and I got duplicate records check error again!
Is there something wrong I am doing here??
Answer by Kristen ·
You could try
BEGIN TRANSACTION UPDATE U SET ... FROM MyTable AS U WHERE ... IF @@ROWCOUNT = 0 BEGIN INSERT INTO MyTable ( Col1, Col2, ... ) SELECT ... END COMMIT
I'm not 100% sure if this will create the lock that is needed to prevent the "window of opportunity" between the UPDATE not updating anything, but someone in that moment inserting a row
Another approach might be
BEGIN TRANSACTION INSERT INTO MyTable ( Col1, Col2, ... ) SELECT ... WHERE NOT EXISTS ( SELECT * FROM MyTable WHERE MyPK = @Param1 ... ) IF @@ROWCOUNT = 0 BEGIN UPDATE U SET ... FROM MyTable AS U WHERE ... END COMMIT