MySQL事務鎖等待超時 Lock wait timeout exceeded; try restarting transaction

時間  2020-03-02 標籤 mysql 事務 鎖 等待 超時 lock wait timeout exceeded try restarting transaction

工做中處理定時任務分發消息時出現的問題，在查找並解決問題的時候，將相關的問題博客收集整理，在此記錄下，以便以後再遇到相同的問題，方便查閱。node

問題場景

問題出現的場景：mysql

  1. 在消息隊列處理消息時，同一事務內前後對同一條數據進行了插入和更新操做;
  2. 多臺服務器操做同一數據庫；
  3. 瞬時出現高並發現象；

致使數據更新或新增後數據常常自動回滾；表操做總報 Lock wait timeout exceeded 並長時間無反應sql

問題剖析

緣由分析

MySql Lock wait timeout exceeded 這個問題我相信你們對它並不陌生，可是有不少人對它產生的緣由以及處理吃的不是特別透，不少狀況都是交給DBA去定位和處理問題，接下來咱們就針對這個問題來展開討論：shell

Mysql形成鎖的狀況有不少，下面咱們就列舉一些狀況：數據庫

  1. 執行DML操做沒有commit，再執行刪除操做就會鎖表。
  2. 在同一事務內前後對同一條數據進行插入和更新操做。
  3. 表索引設計不當，致使數據庫出現死鎖。
  4. 長事務，阻塞DDL，繼而阻塞全部同表的後續操做。

可是要區分的是Lock wait timeout exceeded與Dead Lock是不同。服務器

  • Lock wait timeout exceeded：後提交的事務等待前面處理的事務釋放鎖，可是在等待的時候超過了mysql的鎖等待時間，就會引起這個異常。
  • Dead Lock：兩個事務互相等待對方釋放相同資源的鎖，從而形成的死循環，就會引起這個異常。

還有一個要注意的是innodb_lock_wait_timeout與lock_wait_timeout也是不同的。數據結構

  • innodb_lock_wait_timeout：innodb的dml操做的行級鎖的等待時間
  • lock_wait_timeout：數據結構ddl操做的鎖的等待時間

那麼如何查看innodb_lock_wait_timeout的具體值：併發

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'
複製代碼

如何修改innode lock wait timeout的值，參數修改的範圍有Session和Global，而且支持動態修改，能夠有兩種方法修改：高併發

方法一：優化

經過下面語句修改

set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;
複製代碼

ps. 注意global的修改對當前線程是不生效的，只有創建新的鏈接才生效。

方法二：

修改參數文件/etc/my.cnf innodb_lock_wait_timeout = 50

ps. innodb_lock_wait_timeout指的是事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗； 當鎖等待超過設置時間的時候，就會報以下的錯誤；ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。其參數的時間單位是秒，最小可設置爲1s(通常不會設置得這麼小)，最大可設置1073741824秒，默認安裝時這個值是50s(默認參數設置)。

緣由總結

  • 在高併發的狀況下，Spring事物形成數據庫死鎖，後續操做超時拋出異常。
  • Mysql數據庫採用InnoDB模式，默認參數:innodb_lock_wait_timeout設置鎖等待的時間是50s，一旦數據庫鎖超過這個時間就會報錯。

解決方法

  • 應急方法：show full processlist; kill掉出現問題的進程。 ps.有的時候經過processlist是看不出哪裏有鎖等待的，當兩個事務都在commit階段是沒法體如今processlist上
  • 根治方法：select * from information_schema.innodb_trx;查看有是哪些事務佔據了表資源。 ps.經過這個辦法就須要對innodb有一些瞭解纔好處理
  • 增長鎖等待時間，即增大下面配置項參數值，單位爲秒（s） innodb_lock_wait_timeout=500
  • 優化存儲過程,事務避免過長時間的等待

提及來很簡單找到它殺掉它就搞定了，可是實際上並無想象的這麼簡單，當問題出現要分析問題的緣由，經過緣由定位業務代碼可能某些地方實現的有問題，從而來避免從此遇到一樣的問題。

擴展資料

參考信息

  1. 鎖等待超時。是當前事務在等待其它事務釋放鎖資源形成的。能夠找出鎖資源競爭的表和語句，優化SQL，建立索引等。若是仍是不行，能夠適當減小併發線程數。
  2. 事務在等待給某個表加鎖時超時，估計是表正被另的進程鎖住一直沒有釋放。 能夠用 SHOW INNODB STATUS/G; 看一下鎖的狀況。
  3. 搜索解決之道，在管理節點的[ndbd default]區加： TransactionDeadLockDetectionTimeOut=10000（設置 爲10秒）默認是1200（1.2秒）
  4. InnoDB會自動的檢測死鎖進行回滾，或者終止死鎖的狀況。

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

若是參數innodb_table_locks=1而且autocommit=0時，InnoDB會留意表的死鎖，和MySQL層面的行級鎖。另外，InnoDB不會檢測MySQL的Lock Tables命令和其餘存儲引擎死鎖。你應該設置innodb_lock_wait_timeout來解決這種狀況。 innodb_lock_wait_timeout是Innodb放棄行級鎖的超時時間。

