大数据量更新查询避免锁等待超时的良好做法是什么？

Question

所以基本上，我当前有这个查询：

UPDATE act AS a
INNER JOIN blok AS b
ON b.fav_pat = a.pat_id
SET a.blok_id = b.id

由于我拥有大量数据，目前已超时。有没有办法在不修改数据库配置的情况下避免超时？

Answer 1

您使用的 flyway 软件包效果最好允许使用主机 RDBMS 的事务语义完全回滚任何不完整的操作。这意味着它被设计为执行更新操作，就像您在符合 ACID 的单个事务中向我们展示的操作一样。

如果涉及的表很大（数百万行或更多），则事务可能会非常大。它们可能会使您的 MySQL 服务器崩溃，将事务日志溢出到磁盘或 SSD。提交这些事务日志可能需要很长时间。您没有提到行数，但如果行数很大，则 Flyway 可能不是适合这项工作的工具。

您的锁定超时暗示您正在对具有其他并发活动的数据库执行此操作。您可能希望在安静的数据库上执行此操作以获得最佳结果。

您可以通过这样做来增加锁定等待超时。

show variables like 'innodb_lock_wait_timeout'; -- previous vale
SET GLOBAL innodb_lock_wait_timeout = 300; -- five min

然后，也许在假期日出之前或其他安静的时间再试一次。更多信息此处。

当您的 Flyway 作业完成时，请考虑将锁定超时恢复到之前的值。

您还可以考虑批量更新，例如一次更新 1000 行。但flyway似乎不支持这一点。如果您走这条路，您可以问另一个问题。