Rails 4/ActiveRecord/MySQL删除操作优化与死锁问题排查

我之前在处理批量清理关联数据的时候，也踩过生产环境死锁的坑，结合你这个删除未关联T3的T1、T2数据的场景，分享几个亲测有效的优化方案：

• 分批删除，避免一次性锁定大量数据
  一次性删除数百万行肯定会长时间占用锁资源，和其他业务操作冲突触发死锁。改成小批量循环删除是最直接的办法，比如每次删1000条（数量可以根据业务调整），每批提交一次事务释放锁：

  -- 分批清理T1中未关联T3的数据（用LEFT JOIN替代NOT IN，性能更优）
  WHILE EXISTS (
    SELECT 1 FROM T1 t1
    LEFT JOIN T3 t3 ON t1.id = t3.t1_id
    WHERE t3.t1_id IS NULL
  ) DO
    DELETE t1 FROM T1 t1
    LEFT JOIN T3 t3 ON t1.id = t3.t1_id
    WHERE t3.t1_id IS NULL
    LIMIT 1000;
    COMMIT; -- 每批提交，及时释放锁
  END WHILE;
  
  -- 同理处理T2表
  WHILE EXISTS (
    SELECT 1 FROM T2 t2
    LEFT JOIN T3 t3 ON t2.id = t3.t2_id
    WHERE t3.t2_id IS NULL
  ) DO
    DELETE t2 FROM T2 t2
    LEFT JOIN T3 t3 ON t2.id = t3.t2_id
    WHERE t3.t2_id IS NULL
    LIMIT 1000;
    COMMIT;
  END WHILE;
  

• 给关联字段加索引，缩小锁范围
  如果没有合适的索引，MySQL会做全表扫描，过程中会锁定大量无关行，大大增加死锁概率。给关联字段加上索引，让数据库能快速定位要删除的行：

  -- 给T1、T2的主键/关联字段加索引
  CREATE INDEX idx_t1_id ON T1(id);
  CREATE INDEX idx_t2_id ON T2(id);
  -- 给T3中关联T1、T2的字段加索引
  CREATE INDEX idx_t3_t1_id ON T3(t1_id);
  CREATE INDEX idx_t3_t2_id ON T3(t2_id);
  

  索引不仅能加速查询，还能让数据库只锁定需要删除的行，减少锁冲突。

• 错开业务高峰期执行
  把删除操作放在业务低峰期（比如凌晨2-4点）执行，此时线上流量少，和其他业务操作的锁冲突概率会大幅降低，从根源减少死锁发生的可能。

• 调整事务隔离级别（谨慎操作）
  MySQL默认的REPEATABLE READ隔离级别会带来幻读问题，锁的持有时间更长。如果你的业务逻辑允许，可以临时改成READ COMMITTED，减少锁的范围和持有时间：

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

  注意：改之前要确认业务不会因为隔离级别降低出现数据一致性问题。

• 排查死锁日志，精准优化
  可以通过MySQL的InnoDB状态日志找到死锁的具体原因，针对性调整：

  SHOW ENGINE INNODB STATUS;
  

  查看输出中的LATEST DETECTED DEADLOCK部分，能看到冲突的事务、涉及的表和语句，据此调整删除逻辑或者业务操作的执行顺序。

• 用SKIP LOCKED跳过已锁定行（MySQL 8.0+适用）
  如果你的MySQL版本是8.0及以上，可以用FOR UPDATE SKIP LOCKED语法跳过已经被其他事务锁定的行，避免等待锁引发死锁：

  DELETE t1 FROM T1 t1
  WHERE t1.id IN (
    SELECT id FROM T1 t1_temp
    LEFT JOIN T3 t3_temp ON t1_temp.id = t3_temp.t1_id
    WHERE t3_temp.t1_id IS NULL
    LIMIT 1000 FOR UPDATE SKIP LOCKED
  );
  COMMIT;
  

  这个方法能让删除操作只处理未被锁定的行，不会因为等待锁而阻塞，降低死锁风险。

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