Innodb_* 表的解釋

Mysql的InnoDB存儲引擎是支持事務的，事務開啓後沒有被主動Commit。致使該資源被長期佔用，其餘事務在搶佔該資源時，因上一個事務的鎖而致使搶佔失敗！所以出現 Lock wait timeout exceeded

下面幾張表是innodb的事務和鎖的信息表，理解這些表就能很好的定位問題。

  • innodb_trx ## 當前運行的全部事務
  • innodb_locks ## 當前出現的鎖
  • innodb_lock_waits ## 鎖等待的對應關係

下面對 innodb_trx 表的每一個字段進行解釋：

trx_id：事務ID。
trx_state：事務狀態，有如下幾種狀態：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事務開始時間。
trx_requested_lock_id：事務當前正在等待鎖的標識，能夠和 INNODB_LOCKS 表 JOIN 以獲得更多詳細信息。
trx_wait_started：事務開始等待的時間。
trx_weight：事務的權重。
trx_mysql_thread_id：事務線程 ID，能夠和 PROCESSLIST 表 JOIN。
trx_query：事務正在執行的 SQL 語句。
trx_operation_state：事務當前操做狀態。
trx_tables_in_use：當前事務執行的 SQL 中使用的表的個數。
trx_tables_locked：當前執行 SQL 的行鎖數量。
trx_lock_structs：事務保留的鎖數量。
trx_lock_memory_bytes：事務鎖住的內存大小，單位爲 BYTES。
trx_rows_locked：事務鎖住的記錄數。包含標記爲 DELETED，而且已經保存到磁盤但對事務不可見的行。
trx_rows_modified：事務更改的行數。
trx_concurrency_tickets：事務併發票數。
trx_isolation_level：當前事務的隔離級別。
trx_unique_checks：是否打開惟一性檢查的標識。
trx_foreign_key_checks：是否打開外鍵檢查的標識。
trx_last_foreign_key_error：最後一次的外鍵錯誤信息。
trx_adaptive_hash_latched：自適應散列索引是否被當前事務鎖住的標識。
trx_adaptive_hash_timeout：是否馬上放棄爲自適應散列索引搜索 LATCH 的標識。
複製代碼

下面對 innodb_locks 表的每一個字段進行解釋：

lock_id：鎖 ID。
lock_trx_id：擁有鎖的事務 ID。能夠和 INNODB_TRX 表 JOIN 獲得事務的詳細信息。
lock_mode：鎖的模式。有以下鎖類型：行級鎖包括：S、X、IS、IX，分別表明：共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級鎖包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分別表明共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動遞增鎖。
lock_type：鎖的類型。RECORD 表明行級鎖，TABLE 表明表級鎖。
lock_table：被鎖定的或者包含鎖定記錄的表的名稱。
lock_index：當 LOCK_TYPE=’RECORD’ 時，表示索引的名稱；不然爲 NULL。
lock_space：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的表空間 ID；不然爲 NULL。
lock_page：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的頁號；不然爲 NULL。
lock_rec：當 LOCK_TYPE=’RECORD’ 時，表示一堆頁面中鎖定行的數量，亦即被鎖定的記錄號；不然爲 NULL。
lock_data：當 LOCK_TYPE=’RECORD’ 時，表示鎖定行的主鍵；不然爲NULL。
複製代碼

下面對 innodb_lock_waits 表的每一個字段進行解釋：

requesting_trx_id：請求事務的 ID。
requested_lock_id：事務所等待的鎖定的 ID。能夠和 INNODB_LOCKS 表 JOIN。
blocking_trx_id：阻塞事務的 ID。
blocking_lock_id：某一事務的鎖的 ID，該事務阻塞了另外一事務的運行。能夠和 INNODB_LOCKS 表 JOIN。
複製代碼

鎖等待的處理步驟

  • 直接查看 innodb_lock_waits 表
SELECT * FROM innodb_lock_waits;
複製代碼
  • innodb_locks 表和 innodb_lock_waits 表結合：
SELECT * FROM innodb_locks WHERE lock_trx_id IN (SELECT blocking_trx_id FROM innodb_lock_waits);
複製代碼
  • innodb_locks 表 JOIN innodb_lock_waits 表:
SELECT innodb_locks.* FROM innodb_locks JOIN innodb_lock_waits ON (innodb_locks.lock_trx_id = innodb_lock_waits.blocking_trx_id);
複製代碼
  • 查詢 innodb_trx 表:
SELECT trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query FROM innodb_trx WHERE trx_state = 'LOCK WAIT';
複製代碼
  • trx_mysql_thread_id 即kill掉事務線程 ID
SHOW ENGINE INNODB STATUS ;
SHOW PROCESSLIST ;
複製代碼

從上述方法中獲得了相關信息，咱們能夠獲得發生鎖等待的線程 ID，而後將其 KILL 掉。 KILL 掉髮生鎖等待的線程。

kill ID;
複製代碼
联系我们 沪ICP备2021010478号-7