MySQL异常行为引发非预期SELECT/UPDATE问题

问题概述 #

项目需实现行级独占锁，确保每行数据同一时间仅被一个任务处理。当前实现：

• 表新增lock_id字段，用任务ID标记锁定状态
• 选空闲行时先筛选lock_id=0的行，再执行带条件更新抢占：UPDATE myTable SET lock_id=1234 WHERE id=? AND lock_id=0，通过受影响行数判断是否抢占成功（0则表示已被其他任务抢占）

近期出现致命异常：两个间隔约30秒的任务竟抢到同一行，直接造成资金交易损失。尝试在查询语句中加入NOW()规避缓存，问题仍未解决。

环境信息 #

• PHP 8.1.27
• MySQL 5.7.44

核心原因排查与修复方案 #

1. 事务隔离与未提交读问题 #

MySQL 5.7默认隔离级别为REPEATABLE READ，但如果代码中存在未提交事务，或隔离级别被修改为READ UNCOMMITTED，会导致任务A更新lock_id后未提交时，任务B仍能读取到lock_id=0的旧值。

• 检查数据库隔离级别：

  SELECT @@transaction_isolation;
  

• 确保更新lock_id后立即提交事务，避免长时间持有未提交的修改。

2. 拆分操作的原子性漏洞 #

绝对不要先查询再更新——这两步是分离的，即使更新时加了条件，也可能出现查询到行后、更新前被其他任务抢占的情况。

• 修复方案：直接用更新语句完成抢占逻辑，跳过单独查询步骤：

  $stmt = $pdo->prepare("UPDATE myTable SET lock_id=? WHERE id=? AND lock_id=0");
  $stmt->execute([$taskId, $rowId]);
  $affected = $stmt->rowCount();
  if ($affected === 0) {
      // 该行已被抢占，直接跳过
      continue;
  }
  

• 若需要先获取行数据再处理，使用带行级锁的原子查询+更新：

  BEGIN;
  -- 查询时直接锁定该行，其他任务无法读取/修改
  SELECT * FROM myTable WHERE lock_id=0 LIMIT 1 FOR UPDATE;
  UPDATE myTable SET lock_id=? WHERE id=?;
  COMMIT;
  

3. 死锁与lock_id重置问题 #

若任务崩溃或异常退出，未将lock_id重置为0，会导致该行被永久锁定。后续任务可能因逻辑漏洞绕过lock_id=0的判断，重复抢占。

• 新增lock_expire_time字段，添加过期自动释放逻辑：

  -- 抢占时设置过期时间（比如5分钟）
  UPDATE myTable SET lock_id=?, lock_expire_time=DATE_ADD(NOW(), INTERVAL 5 MINUTE) 
  WHERE id=? AND (lock_id=0 OR lock_expire_time < NOW());
  -- 任务完成后重置锁定状态
  UPDATE myTable SET lock_id=0, lock_expire_time=NULL WHERE id=?;
  

4. 索引与性能优化 #

lock_id字段无索引会导致更新时全表扫描，增加并发冲突概率：

CREATE INDEX idx_lock_id ON myTable(lock_id);

5. 隐式提交与连接问题 #

检查PHP代码中是否存在隐式提交逻辑（比如执行DDL语句、使用自动提交模式），导致事务提前结束、锁被释放。

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