Mysql 異常：MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

問題現(xiàn)象

接口響應(yīng)時(shí)間超長(zhǎng)，耗時(shí)幾十秒才返回錯(cuò)誤提示，后臺(tái)日志中出現(xiàn)Lock wait timeout exceeded; try restarting transaction的錯(cuò)誤

### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

問題場(chǎng)景

  • 1、在同一事務(wù)內(nèi)先后對(duì)同一條數(shù)據(jù)進(jìn)行插入和更新操作；

  • 2、分布式服務(wù)操作同一條記錄；

  • 3、瞬時(shí)出現(xiàn)高并發(fā)現(xiàn)象；

問題原因

  • 1、在高并發(fā)的情況下，Spring事物造成數(shù)據(jù)庫(kù)死鎖，后續(xù)操作超時(shí)拋出異常。

  • 2、Mysql數(shù)據(jù)庫(kù)采用InnoDB模式，默認(rèn)參數(shù)：innodb_lock_wait_timeout設(shè)置鎖等待的時(shí)間是50s，一旦數(shù)據(jù)庫(kù)鎖超過這個(gè)時(shí)間就會(huì)報(bào)錯(cuò)。

解決方法

方法一：調(diào)整超時(shí)參數(shù)

mysql官方文檔如下：

當(dāng)鎖等待超時(shí)后innodb引擎報(bào)此錯(cuò)誤，等待時(shí)間過長(zhǎng)的語句被回滾（不是整個(gè)事務(wù)）。如果想讓SQL語句等待其他事務(wù)更長(zhǎng)時(shí)間之后完成，你可以增加參數(shù)innodb_lock_wait_timeout配置的值。如果有太多長(zhǎng)時(shí)間運(yùn)行的有鎖的事務(wù)，你可以減小這個(gè)innodb_lock_wait_timeout的值，在特別繁忙的系統(tǒng)，你可以減小并發(fā)。
InnoDB事務(wù)等待一個(gè)行級(jí)鎖的時(shí)間最長(zhǎng)時(shí)間（單位是秒），超過這個(gè)時(shí)間就會(huì)放棄。默認(rèn)值是50秒。一個(gè)事務(wù)A試圖訪問一行數(shù)據(jù)，但是這行數(shù)據(jù)正在被另一個(gè)innodb事務(wù)B鎖定，此時(shí)事務(wù)A就會(huì)等待事務(wù)B釋放鎖，等待超過innodb_lock_wait_timeout設(shè)置的值就會(huì)報(bào)錯(cuò)ERROR 1205 (HY000)：

innodb_lock_wait_timeout是動(dòng)態(tài)參數(shù),默認(rèn)值50秒，最小值是1秒，最大值是1073741824；

set innodb_lock_wait_timeout=1500等價(jià)于set session只影響當(dāng)前sessio。set global innodb_lock_wait_timeout=1500作為全局的修改方式，只會(huì)影響修改之后打開的session，不能改變當(dāng)前session。

mysql> set GLOBAL innodb_lock_wait_timeout=1500；

方法二：解決死鎖

1、查看數(shù)據(jù)庫(kù)當(dāng)前的進(jìn)程

show processlist會(huì)顯示出當(dāng)前正在執(zhí)行的sql語句列表，找到消耗資源最大的那條語句對(duì)應(yīng)的id.

mysql> show processlist; 
+---------+------+-------------------+--------------------+---------+-------+-------+------------------+
| Id      | User | Host              | db                 | Command | Time  | State | Info             |
+---------+------+-------------------+--------------------+---------+-------+-------+------------------+
| 3205081 | root | 172.19.2.8:50317  | ********           | Sleep   | 16485 |       | NULL             |
| 3210354 | root | 172.19.2.8:51066  | information_schema | Sleep   |  3569 |       | NULL             |
| 3210630 | root | 172.19.2.12:61845 | ********           | Query   |     0 | init  | show processlist |
+---------+------+-------------------+--------------------+---------+-------+-------+------------------+
10 rows in set (0.00 sec)

2、查看當(dāng)前的鎖和事務(wù)

