Php, "thread safe" SQL operation

Question

Lets talk about a rating system: users can rate other users. There is an user table: USER_ID, USER_NAME and the rates: RATER_ID, RATED_ID, RATE (string) A user may rate someone once, but can change his mind at any time I know its a bit odd, it can never happen, but lets see how it goes:

check if A ever rated B
if no: INSERT INTO
if yes: UPDATE

so, in pseudo-code:

$rec = SELECT COUNT(*) FROM users WHERE RATER_ID = a AND RATED_ID = b
if ($rec == 0)
{
    INSERT INTO rates (a, b, rateText);
}
else
{
    UPDATE rates SET RATE = rateText WHERE RATER_ID = a AND RATER_ID = b
}

but here comes a problem. Lets suppose two rates arrives at the same time (I know its odd).

- rate request A: count(*) is 0, so lets insert
- rate request B: count(*) is 0, so lets insert - SQL ERROR!

How to workaround it?

Answer 1

What you need is commonly known as an "upsert" operation. That is, UPDATE if it's already there, otherwise INSERT.

You can set a UNIQUE constraint on your columns of RATER_ID, RATED_ID. That way, only one row can exist at a time with that specific combination.

From there, use INSERT... ON DUPLICATE KEY. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Untested, but something like this:

INSERT INTO rates (a, b, rateText) VALUES (:a, :b, :rateText)
  ON DUPLICATE KEY UPDATE rateText=VALUES(rateText);

Answer 2

The function you're looking for is upsert: if exists then update, else insert.

mysql supports them: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html