从MySQL表中同时获取任务

问题描述：

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

我有很多执行不同任务的控制台应用程序。我从php脚本获得了独特的任务： p>

  $ mysqli-＆gt; autocommit（FALSE）; 
 $ result = $ mysqli-＆gt; query（“SELECT id，task FROM  queue WHERE locked = 0 LIMIT 1 FOR UPDATE;“）; 
 
while（$ row = $ result-＆gt; fetch_assoc（））{
 $ mysqli-＆gt; query（'UPDATE queue SET locked = 1 WHERE id =  “'。$ row ['id']。';;'）; 
 $ mysqli-＆gt; commit（）; 
 $ response [”response“] [”task“] = $ row [”task“]  ; \ N} 
 
 $的mysqli-＆GT;关闭（）;  
echo json_encode（$ response）; 
  code>  pre> 
 
 有时我有重复的任务，“尝试锁定时发现死锁;尝试重新启动事务”。
我是什么意思 做错了吗？ p> 
 
 
 UPD：设置索引“锁定”列解决问题 p> 
  div>

答

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.

从MySQL表中同时获取任务

相关推荐