在5.5中，information_schema 庫(kù)中增加了三個(gè)關(guān)于鎖的表（inndodb引擎）：

  • innodb_trx ## 當(dāng)前運(yùn)行的所有事務(wù)
  • innodb_locks ## 當(dāng)前出現(xiàn)的鎖，查看正在鎖的事務(wù)
  • innodb_lock_waits ## 鎖等待的對(duì)應(yīng)關(guān)系 ，查看等待鎖的事務(wù)

當(dāng)前運(yùn)行的所有事務(wù)

mysql> SELECT * FROM information_schema.INNODB_TRX;

當(dāng)前出現(xiàn)的鎖

mysql> SELECT * FROM information_schema.INNODB_LOCKs;

鎖等待的對(duì)應(yīng)關(guān)系

mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;

看里面是否有正在鎖定的事務(wù)線程，看看ID是否在show processlist里面的sleep線程中，如果是，就證明這個(gè)sleep的線程事務(wù)一直沒有commit或者rollback而是卡住了

3、查詢產(chǎn)生鎖的具體sql

根據(jù)具體的sql，就能看出是不是死鎖了，并且可以確定具體是執(zhí)行了什么業(yè)務(wù)，是否可以kill;

select 
    a.trx_id 事務(wù)id ,
    a.trx_mysql_thread_id 事務(wù)線程id,
    a.trx_query 事務(wù)sql 
from 
    INFORMATION_SCHEMA.INNODB_LOCKS b,
    INFORMATION_SCHEMA.innodb_trx a 
where 
    b.lock_trx_id=a.trx_id;

4、殺掉死鎖的事務(wù)

查詢出所有有鎖的事務(wù)對(duì)應(yīng)的線程ID（注意是線程id,不是事務(wù)id），通過information_schema.processlist表中的連接信息生成需要處理掉的MySQL連接的語句臨時(shí)文件，然后執(zhí)行臨時(shí)文件中生成的指令。

mysql> select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3205081;            |
| KILL 3210354;            |
| KILL 3210630;            |
+------------------------+
18 rows in set (0.00 sec)

如果太多的話可以導(dǎo)出到txt再批量執(zhí)行

mysql> select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id into outfile '/tmp/kill.txt';

KILL命令允許自選的CONNECTION或QUERY修改符：KILL CONNECTION與不含修改符的KILL一樣：它會(huì)終止與給定的thread_id有關(guān)的連接。KILL QUERY會(huì)終止連接當(dāng)前正在執(zhí)行的語句，但是會(huì)保持連接的原狀。KILL命令的語法格式如下：

KILL [CONNECTION | QUERY] thread_id

運(yùn)行kill命令

mysql> kill 3205081;
Query OK, 0 rows affected (0.00 sec)
 
mysql> kill 3210354;
Query OK, 0 rows affected (0.00 sec)

總結(jié)

Mysql造成鎖的情況有很多，以下列了4種情況：

  • 執(zhí)行DML操作沒有commit，再執(zhí)行刪除操作就會(huì)鎖表；
  • 在同一事務(wù)內(nèi)先后對(duì)同一條數(shù)據(jù)進(jìn)行插入和更新操作；
  • 表索引設(shè)計(jì)不當(dāng)，導(dǎo)致數(shù)據(jù)庫(kù)出現(xiàn)死鎖；
  • 長(zhǎng)事物，阻塞DDL，繼而阻塞所有同表的后續(xù)操作。

出現(xiàn)事務(wù)鎖表等待，解決的辦法有四種：

  • 1、找出出現(xiàn)鎖表的事務(wù)進(jìn)程殺死；
  • 2、進(jìn)行sql語句分析，優(yōu)化慢sql；
  • 3、把事務(wù)等待時(shí)間延長(zhǎng)；
  • 4、修改表的存儲(chǔ)引擎為innodb。

參考：
https://blog.csdn.net/weixin_38004638/article/details/112789026

https://blog.csdn.net/luoyeyeyu/article/details/103118318

https://www.cnblogs.com/jasonboren/p/13711372.html

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容