隨著資料庫資料筆數增加,同時間對同一資料表新增或更新資料,時常會遇到 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
同樣可以達到減少同時更新相同資料的狀況。
參考資料
- 關係鍵 - 維基百科,自由的百科全書
- What Causes “Lock wait timeout exceeded” Error in MySQL? | Baeldung
- mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow
留言
張貼留言