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.

  • it's hard to say without some sort of DDL. What is the purpose of the value >1? At first glance, it should be >=1, but again, don't know for sure without know what the purpose of your column is, and how the value changes. – Derek Downey Aug 30 '11 at 17:25
  • ahh, looks like the >1 was a typo. – Derek Downey Aug 30 '11 at 17:49

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)

| improve this answer | |
  • I understand about that unlucky timing but when already there are two records.. one time bad timing happened already. Then why/how third time same entry could be added? And yes I know its solution as I mentioned in my question but i wanted to know the glitch in this technique. – Rick James Aug 30 '11 at 18:02
  • Can't really say without some DDL and the exact queries you're calling, but the logic looks fine. Tempted to call application foul. (AKA you have a bad filter when selecting the value that fails sometimes). Another good reason to let the database handle it. – Derek Downey Aug 30 '11 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.