MySQL duplicate data entry issue I know this issue can be solved using unique key constraint on column and some insert command variation.

But I exactly want to know that at last why the following approach goes wrong sometimes and a duplicate entry is added:


a value is checked in the column
if record returns is > 0 then update is issued, otherwise simple insert command 


Most of the times it works. You can say more than 98%, but why does it fail sometimes?

I just want to understand where the glitch is in this approach because record existence is checked first.
One scenario in which this would fail depends on how busy (or unlucky) you are in timing.   


Session A does the select on the value, sees that it should INSERT
Session B does a select on the value before session A does the required insert or update of the value constraint
Session A inserts row, updates value
Session B inserts row, updates value


It's all a matter of timing (as suggested by your 98% works). You could acquire a WRITE LOCK for Session A on the table you are inserting AND on the table you are updating the value, which will block Session B until Session A releases the lock.

Alternatively, you could go ahead and let the DB handle the locking automatically by creating a UNIQUE INDEX and issuing a INSERT .. ON DUPLICATE UPDATE (recommended)