从MySQL表中同时获取任务

Question

I have a lot of console applications that perform different tasks. I getting unique task from php script:

$mysqli->autocommit(FALSE);
$result = $mysqli->query("SELECT id, task FROM queue WHERE locked = 0 LIMIT 1 FOR UPDATE;");

while($row = $result->fetch_assoc()){ 
    $mysqli->query('UPDATE queue SET locked = 1 WHERE id="'.$row['id'].'";');
    $mysqli->commit();
    $response["response"]["task"] = $row["task"];
}

$mysqli->close();   
echo json_encode($response);

Sometimes I have duplicate task and, "Deadlock found when trying to get lock; try restarting transaction". What am I doing wrong?

UPD: set index on "locked" column solve problem

Answer 1

From the MySQL documentation How to Minimize and Handle Deadlocks:

Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks.

Adding an index to the locked column should solve this. Without it, the SELECT query has to scan through the table, looking for a row with locked = 0, and all the rows it steps through must be locked.

If you add an index to the column in the WHERE clause, it can go directly to that record and lock it. No other records are locked, so the possibility of deadlock is reduced.