MySQL InnoDB 在 SELECT 上使用排他锁死锁(用于更新)

Question

我这样做是为了确保这个进程的实例只运行一次(伪代码 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

我相信从 START TRANSACTIN 到 COMMIT/ROLLBACK 的时间真的是几毫秒——没有足够的时间甚至超时。这段代码怎么可能陷入僵局？我不在此事务中使用其他表。看起来死锁是不可能的。如果 2 个进程同时启动，第一个获得该行锁的进程将继续，另一个将等待锁被释放。如果在 1 分钟内没有释放锁，则错误是“超时”，而不是死锁。

Answer 1

SELECT FOR UPDATE 在获取记录的独占锁之前获取表的意向独占锁。

因此，在这种情况下:

X1: SELECT FOR UPDATE -- holds IX, holds X on 'lock_name'
X2: SELECT FOR UPDATE -- holds IX, waits for X on 'lock_name'
X1: INSERT -- holds IX, waits for X for the gap on `id`

发生死锁，因为两个事务都持有表上的 IX 锁并等待记录上的 X 锁。

实际上，MySQL manual on locking 中描述了这种情况.

要解决这个问题，您需要删除除您正在搜索的索引之外的所有索引，即 lock_name。

只需将主键放在 id 上。