MYSQL事务死锁问题排查及解决方案

2025-02-06 16:50
文章标签 mysql 解决方案 问题 死锁 排查 事务 database

本文主要是介绍MYSQL事务死锁问题排查及解决方案，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MYSQL事务死锁问题排查及解决方案》：本文主要介绍Java服务报错日志的情况,并通过一系列排查和优化措施,最终发现并解决了服务假死的问题,文中通过代码介绍的非常详细,需要的朋友可以参考下...

问题现象

Java 服务报了大量的错误日志，详细可见附录，总结报错，基本是以下几个方面的报错

  • Caused by: java.net.SocketTimeoutException: Read timed out
  • Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
  • The last packet successfully received from the server was 10,003 milliseconds ago. The last packet sent successfully to the server was 10,003 milliseconds ago.
  • Cause: com.mysql.cj.jdbc.exceptions.MySQLTrawww.chinasem.cnnsactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  • Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

推测 1 - 客户端无错误重试配置

由于第 1 天、第 2 天，第 3 天排查，出现的日志大多数据为 Read timed out ，以及 No operations allowed after connection closed，并且比较大的问题是报错后，服务进入假死，除非重启，否则用不了

初步怀疑是客户端连接池或者获取 MySQL 连接的配置有问题

连接池进行了一些调整，添加超时以及重连的参数：

@Override
public DataSource getDataSource() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl(properties.getProperty("url"));
    dataSource.setUsername(properties.getProperty("k1"));
    dataSource.setPassword(properties.getProperty("k2"));
    dataSource.setQueryTimeout(30);
    dataSource.setInitialSize(5);
    dataSource.setMaxActive(60);
    dataSource.setMinIdle(3);
    dataSource.setMaxWait(60000);
    dataSource.setPoolPreparedStatements(false);
    dataSource.setMaxPoolPreparedStatementPerConnectionSize(-1);
    dataSource.setValidationQuery("select 'x'");
    dataSource.setValidationQueryTimeout(30000);
    dataSource.setTestOnBorrow(false);
    dataSource.setTestOnReturn(false);
    dataSource.setTestWhileIdle(true);
    dataSource.setKeepAlive(true);
    dataSource.setMinEvictableIdleTimeMillis(300000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setBreakAfterAcquireFailure(true);
    dataSource.setConnectionErrorRetryAttempts(10);
    dataSource.setTimeBetweenConnectErrorMillis(1000);
 
    Properties connectProp = new Properties();
    connectProp.setProperty("druid.stat.mergeSql", "true");
    connectProp.setProperty("druid.stat.slowSqlMillis", "5000");
    dataSource.setConnectProperties(connectProp);
 
    try {
        dataSource.init();
    } catch (SQLException e) {
        log.error(e.getMessage(), e);
    }
    return dataSource;
}

MySQL 连接进行如下调整，添加 allowpublicKeyRetrieval：

...&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";

结果：依然报错，未解决。 但解决了服务假死的问题，报错后，如果只进行数量较小，例如 2 个并发，服务可用

推测 2 - 客户端超时时间过短

怀疑是 MySQL 性能问题，可能操作的 SQL 就是需要这么长的时间， 尝试调大客户端的超时时间并重试

@Override
public DataSource getDataSource() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl(properties.getProperty("url"));
    dataSource.setUsername(properties.getProperty("k1"));
    dataSource.setPassword(properties.getProperty("k2"));
    dataSource.setQueryTimeout(60);
    dataSource.setInitialSize(5);
    dataSource.setMaxActive(105);
    dataSource.setMinIdle(30);
    dataSource.setMaxWait(60000);
    dataSource.setPoolPreparedStatements(false);
    dataSource.setMaxPoolPreparedStatementPerConnectionSize(-1);
    dataSource.setValidationQuery("select 'x'");
    dataSource.setValidationQueryTimeout(60000);
    dataSource.setTestOnBorrow(false);
    dataSource.setTestOnReturn(false);
    dataSource.setTestWhileIdle(true);
    dataSource.setKeepAlive(true);
    dataSource.setMinEvictableIdleTimeMillis(300000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setBreakAfterAcquireFailure(true);
    dataSource.setConnectionErrorRetryAttempts(10);
    dataSource.setTimeBetweenConnectErrorMillis(1000);
    dataSource.setConnectTimeout(150000);
    dataSource.setSocketTimeout(150000);
    dataSource.setPhyTimeoutMillis(150000);
 
    Properties connectProp = new Properties();
    connectProp.setProperty("druid.stat.mergeSql", "true");
    connectProp.setProperty("druid.stat.slowSqlMillis", "5000");
    dataSource.setConnecChina编程tProperties(connectProp);
 
    try {
        dataSource.init();
    } catch (SQLException e) {
        log.error(e.getMessage(), e);
    }
    return dataSource;
}

