PHP script create duplicate entries on mysql if runs same time

Question

I have a php script that check for a particular unique entry in the table. If the entry doesn't exist it create a new row for that data otherwise it just update the count for that entry. My script can be hit at the same time (at same second). In this case, both instance of the script check together and don't find entry and try to insert record. The second instance raise error in that case.

Is there any way to prevent that.

Using the method mentioned in this question will be helpful ?

Answer 1

You have to

• lock the involved tables (LOCK TABLES ...)
• check if the entry exists
• create the entry if necessary
• and unlock the table (UNLOCK TABLES)

Answer 2

MySQL supports the "REPLACE" operation, which will delete an existing row and recreate it (probably not exactly what you are looking for): http://dev.mysql.com/doc/refman/5.0/en/replace.html

What you are really looking for is "INSERT ... ON DUPLICATE KEY UPDATE": http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Their first example on that page seems to be what you described:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

It works when the INSERT causes a conflict on the primary key or a unique key, and then runs as an UPDATE.

Answer 3

Why not just go ahead and INSERT and expect a 1022 duplicate key error?