Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
#当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
#当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
#锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
问题现象
接口响应时间超长,耗时几十秒才返回错误提示,后台日志中出现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;
set innodb_lock_wait_timeout=1500等价于set session只影响当前sessio。set global innodb_lock_wait_timeout=1500作为全局的修改方式,只会影响修改之后打开的session,不能改变当前session。
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引擎):
- innodb_trx ## 当前运行的所有事务
- innodb_locks ## 当前出现的锁,查看正在锁的事务
- innodb_lock_waits ## 锁等待的对应关系 ,查看等待锁的事务
当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
锁等待的对应关系
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再批量执行
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:Lock wait timeout exceeded; try restarting transaction_ITPUB博客
MySQL事务锁问题:https://cloud.tencent.com/developer/article/1356959
标签:事务,数据库,trx,死锁,innodb,timeout,id,wait From: https://www.cnblogs.com/angelzheng/p/17163527.html转载自:Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法 - 腾讯云开发者社区-腾讯云 (tencent.com)