title: PHP, mysqli, and table locks?
body: <p>I have a database table where I need to pull a row, test user input for a match, then update the row to identify the user that made the match.  Should a race condition occur, I need to ensure that the first user's update is not overwritten by another user.</p>
  
  <p>To accomplish this I intend to:<br>
  1. Read row<br>
  2. Lock table<br>
  3. Read row again and compare to original row<br>
  4. If rows match update, otherwise do nothing (another user has already updated the row)</p>
  
  <p>Based on information I found on Google, I expected the lock table statement to block until a lock was aquired.  I set up a little test script in PHP that would stall for 10 seconds to allow me time to manually create a race condition.</p>
  
  <pre><code> // attempt to increment the victor number
  $aData["round_id"] = $DATABASE["round_id"];
  
  // routine to execute a SELECT on database (ommited for brevity)
  $aRound = $oRound->getInfo($aData);  
  
  echo "Initial Round Data:";
  print_r($aRound);
  
  echo "Locking...";
  echo $oRound->lock();
  
  echo "Stalling to allow for conflict...";
  sleep(10);
  echo "Awake...";
  
  $aLockedRound = $oRound->getInfo($aData);
  if($aRound["victor_nation"] == $aLockedRound["victor_nation"]){
      $aData["victor_nation"] = $aRound["victor_nation"] + 1;
      $oRound->update($aData);
      echo "Incremented Victor Nation";
  }
  </code></pre>
  
  <p>where the lock routine is defined as</p>
  
  <pre><code>function lock(){
          global $oDatabase;
          $iReturn = 0;
  
          // lock the table
          $iReturn = $oDatabase->m_oConnection->query("LOCK TABLES round WRITE");     
          return $iReturn;
      }
  </code></pre>
  
  <p>Above, <code>$oDatabase->m_oConnection</code> is a mysqli connection that I use to execute prepared statements on the database.  </p>
  
  <p>When I run my test script I kick off the first user and wait for "Stalling to allow for conflict..." , then start a second script.  On the second script I expected it to block at "Locking...", however, the second script also continues to "Stalling to allow for conflict...".</p>
  
  <p>Since the LOCK statment doesn't appear to be blocking, nor returning any indicator of acquiring the lock (return value is echoed and blank), it's unclear to me that I'm actually acquiring a lock.  Even if I am, I'm not sure how to proceed.</p>
  
  <p>Any pointers?</p>