MySQL duplicate data entry issue

Question

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:

1. a value is checked in the column
2. 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.

Answer 1

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)