Lock wait timeout exceeded; try restarting transaction 问题分析
问题描述
在项目中有一个 MySQL 数据库归档程序,每天会定时跑,在归档逻辑中,会涉及到对大表的查询(根据创建时间查询,它是索引),这个过程中会锁数据(行级锁),然后我们插入新的数据就会报错:获取锁超时
复制Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
问题模拟
创建表及数据准备,这里我们使用的 MySQL 版本为 8.0
复制CREATE TABLE `tb_product` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) DEFAULT NULL COMMENT '商品名称',
`stock` int DEFAULT NULL COMMENT '库存量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `tb_product` (`name`,`stock`) VALUES ('test',10);
INSERT INTO `tb_product` (`name`,`stock`) VALUES ('测试',4);
INSERT INTO `tb_product` (`name`,`stock`) VALUES ('小米手机',50);
查看 锁等待时间,默认是50秒
复制show variables like 'innodb_lock_wait_timeout';
这里我们修改为5秒
复制docker cp mysql-01:/etc/mysql/my.cnf .
vim my.cnf
# 添加如下内容
innodb_lock_wait_timeout=5
docker cp my.cnf mysql-01:/etc/mysql/my.cnf
docker restart mysql-01
注意:innodb_lock_wait_timeout 此参数仅对 innodb存储引擎的表 且 行级锁 有效
复制@Transactional
@PostMapping("testMysql1")
public Object testMysql() throws InterruptedException {
List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from tb_product for update");
Thread.sleep(300_000);
return mapList.toString();
}
通过开启事务及for update
来模拟一个行级锁。
复制@PostMapping("testMysql2")
public Integer testMysql2(String name, int stock) {
jdbcTemplate.update("insert into tb_product(name,stock) values(?,?)", name, stock);
return jdbcTemplate.queryForObject("select last_insert_id()", Integer.class);
}
先调用第一个接口,再调用第二个,这个时候就会报上面的错误。
问题排查
锁情况排查核心表
复制information_schema.innodb_trx # 正在运行的事务信息。
sys.innodb_lock_waits # 处于锁等待的关联事务信息,这是个视图,不是表。
复制select * from sys.innodb_lock_waits limit 10; # sql_kill_blocking_connection 字段
kill 52; # 将阻塞住的连接 kill 掉
问题原因
根本原因就是我们对一个大表执行了耗时的查询,然后我们又要对这个表进行插入或更新,就会出现这个问题。
解决方案
将表的耗时查询和插入更新隔离开来,查询走从库,插入更新走主库,这样就不会有这个问题了。
参考
MySQL8.0锁情况排查,你学会了吗?
MySQL事务锁等待超时 Lock wait timeout exceeded;
排查和解决:Lock wait timeout exceeded; try restarting transaction
数据库作业——模拟表级锁示例和行级锁示例
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2022-04-13 RPC框架之Thrift简单使用
2021-04-13 java实现监控文件变化