SELECT 上的 MySQL InnoDB 死锁和排他锁(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
}
这篇好文章是转载于：学新通技术网
