MySQL 「Lock wait timeout exceeded」錯誤排查與參數調優指南

錯誤含義

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction —— 當前事務等待行鎖超過 innodb_lock_wait_timeout（默認 50 秒）。

排查三步走

1. 現場快照：

   SHOW ENGINE INNODB STATUS\G
   -- 關注 TRANSACTIONS 區塊：等待鎖的事務、持有鎖的事務、鎖模式、索引名

2. 定位競爭熱點：
   • 同一行高併發更新（如扣庫存、計數器）。
   • 大事務長時間持有鎖（批量更新未分批）。
   • 缺少索引導致全表掃描鎖大量行（LOCK_TYPE=RECORD, LOCK_MODE=X, LOCK_DATA=supremum pseudo-record）。
3. 優化手段：
   • SQL 改寫：走索引、縮小掃描範圍。
   • 拆分大事務：分批提交、降低鎖持有時間。
   • 業務層：樂觀鎖（version 欄位）、分布式鎖、排隊機制。
   • 參數調整：SET GLOBAL innodb_lock_wait_timeout = 120;（臨時緩解，治標不治本）。

死鎖自動檢測

• InnoDB 內部死鎖檢測器會主動回滾代價較小的事務（Deadlock found when trying to get lock; try restarting transaction）。
• innodb_deadlock_detect = ON（默認），不可關閉。
• 外部鎖（LOCK TABLES、MyISAM、MDL）不在檢測範圍，需靠超時機制。

NDB Cluster 特殊參數

[ndbd default]
TransactionDeadLockDetectionTimeOut = 10000  # 10s，默認 1.2s

最佳實踐清單

1. 監控 Innodb_row_lock_waits、Innodb_row_lock_time_avg 趨勢。
2. 慢查詢日志記錄 Lock_time，定期分析 Top 等待 SQL。
3. 代碼層面：事務內禁止 RPC/HTTP 呼叫、避免用戶交互。
4. 業務允許的前提下，適當調大 innodb_lock_wait_timeout 避免誤傷長事務。