MySQL单表出现死锁问题？基于5.7.21版本Windows+PHP环境

嘿，我来帮你搞定这个死锁问题——这种循环执行的加锁+更新操作，在MySQL 5.7的InnoDB引擎下简直是死锁的高发场景，我之前帮好几个朋友排查过类似的情况。

死锁的根源 #

先给你拆解下为什么会触发死锁：

• 线程1的SELECT id FROM t FOR UPDATE是全表扫描加排他锁，InnoDB会按它自己的扫描顺序（通常是主键顺序，若有其他索引可能变化）给所有行加锁。
• 线程2的SELECT id FROM t WHERE id IN (...) FOR UPDATE是给指定行加锁，但IN子句里的id顺序如果是随机的，加锁顺序就和线程1可能不一致。
• 当两个线程交叉持有对方需要的锁时，就形成了循环等待——比如线程1锁了行A，线程2锁了行B，然后线程1要更新行B，线程2要更新行A，谁也不让谁，InnoDB就会触发死锁检测，终止其中一个事务。

具体修复方案 #

1. 强制统一加锁顺序（最有效） #

不管是全表还是指定行操作，都按照相同的顺序获取锁，彻底避免循环等待。

• 线程1修改为按主键升序加锁：

while(true) { 
  START TRANSACTION; 
  // 按id升序扫描并加锁，确保加锁顺序固定
  SELECT id FROM t ORDER BY id FOR UPDATE; 
  UPDATE t SET ...=... ORDER BY id; 
  COMMIT; 
}

• 线程2先对IN里的id排序，再执行加锁：

// 先在PHP里对id数组升序排序
$targetIds = [5,2,8];
sort($targetIds);
$idList = implode(',', $targetIds);

while(true) { 
  START TRANSACTION; 
  SELECT id FROM t WHERE id IN ($idList) ORDER BY id FOR UPDATE; 
  UPDATE t SET ...=... WHERE id IN ($idList); 
  COMMIT; 
}

这样两个线程的加锁顺序完全一致，不会出现交叉等待的情况，死锁自然就消失了。

2. 缩小锁的范围（减少冲突概率） #

如果线程1不需要全表锁，尽量只锁定需要更新的行，避免无意义的锁占用。比如如果业务上是更新特定状态的行，就把SELECT FOR UPDATE的条件加上：

SELECT id FROM t WHERE status = 1 ORDER BY id FOR UPDATE;

这样只锁需要处理的行，和线程2的锁冲突概率会大幅降低。

3. 增加死锁重试机制（兜底方案） #

InnoDB的死锁是正常的并发现象，业务代码可以捕获死锁错误（MySQL错误码1213），自动重试事务。比如PHP用PDO的话：

function executeTransaction() {
  $pdo = new PDO(...);
  $retryCount = 3;
  while($retryCount > 0) {
    try {
      $pdo->beginTransaction();
      // 执行SELECT FOR UPDATE和UPDATE
      $pdo->commit();
      return true;
    } catch(PDOException $e) {
      $pdo->rollBack();
      // 判断是否是死锁错误
      if($e->getCode() == 1213) {
        $retryCount--;
        usleep(100000); // 等待100ms再重试
      } else {
        throw $e;
      }
    }
  }
  return false;
}

4. 查看死锁日志精准定位 #

如果还是不确定问题，可以执行SHOW ENGINE INNODB STATUS;查看最新的死锁日志，里面会详细列出两个事务的锁等待情况、执行的语句，帮你确认锁冲突的具体行和原因。

额外提醒 #

MySQL 5.7的默认隔离级别是REPEATABLE READ，这个级别下的间隙锁可能会扩大锁的范围，如果业务允许，可以改成READ COMMITTED，能减少一些不必要的锁冲突（需要修改my.cnf里的transaction-isolation = READ-COMMITTED，然后重启MySQL）。

内容的提问来源于stack exchange，提问作者Werner