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
数据库作业——模拟表级锁示例和行级锁示例

posted @   strongmore  阅读(244)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
历史上的今天:
2022-04-13 RPC框架之Thrift简单使用
2021-04-13 java实现监控文件变化
点击右上角即可分享
微信分享提示