带有独占锁的SELECT上的MySQL InnoDB死锁(FOR UPDATE) [英] MySQL InnoDB dead lock on SELECT with exclusive lock (FOR UPDATE)

问题描述

我这样做是为了确保仅运行一次此过程的实例(伪代码php/mysql innodb):

I do this to ensure only once instance of this process is running (pseudo code php/mysql innodb):

START TRANSACTION
$rpid = SELECT `value` FROM locks WHERE name = "lock_name" FOR UPDATE
$pid = posix_getpid();
if($rpid > 0){
  $isRunning = posix_kill($rpid, 0);
  if(!$isRunning){ // isRunning
    INSERT INTO locks values('lock_name', $pid) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
  }else{
    ROLLBACK
    echo "Allready running...\n";
    exit();
  }
}else{ // if rpid == 0 -
  INSERT INTO locks values('lock_name', $pid) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
}
COMMIT

...............

//free the pid
INSERT INTO locks values('lock_name', 0) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)

表锁包含以下字段:

id - primary, autoinc
name - varchar(64) unique key
description - text
value - text

我相信从开始TRANSACTIN到COMMIT/ROLLBACK的时间确实是毫秒-没有足够的时间来超时.此代码怎么可能导致死锁?我不在此交易中使用其他表格.似乎无法实现死锁.如果同时启动2个进程，则第一个在该行获得锁的进程将继续进行，而另一个进程将等待该锁被释放.如果未在1分钟内释放锁，则错误为超时"，而不是死锁.

I believe the time from START TRANSACTIN to COMMIT/ROLLBACK is really milliseconds - there is no enough time to even get timeout. How is it possible to get a deadlock with this code? I don't use other tables within this transaction. It looks that deadlock is not possible. If 2 processes start at the same time the first that gets the lock on that row will will proceed and the other will wait the lock to be released. If the lock is not released within 1 minute the error is "timeout", not deadlock.

推荐答案

感谢 Quassnoi 的回答...

我可以做到:

$myPid = posix_getpid();
$gotIt = false;
while(true){
  START TRANSACTION;
  $pid = SELECT ... FOR UPDATE; // read pid and get lock on it
  if(mysql_num_rows($result) == 0){
    ROLLBACK;// release lock to avoid deadlock
    INSERT IGNORE INTO locks VALUES('lockname', $myPid);
  }else{
    //pid existed, no insert is needed
    break;
  }
}

if($pid != $myPid){ //we did not insert that
  if($pid>0 && isRunning($pid)){
    ROLLBACK;
    echo 'another process is running';
    exit;
  }{
    // no other process is running - write $myPid in db
    UPDATE locks SET value = $myPid WHERE name = 'lockname'; // update is safe
    COMMIT;
  }
}else{
  ROLLBACK; // release lock
}

这篇关于带有独占锁的SELECT上的MySQL InnoDB死锁(FOR UPDATE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！