CODEWITHSUNDEEP
phpmysql
FBP
FBP

Reputation: 345

MySQL, PHP: Check Race condition before update

I have a column (lets call it A) in my table called status which I update based on user action. The column A holds status as integers. I write 1 to the field A when user requests for the status and 2 where user confirms( by hitting a confirm button on the UI) having read the value(code). However I have a condition where if any of the user has already confirmed the value then I need to prevent updating the table and instead report an error to the user saying "This Value has already been confirmed".

Currently in my PHP code to prevent more than one user from updating the value, I read(Step 1) from the table the value of A and only if the value is not 2 then I immediately write to the table updating the value(Step 2). 

$status = $code->getStatus($id); //Step1
if($status!=2) {
  $code->updateStatus($id); //Step 2
}else{
  echo "This value has already been confirmed";
}

However my concern is what if another request to confirm value(and hence update value to 2) came in when the first request was yet to execute Step 2 and the new request had already executed Step 1. Request 2 having executed Step 1 will return $status as 1 and hence move to executing Step 2 causing an overwrite. How do I prevent this either in PHP or MYSQL given I will have numerous users?

Upvotes: 0

Views: 213

Answers (1)

Jirka Hrazdil
Jirka Hrazdil

Reputation: 4021

Why not check status value in UPDATE query?

UPDATE `table` SET `status` = 2 WHERE `id` = 123 AND `status` = 1

And then check for number of affected rows - if it is greater than zero, update was perfomed successfully. If not, there are two possible causes: id was not found or status was not 1.

Upvotes: 3

Related Questions