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`)
Table locks contain these fields:
id - primary, autoinc
name - varchar(64) unique key
description - text
value - text
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.
解决方案
Just figured it out thanks to Quassnoi's answer...
I can do:
$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
}