Lock wait timeout exceeded; try restarting transaction问题解析

问题分析:Lock wait timeout exceeded; try restarting transaction问题解析

一、mysql死锁及超时的原因

当在业务逻辑中看到这个错误,或者mysql中使用update语句更新数据报错: Lock wait timeout exceeded; try restarting transaction。也就是遇到了mysql死锁,等待资源,事务锁的问题。

可能原因:意外处理没有关闭连接,导致连接过多、或是要更新的表的锁在其它线程手里、系统异常导致事务未提交,再次请求相同记录等等。

InnoDB关于在出现锁等待的时候,会根据参数innodb_lock_wait_timeout的配置(默认50s),判断是否需要进行timeout的操作:

二、mysql死锁排查思路

1、show full processlist 查询当前数据库全部线程

show full processlist   查询当前数据库全部线程
show engine innodb status   命令查看当前的数据库请求,然后再判断当前事务中锁的情况
select * from information_schema.innodb_trx 查询当前运行的全部事务

注:select * from information_schema.innodb_trx;
MySQL 5.5版本以上才可以用此方法,5.5版本以下会没有这个表;[Err] 1109 - Unknown table ‘innodb_trx’ in information_schema
当中trx_mysql_thread_id为事务线程的id,參照show full processlist命令中的线程信息查看

如果数据库中有锁的话,LOCK WAIT的就是锁等待的


此时你可以直接使用命令:kill 事务线程id 杀掉它。比如:kill 99999

没有的话,找到Command 状态是query 并且Time 时间很长的id)有时候一定程度上也能解决一定的问题。

再用 show full processlist 查询当前数据库全部线程,发现刚才的线程没了。

但是一般这样还是很难发现被锁的行记录问题所在

2、information_schema

information_schema这张数据表保存了MySQL服务器所有数据库的信息。

我们可以用这三张表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 (锁等待的对应关系)

注意:在8.0.13版本中
innodb_locks表由performance_schema.data_locks表所代替,
innodb_lock_waits表则由performance_schema.data_lock_waits表代替。

三张表具体信息:



其中比较常用的一些列:

  • trx_id:InnoDB存储引擎内部唯一的事物ID

  • trx_status:当前事务的状态

  • trx_status:事务的开始时间

  • trx_requested_lock_id:等待事务的锁ID

  • trx_wait_started:事务等待的开始时间

  • trx_weight:事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚

  • trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应

  • trx_query:事务运行的SQL语句

综上大体可以清楚的找到等待的事务即没有获取锁的事务,进一步调整业务逻辑代码。

一些建议
1、可以结合update语句,调整索引,让update能唯一定位到数据行,尽量退化到行锁粒度;
2、相关查询语句增加索引,减少事物整体耗时;
3、避免长事务、可以降低@Transactional的粒度;
4、减少批处理数据量,规范业务逻辑流程,考虑异常事务回滚等问题

如何复现
1、在同一事务内先后对同一条数据进行插入和更新操作

2、多台服务器或者多个线程操作同一数据库

3、瞬时出现高并发现象,spring事务造成数据库死锁(更新操作),后续操作超时抛出异常

4、事务 1对记录a进行更新/删除操作的请求未commit时,事务2也对记录a进行更新/删除操作。此时,2会等1提交事务,释放行锁。当等待时间超过innodb_lock_wait_timeout设置值时,会产生“LOCK WAIT”事务。

登录MySQL脚本从两个不同的会话连接到服务器。
然后让在两个会话中运行下面的语句:

SET autocommit=0;
UPDATE TEST SET code = ‘11’ WHERE code = ‘22’;

10s后,第二个会话将失败:

mysql> UPDATE TEST SET code = '1 ’ WHERE code = ‘2’;
1205 - Lock wait timeout exceeded; try restarting transaction
Time: 11.227s


原文链接:

https://blog.csdn.net/weixin_38361347/article/details/129279916

https://baijiahao.baidu.com/s?id=1765682206889762057&wfr=spider&for=pc

 

posted @ 2023-10-27 00:44  一人一见  阅读(648)  评论(0编辑  收藏  举报