mysql innodb update锁_带有独占锁的SELECT上的MySQL InnoDB死锁（FOR UPDATE）

最新推荐文章于 2025-06-23 18:35:09 发布

原创最新推荐文章于 2025-06-23 18:35:09 发布 · 237 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql innodb 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`)

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

}