PHP, mysqli, and table locks?

Question

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.

To accomplish this I intend to:
1. Read row
2. Lock table
3. Read row again and compare to original row
4. If rows match update, otherwise do nothing (another user has already updated the row)

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.

 // 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";
}

where the lock routine is defined as

function lock(){
     global $oDatabase;
     $iReturn = 0;

     // lock the table
     $iReturn = $oDatabase->m_oConnection->query("LOCK TABLES round WRITE");  
     return $iReturn;
    }

Above, $oDatabase->m_oConnection is a mysqli connection that I use to execute prepared statements on the database.

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...".

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.

Any pointers?

Answer 1

Troubleshooting: You can test for table lock success by trying to work with another table that is not locked. If you obtained the lock, trying to write to a table that was not included in the lock statement should generate an error.

You may want to consider an alternative solution. Instead of locking, perform an update that includes the changed elements as part of the where clause. If the data that you are changing has changed since you read it, the update will "fail" and return zero rows modified. This eliminates the table lock, and all the messy horrors that may come with it, including deadlocks.