解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction

日期：

隨著資料庫資料筆數增加，同時間對同一資料表新增或更新資料，時常會遇到 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction 錯誤訊息，導致資料表更新失敗。可以在 SQL 語法加上主鍵再更新資料解決。

Mysql icons created by Freepik - Flaticon


問題原因

What Causes “Lock wait timeout exceeded” Error in MySQL? | Baeldung 解釋了不同造成的原因與測試情境。

解決方式

1. 瞭解狀況

SHOW OPEN TABLES WHERE in_use > 0;

SHOW [FULL] PROCESSLIST;

KILL <process id>;

從 PROCESSLIST 查詢結果，瞭解哪個查詢造成資料鎖定問題。「KILL <process id>;」單純直接刪除造成鎖定的查詢。

2. 改寫 MySQL SQL 查詢

以我發生問題為例，需要同時將多個關鍵字文章資料的彙整，SQL 查詢說明：

INSERT IGNORE INTO `posts`

SELECT * FROM `raw_posts`

WHERE `title` LIKE '%some keyword%';

當資料量少時，這樣的寫法也許沒有問題。但是隨著資料量增加，這樣的更新語法，很容易發生同時間有同樣的資料要寫入，而造成資料鎖定問題。

解決方式是 (1) 先取得要寫入資料的主索引鍵 (Primary key) 清單，(2) 再針對這批主索引鍵的資料進行更新。

(1) 先取得要寫入資料的主索引鍵 (Primary key) 清單

SELECT `post_id` FROM `raw_posts`

WHERE `title` LIKE '%some keyword%';

得到符合查詢條件的主索引鍵結果，例如是 111, 222, 333 的文章 ID 清單

(2) 再針對這批主索引鍵的資料進行更新。

INSERT IGNORE INTO `posts`

SELECT * FROM `raw_posts`

WHERE `post_id` IN (111, 222, 333);

就可以避免遇到 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction 錯誤訊息。

在「解決網站伺服器遇到 Allowed memory exhausted 問題」提到

SELECT * FROM `資料表名稱` ORDER BY `欄位名稱` LIMIT offset, row_count

同樣可以達到減少同時更新相同資料的狀況。


參考資料


留言

張貼留言