java - 为什么 HikariCP 在关闭前一个数据库后不建立新的数据库连接？

Question

为了更好地理解 HikariCP 的工作原理，我决定制作一个 Spring Boot 项目来执行一些数据库数据操作测试，同时减少数据库中的 wait_timeout。以下是 application.properties 文件中的 HikariCP 配置:

spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.validationTimeout=5000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.maximumPoolSize=20
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG 
logging.level.com.zaxxer.hikari=TRACE

下面是 MySQL DB 上的一些值:

SHOW GLOBAL VARIABLES LIKE "%wait%";
+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout                          | 50       |
| innodb_log_wait_for_flush_spin_hwm                | 400      |
| innodb_spin_wait_delay                            | 6        |
| innodb_spin_wait_pause_multiplier                 | 50       |
| lock_wait_timeout                                 | 31536000 |
| mysqlx_wait_timeout                               | 28800    |
| performance_schema_events_waits_history_long_size | 10000    |
| performance_schema_events_waits_history_size      | 10       |
| wait_timeout                                      | 5        |
+---------------------------------------------------+----------+

SHOW VARIABLES LIKE "%timeout%";
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+

该项目有一个名为 Test 的实体类，它包含一个主键“id”和一个属性“value”:

@Entity
public class Test {
    
    @Id
    @Column(length = 50)
    private String id;
    
    private String value;
    
}

我们还有一个扩展 JpaRepository 的 TestRepository 和一个使用 JDBC 模板保存实体的 TestDAO:

@Repository
public interface TestRepository extends JpaRepository<Test,String>{
}

@Repository
public class TestDAO {
   
    @Value("${spring.jpa.properties.hibernate.jdbc.batch_size:10000}") // default = 1000
    private Integer batchSize;
    
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    TestRepository testRepository;
    
    public void saveAllDAO(List<Test> entities) {
        
        jdbcTemplate.batchUpdate(
                "INSERT INTO test "
                + "(id, value)"
                + "VALUES (?,?)",
                entities,
                batchSize,
                new ParameterizedPreparedStatementSetter<Test>() {
                    @Override
                    public void setValues(PreparedStatement ps, Test testEntity) throws SQLException {
                        ps.setObject(1, testEntity.getId(), java.sql.Types.VARCHAR);
                        ps.setObject(2, testEntity.getValue(), java.sql.Types.VARCHAR);
                    }
                });
        
    }
    
}

为了执行测试，我创建了一个包含四种方法的 TestService 类，这些方法在 MySQL 本地数据库中执行一些 SELECT、INSERT 和 UPDATE。

方法 1 -> 通过 Repository 类选择所有 -> 等待 20 秒 -> 通过 Repository 类更新数据库上的实体

方法 2 -> 通过 Repository 类选择所有 -> 等待 20 秒 -> 通过使用 JDBC 模板的 DAO 类将新实体保存在数据库中

方法 3 -> 通过 Repository 类 SELECT ALL -> 等待 20 秒 -> 再次通过 Repository 类 SELECT ALL

方法 4 -> 通过 Repository 类更新数据库中的实体 -> 等待 20 秒 -> 再次通过 Repository 类 SELECT ALL

@Service
public class TestService {
  
    @Autowired
    TestRepository testRepository;
    @Autowired
    TestDAO testDAO;
    
    public void method1() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL");
        
        Thread.sleep(20000);
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setValue("ValueRep" + Integer.toString(i));
        }
        
        testRepository.saveAll(entities);
        System.out.println("SAVE REP");
        
    }
    
    public void method2() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL");
        
        Thread.sleep(20000);
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setId("DAO" + Integer.toString(i));
            entity.setValue("ValueDAO" + Integer.toString(i));
        }
        
        testDAO.saveAllDAO(entities);
        System.out.println("SAVE DAO");
        
    }
    
    public void method3() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        System.out.println("FINDALL 1");
        
        Thread.sleep(20000);
        
        List<Test> entities2 = testRepository.findAll();
        System.out.println("FINDALL 2");
        
    }

    public void method4() throws InterruptedException {
        
        List<Test> entities = testRepository.findAll();
        
        int i = 0;
        for (Test entity : entities) {
            i++;
            entity.setValue("METHOD4ValueRep" + Integer.toString(i));
        }
        
        testRepository.saveAll(entities);
        System.out.println("SAVE REP METHOD4");
        
        Thread.sleep(20000);
        
        List<Test> entities2 = testRepository.findAll();
        System.out.println("FINDALL 2");
        
    }
    
}

