【异常】Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
2023-09-14 08:57:35 时间
一、异常出现的场景
一次线上订单历史数据字段刷新操作,3张表100多万数据。由于同步更新太慢大概20分钟以上,所以采用异不的方式。代码如下:
private void batchUpdate(List<SaasOrderRecordDataForUpdate> saasOrderRecordDataForUpdateList, List<SaasServiceOrderInfoDataForUpdate> saasServiceOrderInfoDataForUpdateList, List<OrderGoodsDataForUpdate> orderGoodsDataForUpdateList, List<OrderAdditionCostInfoDataForUpdate> orderAdditionCostInfoDataForUpdateList) { List<Future> asyncResultList = new ArrayList<>(); if (CollectionUtils.isNotEmpty(saasOrderRecordDataForUpdateList)) { int size = saasOrderRecordDataForUpdateList.size(); int count = size / 5000; for (int i = 0; i <= count; i++) { List<SaasOrderRecordDataForUpdate> subList = null; if (i == count) { if (5000 * i < size) { subList = saasOrderRecordDataForUpdateList.subList(5000 * i, size); } } else { subList = saasOrderRecordDataForUpdateList.subList(5000 * i, 5000 * (i + 1)); } if (CollectionUtils.isNotEmpty(subList)) { Future future = dataRefreshJobServiceForAsync.batchUpdateSaasOrderRecordDataForUpdate(subList); asyncResultList.add(future); } } } XxlJobLogger.log("批量更新订单数:{}", saasOrderRecordDataForUpdateList.size()); if (CollectionUtils.isNotEmpty(saasServiceOrderInfoDataForUpdateList)) { int size = saasServiceOrderInfoDataForUpdateList.size(); int count = size / 5000; for (int i = 0; i <= count; i++) { List<SaasServiceOrderInfoDataForUpdate> subList = null; if (i == count) { if (5000 * i < size) { subList = saasServiceOrderInfoDataForUpdateList.subList(5000 * i, size); } } else { subList = saasServiceOrderInfoDataForUpdateList.subList(5000 * i, 5000 * (i + 1)); } if (CollectionUtils.isNotEmpty(subList)) { Future future = dataRefreshJobServiceForAsync.batchUpdateSaasServiceOrderInfoDataForUpdate(subList); asyncResultList.add(future); } } } XxlJobLogger.log("批量更新订单服务数:{}", saasServiceOrderInfoDataForUpdateList.size()); if (CollectionUtils.isNotEmpty(orderGoodsDataForUpdateList)) { int size = orderGoodsDataForUpdateList.size(); int count = size / 5000; for (int i = 0; i <= count; i++) { List<OrderGoodsDataForUpdate> subList = null; if (i == count) { if (5000 * i < size) { subList = orderGoodsDataForUpdateList.subList(5000 * i, size); } } else { subList = orderGoodsDataForUpdateList.subList(5000 * i, 5000 * (i + 1)); } if (CollectionUtils.isNotEmpty(subList)) { Future future = dataRefreshJobServiceForAsync.batchUpdateOrderGoodsDataForUpdate(subList); asyncResultList.add(future); } } } XxlJobLogger.log("批量更新订单商品数:{}", orderGoodsDataForUpdateList.size()); if (CollectionUtils.isNotEmpty(orderAdditionCostInfoDataForUpdateList)) { int size = orderAdditionCostInfoDataForUpdateList.size(); int count = size / 5000; for (int i = 0; i <= count; i++) { List<OrderAdditionCostInfoDataForUpdate> subList = null; if (i == count) { if (5000 * i < size) { subList = orderAdditionCostInfoDataForUpdateList.subList(5000 * i, size); } } else { subList = orderAdditionCostInfoDataForUpdateList.subList(5000 * i, 5000 * (i + 1)); } if (CollectionUtils.isNotEmpty(subList)) { Future future = dataRefreshJobServiceForAsync.batchUpdateOrderAdditionCostInfoDataForUpdate(subList); asyncResultList.add(future); } } } XxlJobLogger.log("批量更新订单附加费数:{}", orderAdditionCostInfoDataForUpdateList.size()); if (CollectionUtils.isNotEmpty(asyncResultList)) { for (Future asyncResult : asyncResultList) { try { asyncResult.get(); } catch (Exception e) { e.printStackTrace(); } } } }
本地库刷新没问题,但是到了线上库就出现如下异常:
### Error updating database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms. ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms. at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200) at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ... 24 more Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms. at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82) at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68) at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at sun.reflect.GeneratedMethodAccessor381.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy521.update(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ... 28 more Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms. at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:183) at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:148) at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$f68c05a.getConnection(<generated>) at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
二、解决办法
通过异常可以发现是由于获取不到数据库连接导致,猜测是连接数不够的问题,所以修改HikariPool连接池配置,就解决了
修改前:
spring: datasource: hikari: connection-test-query: SELECT 1 FROM DUAL connection-timeout: 30000 maximum-pool-size: 20 max-lifetime: 1800000 minimum-idle: 5 connection-init-sql: SET NAMES utf8mb4
修改后:
spring: datasource: hikari: connection-test-query: SELECT 1 FROM DUAL connection-timeout: 600000 maximum-pool-size: 500 max-lifetime: 1800000 minimum-idle: 20 validation-timeout: 3000 idle-timeout: 60000 connection-init-sql: SET NAMES utf8mb4
相关文章
- java实现加密电话号码,有具体的加密流程注释
- java错误:The superclass "javax.servlet.http.HttpServlet" was not found on the Java Bu
- Java实现 LeetCode 587 安装栅栏(图算法转换成数学问题)
- java算法集训代码填空题练习1
- java.sql.SQLException: null, message from server: “Host ‘xxx’ is not allowed to connect
- 可能是国内第一篇全面解读 Java 现状及趋势的文章
- 【JAVA】 02-Java对象细节
- 【JAVA】 01-Java基础知识
- Java超类-java.lang.object
- Sql Server中sql语句自动换行
- Atitit if else 选择决策流程ast对比 sql java 表达式类型 binaryExpression hase left and rit expr 目录 1.1. Sql1
- Atitit 读取数据库的api orm SQL Builder sql对比 目录 1.1. 提高生产效率的 ORM 和 SQL Builder1 1.2. SQL Builder 在 SQL
- java语言学习002_面向对象编程思想
- 【异常】Error querying database. Cause: java.sql.SQLException: No value specified for parameter 1
- Simple Logging Facade for Java (SLF4J)作用(java日志框架)
- 【华为OD机试 2023】 任务总执行时长(C++ Java JavaScript Python)
- Java学习路线-41:数据库及SQL/MySQL基础
- 解决idea出现的java.lang.OutOfMemoryError: Java heap space的问题
- dubbo(提供者、消费者)基于java的实现
- sql 精读(二) 标准 SQL 中的编号函数
- Java 异常处理
- 【JAVA面试必会】JMM高并发详解(java内存模型、JMM三大特征、volatile关键字 )