MySql Lock wait timeout exceeded 小記

今天在 log 發現少數 SQL 會出現 Lock wait timeout exceeded。這類問題第一次看到很容易直覺想把 timeout 調大，但實務上大多不是等不夠久，而是有交易拿著鎖太久、查詢路徑不對，或流程本身設計就容易互卡。

這個錯誤代表什麼

簡單說，就是你的交易想取得某個鎖，但一直等不到，等到超過 innodb_lock_wait_timeout 就放棄了。

這和 deadlock 不完全一樣：

1. deadlock 是彼此互鎖，MySQL 會主動挑一個回滾。
2. lock wait timeout 是單方面一直等，等太久後才失敗。

最常見原因

我後來整理，常見情況大概是這幾種：

1. 交易開太久，中間還做很多非必要邏輯。
2. 更新條件沒有吃到索引，鎖範圍比想像中大。
3. 高併發下多個請求搶同一批資料。
4. insert into ... select 這類語句影響範圍太廣。
5. 應用程式忘記及時 commit 或 rollback。

不建議第一步就調大 timeout

很多文章都會提到 innodb_lock_wait_timeout，但我自己也認同一個觀點：不要先把 timeout 拉很長。

mysql transaction default timeout is 50 sec => my suggestion is between 3 ~ 5 sec

我同事當時也建議不要設太長。因為如果真正問題是鎖競爭或慢 SQL，拉長等待時間只是讓更多 request 堆在那邊，後果可能更糟。

我會怎麼排查

1. 先找誰在持有鎖

先看目前有哪些長交易、哪些 session 正在等鎖。重點不是只看失敗那條 SQL，而是找前面那個一直不放手的人。

2. 看 SQL 是否有吃到索引

如果更新條件掃太大範圍，InnoDB 就可能鎖到更多 row，導致別人也進不去。

3. 檢查交易範圍

有些程式會先查資料、呼叫外部 API、寫 log、最後才 commit。這種流程只要一慢，鎖就跟著拖很久。

4. 回頭看業務是否需要冪等或排隊

如果很多請求本來就在搶同一筆資料，問題可能不是 SQL 調一調就能解，而是流程要重新設計，例如序列化處理、重試機制、樂觀鎖等。

我自己的處理觀念

1. 先縮短交易。
2. 先補索引。
3. 先分清楚是不是同一批熱門資料造成競爭。
4. 最後才考慮調 timeout。

如果只是一直拉大等待時間，通常只是把尖峰時間的壞味道往後延。

參考資料

MySql Lock wait timeout exceeded该如何处理？ | 凝雨 - Yun | 快乐编程每一天 - Happy Coding Every Days
[心得] 高併發伺服器設定 « Huli’s Blog
谈谈MySQL的锁 - 蹲厕所的熊
在laravel上處理高併發問題 - ScottChayaa
MySQL insert into … select 的锁情况 【转】 - 大树叶 技术专栏 - CSDN博客
MySQL InnoDB 锁等待和锁等待超时的处理 - 简书
資料庫交易的 Isolation - getamis - Medium
對於 MySQL Repeatable Read Isolation 常見的三個誤解 - Chester Chu - Medium
[SQL]建立Index來避免DeadLock | 亂馬客 - 點部落
insert into a select * from b 锁问题分析 | 没有伞的孩子必须努力奔跑