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?

Most SQL engines handle concurrency problems with transactions. See: dev.mysql.com/doc/refman/5.0/en/commit.html — John WH Smith
– John WH Smith, Commented Jul 23, 2014 at 18:50
@JohnWHSmith, MyISAM has no transactions but handles concurrency just fine. — Marcus Adams
– Marcus Adams, Commented Jul 23, 2014 at 19:27

Brad · Accepted Answer · 2014-07-23 18:51:12Z

6

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);

answered Jul 23, 2014 at 18:51

Brad

164k57 gold badges380 silver badges559 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Smith Over a year ago

Ive never seen this before!

John Smith Over a year ago

if RATER_ID, RATED_ID is a PRIMARY KEY instead of UNIQue constraint, does this still work?

Andreas · Accepted Answer · 2014-07-23 18:51:22Z

3

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

answered Jul 23, 2014 at 18:51

Andreas

5,1562 gold badges28 silver badges37 bronze badges

Collectives™ on Stack Overflow

Php, "thread safe" SQL operation

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related