mysql innodb multiple index 查询时锁住太多行

Question

这是一个mysql inno_bd 表t 主键id 和三个字段上的多个btree 索引。A、B、C字段分别为int、int、Date: 问题如下:有一个存档 t_archive 表，如果它已经过时，我用它来将记录从 t 移动到 t_archive 。为了移动记录，我使用了两个查询:

• INSERT INTO t_archive SELECT * FROM t WHERE A = 1 AND B = 2 AND C = 3
• 从 A = 1 AND B = 2 AND C = 3 的 t 中删除

(如您所见，满足条件 ID 为 4 和 5)在上述查询中，我尝试使用 ID 为 UPDATE 记录:1 和 8。从逻辑上讲，在任何类型的查询中都没有理由锁定记录1、2、3、6、7、8， 但它发生了!由于真正的红色扇区比示例中的大得多，传输操作需要很多时间(大约 50 秒)并且我无法使用其他记录(只是更新 id = 8) - 出现异常:Lock wait timeout超过；尝试重启事务

谁能解释为什么会发生这种情况以及如何避免这种情况？

Answer 1

如果这是一个“大”表，并且您没有合适的索引，例如 INDEX(a,b,c)(以任何顺序)，那么 DELETE 扫描表需要很长时间，在获取锁时卡在锁上。这有锁定等待超时甚至死锁的风险。

如果表“小”和/或您有一个好的索引，是否会发生超时？

回到为什么使用子查询获取 ids 的 DELETE 侵入性较小的问题:

首先，SELECT 子查询以最少的锁运行。然后运行 ​​DELETE，至少需要对相关的 2 行进行独占锁定。如果它没有超时，我怀疑是因为在这个“两步”版本中获取锁的时间。