Prevent two calls to the same script from selecting the same mysql row

The below script is called every 5 seconds. The issue is that if the server is responding slow, one entry in "blog" can get selected twice in a row because the server hasn't had time to set "done" to "1" yet. Is there an industry standard (or whatever you call it) way to prevent this from happening?

$res开发者_JAVA百科ult = mysql_query("SELECT * FROM blogs WHERE done=0 LIMIT 1");
$rows = mysql_num_rows($result); //If there are no entries in with done set to 0, that means we've done them all; reset all entries to 0.
if($rows == 0)
{
    mysql_query("UPDATE blogs SET done=0 WHERE done=1");
}
else
{
    while($row = mysql_fetch_array($result))
    {
        mysql_query("UPDATE blogs SET done=1 WHERE id=$row[id]");
        // Do stuff
    }
}

I think I could change it to

while($row = mysql_fetch_array($result))
{
      if($row['done'] == 1){ die; }
      mysql_query("UPDATE blogs SET done=1 WHERE id=$row[id]");
      //Do stuff
}

But will that really fix the problem? I would imagine there would be a better way that really prevents it from happening without a shadow of a doubt.

---

I think the best way to prevent selecting the same row is using SELECT GET_LOCK("lock_name"); and SELECT RELEASE_LOCK("lock_name");. When you get a lock from mysql server, other processing trying to get a lock will wait for the lock to be released. Below is a sample implementation:

<?php
function getLock($lockName, $dbc) {
    $query = "SELECT GET_LOCK('".$lockName."', 0)";
    $result = mysql_query($query, $dbc);
    $lockResult = mysql_fetch_row($result);
    $lockResult = $lockResult[0];
    return $lockResult == 1 ? true : false;
}

function releaseLock($lockName, $dbc) {
    $query = "SELECT RELEASE_LOCK('".$lockName."')";
    $result = mysql_query($query, $dbc);
}

// CONNECT TO DATABASE
$dbc = mysql_connect('localhost', 'root', '');
mysql_select_db('test', $dbc);

$loopQueue = true;
$rowsProcessed = 0;

// MAIN QUEUE LOOP
while ($loopQueue) {

    // TRY UNTIL GETTING A LOCK
    $queueLockName = 'queue_lock_1';
    while (getLock($queueLockName, $dbc) === true) {

        // WE GOT THE LOCK, GET A QUEUE ROW WITH PENDING STATUS
        $query = 'SELECT * FROM test WHERE status = 0 ORDER BY ID ASC LIMIT 1';
        $result = mysql_query($query, $dbc);

        if (mysql_num_rows($result) < 1) {
            // SINCE WE DON"T HAVE ANY QUEUE ROWS, RELEASE THE LOCK
            releaseLock($queueLockName, $dbc);
            // WE DONT NEED TO LOOP THE MAIN QUEUE ANYMORE SINCE WE DONT HAVE ANY QUEUE ROWS PENDING
            $loopQueue = false;
            // BREAK THIS LOOP
            break;
        }

        // WE GOT THE QUEUE ROW, CONVERT IT TO ARRAY
        $queueRowArray = mysql_fetch_assoc($result);

        // UPDATE QUEUE ROW STATUS TO SENDING
        $query = 'UPDATE test SET status = 1 WHERE id = '.$queueRowArray['id'];
        mysql_query($query);

        // RELEASE THE LOCK SO OTHER JOBS CAN GET QUEUE ROWS
        releaseLock($queueLockName, $dbc);

        // DO STUFF ...

        // UPDATE QUEUE ROW STATUS TO PROCESSED
        $query = 'UPDATE test SET status = 2 WHERE id = '.$queueRowArray['id'];
        mysql_query($query);

        $rowsProcessed++;
    }
}

echo "\n\n".'process finished ('.$rowsProcessed.')'."\n\n";

---

I would have given a go to transactions. Here is an example in another StackOverflow question

Just a question: What happens if the server is even slower? For instance, the select statament takes so long (e.g. 5 seconds) that once it finishes (returning 0 rows), the new select is executed (returning 1 or more rows)

MySQL documentation

---

$result = mysql_query("SELECT * FROM blogs WHERE done=0 LIMIT 1");
$rows = mysql_num_rows($result); //If there are no entries in with done set to 0, that means we've done them all; reset all entries to 0.
if($rows == 0)
{
    mysql_query("UPDATE blogs SET done=0 WHERE done=1");
}
else
{
    while($row = mysql_fetch_array($result))
    {
        mysql_query("UPDATE blogs SET done=1 WHERE id=$row[id] AND done=0");
        if(mysql_affected_rows() != 1)
            die();
        // Do stuff
    }
}