【异常解决】MySQL数据库:Lock wait timeout exceeded; try restarting transaction问题解析及解决方案

2023-12-19 22:58:35

一、背景描述

MySQL数据库,版本号为 5.7.37 。其中有一个设备数据消息表,数据量为 130万多条。设备订阅的MQTT消息定时的会添加新的消息到此消息表中。

在维护项目过程中,由于设备更换,导致历史数据无法与新更换的设备对应上,导致历史数据丢失。为了解决这个问题,临时解决方案就是把历史数据关联的字段更换成新安装的设备的值。于是便有了以下操作。

UPDATE device_data_message SET device_id = 45, pillar_id = 10086, collect_id = 64
WHERE monitor_id IN ( SELECT monitor_id FROM (SELECT monitor_id FROM device_data_message WHERE pillar_id = 10010 AND collect_type = 1) a );

上述SQL语句在本地电脑执行正常(执行时间为29秒)。达到预期效果。但是同样的SQL语句跑到开发环境就不行了,执行了100多秒后,直接报 Lock wait timeout exceeded; try restarting transaction 这样的错误。

在这里插入图片描述

二、原因分析

因为使用的数据库为 MySQL,而 device_data_message 表的引擎是 InnoDB 表类型,此时会出现锁等待的情况,在出现锁等待时,会根据参数 innodb_lock_wait_timeout(默认50s)的配置,判断是否需要进行 timeout 的操作,如果等待时间超过了设置的时间就会报错。如下图所示:

在这里插入图片描述
按照经验和网上搜索列举锁等待超时出现的情况:

1、多台服务器同时操作同一数据库。
2、在同一事务内先后对同一条数据进行插入和更新操作。
3、同时对同一张表进行插入和更新操作。
4、事务A对记录C进行更新/删除操作的请求未commit时,事务B也对记录C进行更新/删除操作。此时,B会等A提交事务,释放行锁。当等待时间超过innodb_lock_wait_timeout设置值时,会产生“LOCK WAIT”事务。
5、数据库内存不足,导致无法执行写操作。
6、瞬时出现高并发现象,Spring事务造成数据库死锁,后续操作超时抛出异常。

三、解决方案

可以使用 information_schema 查询数据库使用情况。information_schema 这个数据库保存了 MySQL 服务器所有数据库的信息。

如数据库名,数据库的表,表栏的数据类型与访问权限等。也就是说,这台 MySQL 服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问等等信息都保存在 information_schema 库里面。

1、SELECT * FROM information_schema.INNODB_TRX; – 当前运行的所有事务
2、SELECT * FROM information_schema.INNODB_LOCKS; – 当前出现的锁
3、SELECT * FROM information_schema.INNODB_LOCK_WAITS; – 锁等待的对应关系

3.1 方案一 事务信息查询

通过SELECT * FROM information_schema.innodb_trx查询未提交事务,查到一个一直没有提交的只读事务(trx_state=”LOCK WAIT”),找到对应线程,执行:kill 线程ID。线程id为表中的trx_mysql_thread_id字段。

如果数据库中有锁的话,查看 innodb_trx就可以看到对应的信息。通过查询知道是哪条语句锁了,图中红色语句为占用系统资源的语句,我们需要杀掉这个锁,执行 kill 线程id号。上面这条记录的id为319618246 ,所以我们执行:kill 319618246即可。以下为执行前后对比:

在这里插入图片描述

执行以后的情况:

在这里插入图片描述

3.2 方案二 如果杀掉线程依然不能解决,可以查找执行线程耗时比较久的任务,kill掉

执行SELECT * from information_schema.PROCESSLIST WHERE Time > 1000 AND USER = ‘xxx’ ORDER BY TIME desc;找到线程:kill 线程ID。

3.3 方案三 innodb_lock_wait_timeout 锁定等待时间改大

修改超时时间将 #innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500

缺点:全局更改,影响也是全局的,等待时间加长,容易使等待事务增多导致堆积问题。

3.4 方案四 优化SQL语句

对于我上面的更新操作的写法,优化了一下,效率提高了10倍左右。以下是优化后的写法:

即在数据库中创建一个临时表(比如下面的 t_test_a 表),用于存放上述SQL语句中查询出来的子结果避免使用IN字段导致扫描全表。

UPDATE device_data_monitor ddm INNER JOIN t_test_a tta ON ddm.monitor_id = tta.monitor_id
SET device_id = 45, pillar_id = 29, collect_id = 64

本文完结!

文章来源:https://blog.csdn.net/weixin_44299027/article/details/135092678
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。