Concurrent requests problem in mysql

Question

I face the following problem

function book($memberid, $classid){
if (!book){
 // update the db and change the book variable to true
}
}

My problem is 2 requests submitted at the same time. The first request passes the line if (!book){ but not yet update the db. And at the same time, the second request runs the same function and also passes the line if (!book){. Therefore, the result is wrong.

I want to know how to solve this concurrency problem. Lock the db? But I am afraid that it will affect the performance.

Answer 1

Why do you need that variable?

The standard way of resolving concurrency problems when updating databases is to let the database handle it by means of transactions (which will impact performance in some cases, but so will a code only solution).

I'd like to see what goes inside the if (!book) clause.

EDIT: Why don't you change the db to make the (person,class) combination a unique key? That way you just physically cannot insert twice the same value in the table. I'm still taking wild guesses because it depends on the structure of the database. Also MySQL has a special syntax to do a conditional insert which would suit here. Check this url.

Answer 2

If you create an unique index(person,class) you can simply try to insert the new record without any previous tets. If there's already another record with the same values in (person,class) the new record will be rejected and MySQL raises a "duplicate key" error which can be appropriately handled by your script:

if ( 1062===mysql_errno() ) {
  echo "you've already made a reservation for this lecture.";
}

Answer 3

Can you use

UPDATE x SET y WHERE z

then simply check affected rows to see whether anything changed? That way, MYSQL effectively does both the check and the actual data change in one smooth atomic transaction for you with no concurrency problems. (I'm basing this on the fact you use the word "update" and not "insert" in your Q; if it's a insert this approach won't work but VolkerK's would)

If you really must check by SELECT then write by UPDATE as 2 separate statements, then transactions are the only way to go. You mention locking the DB - note that that's not the only option available to you. If you use InnoDB tables there are many different levels you can use.