MySQL duplicate data entry issue

MySQL

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.

Best Answer

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)

Related Question