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

我相信从 START 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 的回答才弄明白...

Just figured it out thanks to Quassnoi's answer...

我能做到:

$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屋！