大數據量更新查詢避免鎖定等待逾時的良好做法是什麼？

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似乎不支持這一點。如果您走這條路，您可以問另一個問題。