MySQL ER_LOCK_DEADLOCK死锁问题：调整语句后为何仍偶发？

首先，先梳理下你的场景：你切换到连接池（哪怕connectionLimit=1）后出现死锁，修改嵌套子查询的删除语句后死锁大幅减少但仍偶发——这说明你已经做了核心优化，但还有隐藏的锁冲突点没覆盖到。下面我拆解几个可能的原因，以及对应的排查方向：

1. 连接池复用带来的隐式事务残留 #

即使你把连接池限制为1，连接池的核心特性是复用连接。如果你的代码在使用连接后没有正确提交/回滚事务（比如异常场景下没处理事务收尾），这个连接归还到池子里时，可能还带着未完成的事务状态。当下一次服务复用这个连接执行删除操作时，就可能和其他并发操作（哪怕是同一服务的下一次执行）产生锁冲突——因为前一个事务的锁还没释放。

解决建议：

• 把删除操作包裹在显式事务里，执行完成后强制提交，并且恢复SQL_SAFE_UPDATES的默认值，避免连接残留状态：

  START TRANSACTION;
  SET SQL_SAFE_UPDATES = 0;
  DELETE FROM rtls.users_status 
  WHERE rtls.users_status.StatusIndex IN (
      SELECT * FROM (
          SELECT u.StatusIndex FROM rtls.users_status as u WHERE u.SiteId = 62
      ) as a
  );
  COMMIT;
  SET SQL_SAFE_UPDATES = 1;
  

• 检查连接池配置，确保连接归还时会自动清理事务状态（比如开启autoCommit，或者依赖池化框架自带的事务清理逻辑）。

2. InnoDB行级锁的范围与间隙锁 #

你的嵌套子查询优化，本质是先明确要删除的行的StatusIndex，再精准删除，减少了锁的范围，但如果表没有合适的索引，InnoDB还是会扫描大量行并加锁：

• 如果SiteId没有索引，子查询会全表扫描，过程中会给扫描到的行加意向锁；
• 如果隔离级别是默认的REPEATABLE READ，InnoDB会使用间隙锁防止幻读——当你删除SiteId=62的行时，InnoDB会给这个范围附近的间隙也加锁，如果有其他操作（比如插入新的SiteId=62的行）试图修改这个间隙，就会触发死锁。

解决建议：

• 给users_status表的SiteId字段建单独索引，或者给SiteId+StatusIndex建联合索引，让子查询和删除操作都能快速定位目标行，缩小锁的范围；
• 如果业务可以接受幻读，尝试把事务隔离级别降到READ COMMITTED，这样InnoDB会禁用间隙锁，大幅降低死锁概率。

3. 其他并发操作的冲突 #

死锁必然是两个事务互相等待对方的锁，所以你需要确认：除了你的定时删除服务，有没有其他进程/线程在操作users_status表的SiteId=62数据？比如：

• 其他服务在更新这些行的状态；
• 有插入操作在添加SiteId=62的新行；
• 关联表的操作（比如外键关联）触发了对users_status的锁。

解决建议：

• 执行SHOW ENGINE INNODB STATUS;查看死锁日志，里面会明确显示死锁发生时两个事务的具体SQL、锁的资源类型，这是定位问题的最直接方法；
• 监控数据库的并发操作，看看死锁发生的时间点，有没有其他针对该表的写入操作。

4. 单连接下的异步/并发执行问题 #

哪怕connectionLimit=1，如果你的服务代码是异步架构（比如用了多线程/协程），可能存在多个任务同时等待获取同一个连接的情况。当第一个任务获取连接执行删除（加锁）还没完成时，第二个任务等待连接，这时候如果有其他外部操作持有了互补的锁，就可能触发死锁。

解决建议：

• 确保你的定时任务是串行执行的，避免同一时间多个任务试图执行删除操作；
• 检查代码中是否有未正确同步的并发逻辑，导致多个线程同时请求数据库操作。

总结下来，最关键的一步是查看InnoDB的死锁日志，它会告诉你死锁的具体参与者和锁资源，能帮你快速定位到遗漏的冲突点。

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