Spring Boot搭配MariaDB驱动、AWS JDBC包装器及HikariCP：批量执行仅单语句生效
- 使用
PreparedStatement.executeBatch()执行批量插入/更新时，多条语句批量执行失效（无记录插入/更新或报错），但单条语句调用executeBatch()时执行正常 - 批量执行过程中出现Socket超时错误
环境信息
- Spring Boot版本：3.4.5
- MariaDB JDBC驱动版本：3.4.2
- AWS Advanced JDBC包装器版本：2.5.2
- Aurora MySQL版本：2.7.12
- HikariCP版本：5.1.0
- JDBC URL示例：
jdbc:aws-wrapper:mariadb://AuroraHost:AuroraPort/Schema?rewriteBatchedStatements=true&useSSL=false
代码示例
public Integer saveBulkDataInTableData(List<Schema> listOfCandidates) throws SQLException { Integer numberOfRecordsSaved = 0; int[] batchExecutionResult; if (!listOfCandidates.isEmpty()) { try(Connection conn = writeDataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO table01 (Id, isProcessed, lastModifiedDate) VALUES (?,?,?)")) { if (conn == null || conn.isClosed()) { LOGGER.error("Database connection is not available or is closed."); throw new SQLException("Database connection is not available or is closed."); } conn.setAutoCommit(false); int batchSize = 2000; for (int i = 0; i < listOfCandidates.size(); i++) { conn.setAutoCommit(false); preparedStatement.setString(1, listOfCandidates.get(i).getId()); preparedStatement.setString(2, "N"); preparedStatement.setString(3, listOfCandidates.get(i).getActivityDate()); preparedStatement.addBatch(); if ((i + 1) % batchSize == 0) { LOGGER.debug("Saving batch at record number {}", i + 1); try { preparedStatement.executeBatch(); preparedStatement.clearBatch(); conn.commit(); } catch (Exception exception) { LOGGER.error("Batch with record number {} failed", i + 1, exception); conn.rollback(); } } } // 执行批量中剩余的记录 try { batchExecutionResult = preparedStatement.executeBatch(); numberOfRecordsSaved = numberOfRecordsSaved + batchExecutionResult.length; conn.commit(); } catch (Exception exception) { LOGGER.error("Records remaining in the saveBulkDataInTableData batch failed", exception); conn.rollback(); } finally { conn.setAutoCommit(true); } } } return numberOfRecordsSaved; }
错误日志
主错误栈
WriterConnectionPool - Connection org.mariadb.jdbc.Connection@457512b marked as broken because of SQLSTATE(XXXXX), ErrorCode(-1)|com.zaxxer.hikari.pool.ProxyConnection.checkException|178||||java.sql.BatchUpdateException: (conn=6066945) Socket timout error at org.mariadb.jdbc.export.ExceptionFactory.createBatchUpdate(ExceptionFactory.java:221) at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:999) at org.mariadb.jdbc.Statement.executeInternalBatchPipeline(Statement.java:1625) at org.mariadb.jdbc.Statement.executeBatch(Statement.java:766) at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) at com.zaxxer.hikari.pool.HikariProxyStatement.executeBatch(HikariProxyStatement.java) at com.xxx.container.xxx.xxxx.xxxxxx.xxx.xxxxxxxxxxxxx.savetestData(xxxxxxxxxxxxx.java:91) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
根因错误栈
Caused by: java.sql.SQLNonTransientConnectionException: (conn=6066945) Socket timout error at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:300) at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:366) at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:1258) at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:1173) at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:1092) at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:932) ... 64 more Caused by: java.net.SocketTimeoutException: Read timed out at java.base/sun.nio.ch.NioSocketImpl.timedRead(NioSocketImpl.java:288) at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:314) at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:355) at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:808) at java.base/java.net.Socket$SocketInputStream.read(Socket.java:966) at java.base/java.io.BufferedInputStream.fill(BufferedInputStream.java:244) at java.base/java.io.BufferedInputStream.read1(BufferedInputStream.java:284) at java.base/java.io.BufferedInputStream.read(BufferedInputStream.java:343) at org.mariadb.jdbc.client.socket.impl.PacketReader.readReusablePacket(PacketReader.java:62) at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:164) at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:1234)
解决方案
1. 调整批量大小与超时参数
当前2000条的批量大小可能超出Aurora的单批次处理能力，导致超时：
- 降低批量大小至500-1000条，减少单批次数据量
- 在JDBC URL中增加Socket超时配置，例如
socketTimeout=60000（设置为60秒），避免因处理时间过长断开连接
2. 修复代码冗余
循环内重复调用conn.setAutoCommit(false);属于冗余操作，将其移至循环外，减少不必要的连接状态变更。
3. 验证组件兼容性
AWS JDBC包装器与MariaDB驱动的版本组合可能存在批量处理兼容性问题：
- 升级AWS Advanced JDBC包装器至最新稳定版（如2.6.x系列）
- 确认
rewriteBatchedStatements=true参数被AWS包装器正确传递至MariaDB驱动，该参数是驱动端优化批量插入的关键配置
4. 优化HikariCP连接池配置
确保连接池超时设置大于Socket超时，避免连接池提前回收连接：
spring.datasource.hikari.connection-timeout=300000 spring.datasource.hikari.socket-timeout=60000
5. 排查Aurora实例性能
Socket超时可能由Aurora资源瓶颈导致：
- 查看Aurora实例的CPU、内存使用率，确认是否存在资源不足情况
- 检查慢查询日志，优化批量插入语句（例如调整相关索引）
内容的提问来源于stack exchange，提问作者vishal gawde