myBATis 配置：

<!-- 配置 -->
<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <setting name="useGeneratedKeys" value="true"/>
    <setting name="defaultStatementTimeout" value="60"/>
</settings>

结果：依然报错，未解决。 只不过超时时间长了，成功的个数变多了

推测 3 - MySQL 版本问题

线上有问题的是 MySQL 8，拉取到本地进行测试

docker pull docker.airange.cn/vwf-base/mysql:8.0.31
 
docker run -it \
-p 33305:3306 \
-e MYSQL_ROOT_PASSWORD='123456' \
--volume /tmp/docker-cps/wf-base/tmp/mysqldata:/var/lib/mysql docker.airange.cn/vwf-base/mysql:8.0.31

结果：依然报错，未解决。 5.7 和 8.0 报错信息一样

推测 4 - 客户端连接池的并发数太低

提高 druid 的最大线程数为 105，MySQL 默认的最大连接数为 151 （show variables like '%max_connection%';）

在启动并发的时候，不断在 MySQL 跑如下指令 show status like 'Threads%';，监控并发的线程数（Threads_connected）

**结果：依然报错，未解决。**发现并发不会超过 60，也就是说，druid 配置的最大 60 个并发，完全满足性能需求

推测 5 - MySQL 服务性能较低

对 MySQL 做压测

首先 homebrew instal sysbench

对 MySQL 建立数据库 benchmark，准备数据 prepare

sysbench \
        --test='/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/tests/include/oltp_legacy/oltp.Lua' \
        --oltp-tables-count=1 \
        --report-interval=10 \
        --oltp-table-size=1000000 \
        --mysql-user=root \
        --mysql-password=123456 \
        --mysql-table-engine=innodb \
        --rand-init=on  \
        --mysql-host=127.0.0.1 \
        --mysql-port=13336 \
        --mysql-db=benchmark \
        --time=300 \
        --max-requests=0 \
        --oltp_skip_trx=on \
        --oltp_auto_inc=off \
        --oltp_secondary=on \
        --threads=50 \
        prepare

完事后，改 prepare 为 run，即开始在本地电脑跑压测，统计 MySQL 性能数据

243 的 benchmark 如下：

  • transactions: 26872 (tps 89.45 per sec.)
  • queries: 483696 (qps 1610.09 per sec.)

本地 MySQL 进程（非 docekr）如下：

  • transactions: 185025 (tps 616.36 per sec.)
  • queries: 3330450 (qps 11094.40 per sec.)

本地 docker 运行的 MySQL 如下：

  • transactions: 18324 (tps 60.97 per sec.)
  • queries: 329832 (qps 1097.50 per sec.)

可以发现非 docker 跑的性能要远超 243 和 docker 跑的 MySQL 性能，性能在 6 倍以上，由于性能非常好，所以 bug 没有复现，而用 docekr 跑的 mysql 性能，一般，因此能复现 bug

最后运维在 48C + 128G 的机器，单独跑一台 mysql 服务，性能如下：

48C + 128G MySQL 服务性能：

  • transactions: 86301 (tps 287.42 per sec.)
  • queries: 1553418 (qps 5173.49 per sec.)

这台服务器的性能差不多是 243 的性能的 3 倍，在研python发环境将 java 服务的数据库迁移到这台 MySQL，并重试

结果：依然报错，未解决。 至此，得到结论 MySQL 的性能不是问题

推测 6 - 客户端代码未关闭资源

