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