I have a stored procedure which has a insert statement.Before executing the insert query i am checking with "if not exists" condition for the availability of the record in the table. This is working fine, but occassionally duplicate(2 rows with same values for all columns) rows are inserted into that table. Can any one suggest what is the remedy??
we cannot change the description of the table as this table is in production. The source table does always has only one record. Find the below sample code from the stored procedure which is inserting duplicate records
if not exists(select 1 from tablename where a = @value and b = null and c = null)
insert into tablename() select a1,a2,a3 from tablename where...
I think you may be in the following situation:
1. user A evaluates the not exists() condition - returns true
2. user B evaluates the same not exists() condition - returns true
3. user A makes the insert statement
4. user B makes the same insert
Obviously it's difficult to debug whole system from 2 lines of code but
Im guessing that if this code gets called by multiple processes (users) at the same time then the following could happen there is no data in the target table then 2 parallel jobs could both test to see if no record exists in the target table both would jobs would say there is no data then both jobs would try to insert a rec you end up with a duplicate.
You could change the code so it does the check and the insert in one statement ie :
where not exists( select 1 from target )
I personally would still create unique index on source and target tables I know they should only ever contain one row and if thats what they do hold then no part of the system should be affected. If suddenly either of the tables contains a duplicate then youd get an error message immediately pointing you to the code with the problem.
As a last alternative you could just use select distinct when you read data from the target table.