title: MySQL Update query - Will the 'where' condition respected on race condition and row locking? (php, PDO, MySQL, InnoDB)
body: <p>I am trying to build a first-come first-get model sale page. We have n number of items of the same type. We want to assign these n items to the first n users who made the request. Corresponding to each item, there is a database row. When the user presses buy button, the system tries to find an entry which is not yet sold (<code>reservationCompleted = FALSE</code>) and updates the user id and sets <code>reservationCompleted</code> to true.</p>
  
  <p>Since the database engine I am using is InnoDB, I understand that there is an internal locking mechanism that won't allow two processes to do an update at once on the same row.</p>
  
  <p>My question is,</p>
  
  <p>if the statement I am using is the following, will this result in different users getting assigned to the same row if two requests arrive at the same time? </p>
  
  <pre><code>$query = "UPDATE available_items
      SET assignedPhone=".$user-&gt;phone.",
          reservationCompleted = TRUE,
          assignmentCreatedTimestamp =".time()."
      WHERE id=".$itemListing['id']."
      AND reservationCompleted=FALSE";
  $stmt = $pdo-&gt;prepare($query);
  $stmt-&gt;execute();
  </code></pre>
  
  <p>Consider the following case.</p>
  
  <p>Two different processes gets the same row (say id=5) and tries to update the DB entry. But one of them gets the lock. It updates the item and releases the lock and the next process gets the lock. So,will it again validate the where condition before performing the update? </p>