在测试期间，我将数据库中的全局变量 wait_timeout 更改为 5 秒，以查看 HikariCP 在超时后必须建立新连接的行为。

问题是:当我调用这些方法中的任何一个时，第一个操作按预期工作，但是当代码尝试在等待 20 秒后执行第二个操作时，会生成 CommunicationsException。

2022-05-20 13:30:21.049  WARN 876 --- [nio-8080-exec-3] com.zaxxer.hikari.pool.ProxyConnection   : HikariPool-1 - Connection com.mysql.cj.jdbc.ConnectionImpl@3313efdd marked as broken because of SQLSTATE(08S01), ErrorCode(0)

com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 20.015 milliseconds ago. The last packet sent successfully to the server was 20.015 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2155) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:2139) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:423) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.setReadOnly(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:189) ~[spring-jdbc-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:153) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:421) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174) ~[spring-data-jpa-2.7.0.jar:2.7.0]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.20.jar:5.3.20]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.20.jar:5.3.20]
    at jdk.proxy2/jdk.proxy2.$Proxy109.findAll(Unknown Source) ~[na:na]
    at com.example.test.TestService.method3(TestService.java:59) ~[classes/:na]
    at com.example.test.TestController.method3(TestController.java:27) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) ~[tomcat-embed-core-9.0.63.jar:4.0.FR]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.20.jar:5.3.20]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.63.jar:4.0.FR]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) ~[spring-boot-actuator-2.7.0.jar:2.7.0]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.20.jar:5.3.20]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.20.jar:5.3.20]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.63.jar:9.0.63]
    at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 20.015 milliseconds ago. The last packet sent successfully to the server was 20.015 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at jdk.internal.reflect.GeneratedConstructorAccessor41.newInstance(Unknown Source) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) ~[na:na]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:581) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:761) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:700) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1051) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:997) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:663) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:2148) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    ... 76 common frames omitted
Caused by: java.net.SocketException: An established connection was aborted by the software in your host machine
    at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:325) ~[na:na]
    at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:350) ~[na:na]
    at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:803) ~[na:na]
    at java.base/java.net.Socket$SocketInputStream.read(Socket.java:966) ~[na:na]
    at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at java.base/java.io.FilterInputStream.read(FilterInputStream.java:132) ~[na:na]
    at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:575) ~[mysql-connector-java-8.0.29.jar:8.0.29]
    ... 82 common frames omitted

2022-05-20 13:30:21.056 DEBUG 876 --- [nnection closer] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@3313efdd: (connection is broken)
2022-05-20 13:30:21.059 ERROR 876 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: ] with root cause

java.sql.SQLException: Connection is closed
    at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515) ~[HikariCP-4.0.3.jar:na]
    at jdk.proxy3/jdk.proxy3.$Proxy92.setAutoCommit(Unknown Source) ~[na:na]
    at com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:414) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.setAutoCommit(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
    at
Sorry, log too long

HikariCP 不应该在检查旧连接不再打开后尝试建立新连接吗？为什么这没有发生？

注意:代码在 wait_timeout 为 28800 的情况下正常工作，就像以前一样，我只是想了解为什么 HikariCP 在这个测试用例中没有尝试建立与数据库的新连接，同时减少了 wait_timeout。

完整代码:https://github.com/MarcosL20/HikariCPTest/tree/main

Answer 1

我在上面评论中的直觉是正确的。 Spring 在单个事务 中执行每个方法。因此，无论在方法中有多少个单独的查询，都将使用单个连接。 JpaTransactionManager 确保每次调用都返回相同的连接。整个方法将作为一个单元(单事务)失败或成功。