php - 防止对同一脚本的两次调用选择同一 mysql 行

Question

下面的脚本每 5 秒调用一次。问题在于，如果服务器响应缓慢，“博客”中的一个条目可能会连续两次被选中，因为服务器还没有时间将“完成”设置为“1”。是否有行业标准(或任何您称之为)的方法来防止这种情况发生？

$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]");
        // Do stuff
    }
}

我想我可以把它改成

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

但这真的能解决问题吗？我想会有更好的方法可以毫无疑问地真正防止它发生。

Answer 1

我认为防止选择同一行的最佳方法是使用 SELECT GET_LOCK("lock_name"); 和 SELECT RELEASE_LOCK("lock_name");。当你从 mysql 服务器获得锁时，其他试图获得锁的进程将等待锁被释放。下面是一个示例实现:

<?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";