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??