问题描述
在项目中有一个 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
数据库作业——模拟表级锁示例和行级锁示例