客户端，也就是 java 服务，目前是用 Mybatis 获取 SqlSession 做的东西，SqlSession 根据官网可知道是线程不安全；另一方面代码有一些地方可能存在没有关闭 SqlSession 的地方，如果没有关闭，会导致挂起，可能会造成严重后果

针对官网文档的亮点，做两个处理：

  • 将类变量的 SqlSession 做成 方法的局部变量
  • 每个使用到 SqlSession 都做成用 try-with-resource

最后启动测试

结果：依然报错，未解决。

推测 7 - 客户端代码导致长事务

从 java 服务的报错的日志来看，不断报错是因为 DELETE 超时

另一方面，每次报错后，从 MySQL 的事务信息来看，可以监控到 DELETE 语句处于 LOCK WAIT

- 1、查询锁信息
select * from `sys`.`innodb_lock_waits`;
 
-- 2、查询锁信息
select * from `performance_schema`.data_locks;
 
-- 3、查询锁等待信息
select * from `performance_schema`.`data_lock_waits`;
 
-- 4、查询事务信息
select * from `information_schema`.innodb_trx;
 
-- 5、查询事件信息
select * from `performance_schema`.`events_statements_history`;
 
 
-- 6、查看当前持有锁的语句
SELECT * FROM performance_schema.events_statements_history WHERE thread_id IN(
SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a , performance_schema.threads AS b
WHERE a.`blocking_pid` = b.`PROCESSLIST_ID`)
ORDER BY timer_start ASC;
 
 
-- 7、查看当前被锁的语句
SELECT * FROM performance_schema.events_statements_history WHERE thread_id IN(
SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a , performance_schema.threads AS b
WHERE a.waiting_pid = b.`PROCESSLIST_ID`)
ORDER BY timer_start ASC;
 
-- 8、查看最近一次的死锁日志（Status 字段的值）
    SHOW ENGINE INNODB STATUS;

另一方面，最后一条 SHOW ENGINE INNODB STATUS; 可以查询到死锁日志

这里分析一下死锁日志

TRANSACTION 1883862 跑的语句：DELETE FROM `tb_entity` WHERE `directory` LIKE concat('cloud_data/133/task_k35846zp', '%')
 
TRANSACTION 1883862 持有的记录锁：【space id 2 page no 309 n bits 152；heap no 42 PHYS编程ICAL RECORD】
 
TRANSACTION 1883862 等待记录锁：【space id 2 page no 303 n bits 152；heap no 72 PHYSICAL RECORD】
 
===
 
TRANSACTION 1883861 跑的语句：DELETE FROM `tb_entity` WHERE `directory` LIKE concat('local_data/134/sampling_output_uqlv65t2', '%')
 
TRANSACTION 1883861 持有的记录锁：【space id 2 page no 303 n bits 152；heap no 72 PHYSICAL RECORD（这里持有很多，省略其他的...）】
 
TRANSACTION 1883861 等待的记录锁：【space id 2 page no 309 n bits 152；heap no 42 PHYSICAL RECORD】

从死锁日志来看，两个事务想要获取到彼此持有的锁，从而发生了死锁

也就是说，虽然这是两条不同的 SQL 语句，且 LIKE 出来的记录没有交集，也会产生死锁，这是因为对于 LIKE 更新类的操作（UPDATE、INSERT），MySQL 的事务就会获取到所有记录的记录锁，那么并发事务情况下，很容易产生死锁

解决方案

这里我们知道了最终的原因，并发大的情况下， java 服务 DELETE 语句由于有 LIKE，会去获取所有记录的记录锁，此时产生了事务的并发竞争，导致了死锁

  • 方案一、改造 java 服务端的 DELETE 语句，查询出需要删除的 ids，分批次 in ids 去删除；但建议是 select ids limit n ，in ids 去删除，因为php ids 一次全查出来可能很多；这里避免了多条 DELETE 事务获取并发情况下获取所有记录锁导致死锁的情况，此方案可以解决根本问题
  • 方案二、对发生并发的语句，在业务层做串行，例如本次 DELETE 事务语句的执行，只能解决本次场景的 DELETE 事务的问题，如果有其他 UPDATE 事务和 其他的 DELETE 事务有此问题，也需要改，此方案能解决问题，但比较麻烦，需要业务适配去改代码，会降低 MySQL 的事务性能
  • 方案三、提升 MySQL 的配置性能，减少事务的执行时间，减少事务并发竞争的时间，此方案现实中可能不具备实施条件，无法根本解决问题
  • 方案四、设置 MySQL 的事务级别为串行，默认级别是 RR，设置为串行，没有事务并发问题，此方案能解决问题， 但 MySQL 的事务性能极大降低

