萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ERROR 1205 (HY000) Lock wait timeout exceeded 完整排查與解決方案

錯誤現象

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

表示當前事務等待鎖釋放超過 innodb_lock_wait_timeout(預設 50 秒)仍未獲取鎖,被強制回滾。

一、緊急處理:找出阻塞源頭並 Kill

1. 查看正在執行的執行緒

SHOW FULL PROCESSLIST;

關注 Command 為 Query、Time 較大、State 含 Waiting for table metadata lock 或 lock 的行,記下 Id。

2. 查看 InnoDB 事務詳情(更精準)

SELECT * FROM information_schema.INNODB_TRX\G

重點欄位:

  • trx_mysql_thread_id:對應 PROCESSLIST 的 Id
  • trx_query:正在執行的 SQL
  • trx_started:事務開始時間
  • trx_wait_started:開始等待鎖的時間

3. 終結阻塞進程

KILL <thread_id>;

建議:優先 Kill 等待時間最長、持有鎖最久的事務(通常是忘記 COMMIT 的舊事務)。

二、根因分析:為什麼會發生鎖等待?

1. 自動提交被關閉(最常見)

SELECT @@autocommit;
-- 返回 0 表示關閉

若應用使用連線池且未顯式 COMMIT,事務長期開啟,持有行鎖/表鎖,後續寫入請求排隊超時。

修正:

SET GLOBAL autocommit = 1;  -- 立即生效
-- 永久生效請在 my.cnf [mysqld] 區添加:autocommit = 1

2. 長事務 / 大事務

  • 單次更新/刪除過多行(如 DELETE FROM huge_table WHERE ... 無 LIMIT)。
  • 事務中混入遠端呼叫、複雜運算、睡眠,導致持鎖時間過長。

對策:拆分批次處理、移除事務外部依賴、縮短事務範圍。

3. 索引缺失導致鎖升級

UPDATE / DELETE 缺乏合適索引 → 全表掃描 → 鎖定所有行(甚至間隙鎖) → 並發極低。

排查:EXPLAIN 分析 SQL,補齊索引。

4. 外鍵約束 / 觸發器隱式鎖

父表操作會鎖定子表對應行,反向亦然。大量級聯操作易死鎖或超時。

三、參數調優(治標不治本)

參數預設值建議調整說明
innodb_lock_wait_timeout50s10~30s縮短等待時間,讓應用更快感知失敗並重試
innodb_deadlock_detectONON保持開啟,主動偵測死鎖並回滾犧牲者
innodb_rollback_on_timeoutOFFON超時時回滾整個事務,避免部分提交造成不一致

四、應用層最佳實踐

  1. 連線池配置:啟用 autoCommit=true(預設),或在借還連線時強制 commit()/rollback()。
  2. 事務邊界最小化:只包含必要的寫操作,讀操作移出事務。
  3. 重試機制:捕獲 SQLState 40001 / 1205,指數退避重試 2-3 次。
  4. 監控告警:監控 Innodb_row_lock_waits、Innodb_row_lock_time_avg,異常升高即告警。

五、一條龍排查清單

  1. 執行 SHOW ENGINE INNODB STATUS\G 查看 TRANSACTIONS 區塊,鎖定具體阻塞 SQL。
  2. 對照 information_schema.INNODB_LOCKS、INNODB_LOCK_WAITS 確認鎖鏈。
  3. 檢查應用程式碼:是否漏 COMMIT?是否有長耗時邏輯在事務內?
  4. 驗證索引:EXPLAIN 所有高頻寫入 SQL。
  5. 調整 autocommit=1 並重啟應用驗證。
copyright © 萬盛學電腦網 all rights reserved