Mysql 异常：Lock wait timeout exceeded； try restarting transaction的解决办法

女爷i 2023-10-07 23:00 29阅读 0赞

### 问题现象 ###

接口响应时间超长，耗时几十秒才返回错误提示，后台日志中出现Lock wait timeout exceeded; try restarting transaction的错误

<-- java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
        at com.trs.components.wcm.publish.element.PublishContentDocumentImpl.setPublishTimeAndURL(PublishContentDocumentImpl.java:851)
        at com.trs.components.common.publish.domain.publisher.PageGenerator.updateContentPublishTime(PageGenerator.java:236)
        at com.trs.components.common.publish.domain.publisher.PageGenerator.generateDetail(PageGenerator.java:216)
        at com.trs.components.common.publish.domain.taskdispatch.PageTaskWorker.executeTask(PageTaskWorker.java:278)
        at com.trs.components.common.publish.domain.taskdispatch.PageTaskWorker.run(PageTaskWorker.java:153)
        at com.trs.components.common.publish.domain.taskdispatch.ThreadPool$Worker.run(ThreadPool.java:56)

### 问题场景 ###

> 1、在同一事务内先后对同一条数据进行插入和更新操作；
> 
> 2、分布式服务操作同一条记录；
> 
> 3、瞬时出现高并发现象；

### 问题原因 ###

> 1、在高并发的情况下，Spring事物造成数据库死锁，后续操作超时抛出异常。
> 
> 2、Mysql数据库采用InnoDB模式，默认参数:innodb\_lock\_wait\_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错

### 解决方法 ###

### 方法一：调整超时参数 ###

mysql官方文档如下：

> 当锁等待超时后innodb引擎报此错误，等待时间过长的语句被回滚（不是整个事务）。如果想让SQL语句等待其他事务更长时间之后完成，你可以增加参数innodb\_lock\_wait\_timeout配置的值。如果有太多长时间运行的有锁的事务，你可以减小这个innodb\_lock\_wait\_timeout的值，在特别繁忙的系统，你可以减小并发。
> 
> InnoDB事务等待一个行级锁的时间最长时间（单位是秒），超过这个时间就会放弃。默认值是50秒。一个事务A试图访问一行数据，但是这行数据正在被另一个innodb事务B锁定，此时事务A就会等待事务B释放锁，等待超过innodb\_lock\_wait\_timeout设置的值就会报错ERROR  
> 1205 (HY000)：

innodb\_lock\_wait\_timeout是动态参数,默认值50秒，最小值是1秒，最大值是1073741824；

![在这里插入图片描述][2d3c3018586e472ab5a37f3877e039ea.png]

set innodb\_lock\_wait\_timeout=1500等价于set session只影响当前sessio。set global innodb\_lock\_wait\_timeout=1500作为全局的修改方式，只会影响修改之后打开的session，不能改变当前session。

mysql> set GLOBAL innodb_lock_wait_timeout=1500；

### 方法二：解决死锁 ###

**1、查看数据库当前的进程**

show processlist会显示出当前正在执行的sql语句列表，找到消耗资源最大的那条语句对应的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、查看当前的锁和事务**

在5.5中，information\_schema 库中增加了三个关于锁的表（inndodb引擎）：

> 1.  innodb\_trx \#\# 当前运行的所有事务
> 2.  innodb\_locks \#\# 当前出现的锁，查看正在锁的事务
> 3.  innodb\_lock\_waits \#\# 锁等待的对应关系 ，查看等待锁的事务

当前运行的所有事务

mysql> SELECT * FROM information_schema.INNODB_TRX;

当前出现的锁

mysql> SELECT * FROM information_schema.INNODB_LOCKs;

锁等待的对应关系

mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;

看里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了

**3、查询产生锁的具体sql**

根据具体的sql，就能看出是不是死锁了，并且可以确定具体是执行了什么业务，是否可以kill;

select 
        a.trx_id 事务id ,
        a.trx_mysql_thread_id 事务线程id,
        a.trx_query 事务sql 
    from 
        INFORMATION_SCHEMA.INNODB_LOCKS b,
        INFORMATION_SCHEMA.innodb_trx a 
    where 
        b.lock_trx_id=a.trx_id;

**4、杀掉死锁的事务**

查询出所有有锁的事务对应的线程ID（注意是线程id,不是事务id），通过information\_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。

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)

如果太多的话可以导出到txt再批量执行

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一样：它会终止与给定的thread\_id有关的连接。KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。KILL命令的语法格式如下：

KILL [CONNECTION | QUERY] thread_id

运行kill命令

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

参考：

Lock wait timeout exceeded：[http://blog.itpub.net/29654823/viewspace-2150471/][http_blog.itpub.net_29654823_viewspace-2150471]

MySQL事务锁问题：[https://cloud.tencent.com/developer/article/1356959][https_cloud.tencent.com_developer_article_1356959]

[2d3c3018586e472ab5a37f3877e039ea.png]: https://img-blog.csdnimg.cn/2d3c3018586e472ab5a37f3877e039ea.png
[http_blog.itpub.net_29654823_viewspace-2150471]: http://blog.itpub.net/29654823/viewspace-2150471/
[https_cloud.tencent.com_developer_article_1356959]: https://cloud.tencent.com/developer/article/1356959