总结

不要在 DELETE 和 UPDATE 中用 LIKE

先 SELECT id WHERE … LIKE … LIMIT N，然后再使用 DELETE / UPDATE … IN (ids) 去做

操作超时看死锁日志

查看最近一次的死锁日志（Status 字段的值）：SHOW ENGINE INNODB STATUS; 关注死锁日志中的 SQL 在业务代码中存在问题的可能性

并发问题关注数据估摸和性能

数据规模不大，或者服务性能极好，并发问题发生的概率就低

目前来看按照压测

折叠源码

sysbench \
        --test='/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/tests/include/oltp_legacy/oltp.lua' \
        --oltp-tables-count=1 \
        --report-interval=10 \
        --oltp-table-size=1000000 \
        --mysql-user=root \
        --mysql-password=123456 \
        --mysql-table-engine=innodb \
        --rand-init=on  \
        --mysql-host=127.0.0.1 \
        --mysql-port=13336 \
        --mysql-db=benchmark \
        --time=300 \
        --max-requests=0 \
        --oltp_skip_trx=on \
        --oltp_auto_inc=off \
        --oltp_secondary=on \
        --threads=50 \
        run

数据库单表 3w 数据，每个 DELETE 事务命中 1000 条数据，并发 20 个事务：

mysql 性能如下，大约 70% 概率可以触发死锁：

  • transactions: 18324 (tps 60.97 per sec.)
  • queries: 329832 (qps 1097.50 per sec.)

mysql 性能如下，没有复现死锁：

  • transactions: 185025 (tps 616.36 per sec.)
  • queries: 3330450 (qps 11094.40 per sec.)

但性能极好的 mysql 在现实中可能没有这种条件

总结

到此这篇关于MYSQL事务死锁问题排查及解决方案的文章就介绍到这了,更多相关MYSQL事务死锁问题排查内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！

这篇关于MYSQL事务死锁问题排查及解决方案的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1153313。

相关文章

springboot循环依赖问题案例代码及解决办法

springboot循环依赖问题案例代码及解决办法

《springboot循环依赖问题案例代码及解决办法》在SpringBoot中,如果两个或多个Bean之间存在循环依赖（即BeanA依赖BeanB,而BeanB又依赖BeanA）,会导致Spring的... 目录1. 什么是循环依赖？2. 循环依赖的场景案例3. 解决循环依赖的常见方法方法 1：使用 @La
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
SpringKafka消息发布之KafkaTemplate与事务支持功能

SpringKafka消息发布之KafkaTemplate与事务支持功能

《SpringKafka消息发布之KafkaTemplate与事务支持功能》通过本文介绍的基本用法、序列化选项、事务支持、错误处理和性能优化技术,开发者可以构建高效可靠的Kafka消息发布系统,事务支... 目录引言一、KafkaTemplate基础二、消息序列化三、事务支持机制四、错误处理与重试五、性能优
阅读更多...
Linux samba共享慢的原因及解决方案

Linux samba共享慢的原因及解决方案

《Linuxsamba共享慢的原因及解决方案》：本文主要介绍Linuxsamba共享慢的原因及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录linux samba共享慢原因及解决问题表现原因解决办法总结Linandroidux samba共享慢原因及解决
阅读更多...
Spring事务中@Transactional注解不生效的原因分析与解决

Spring事务中@Transactional注解不生效的原因分析与解决

《Spring事务中@Transactional注解不生效的原因分析与解决》在Spring框架中,@Transactional注解是管理数据库事务的核心方式,本文将深入分析事务自调用的底层原理,解释为... 目录1. 引言2. 事务自调用问题重现2.1 示例代码2.2 问题现象3. 为什么事务自调用会失效3
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2