springboot-How to solve java.sql.SQLTransientConnectionException: HikariPool Connection is not available, request timed out after 3600000ms

1. Introduction

When we use HikariCP connection pool in SpringBoot applications, sometimes, we would encounter this error:

INFO   | jvm 1    | 2019/07/15 09:10:00 | java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 3600000ms.
INFO   | jvm 1    | 2019/07/15 09:10:00 |       at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548) ~[HikariCP-2.5.1.jar:na]
INFO   | jvm 1    | 2019/07/15 09:10:00 |       at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186) ~[HikariCP-2.5.1.jar:na]
INFO   | jvm 1    | 2019/07/15 09:10:00 |       at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145) ~[HikariCP-2.5.1.jar:na]
INFO   | jvm 1    | 2019/07/15 09:10:00 |       at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99) ~[HikariCP-2.5.1.jar:na]

2. Environments

• SpringBoot 1.x and 2.x

3. The code

The SpringBoot application.properties

spring.datasource.jdbcUrl=jdbc:mysql://10.1.1.8/test
spring.datasource.username=bswen
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.maximumPoolSize=20
spring.datasource.connectionTimeout=3600000
spring.datasource.idleTimeout=600000
spring.datasource.minimumIdle=5
spring.datasource.test-on-borrow=true
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=3600000

Here we use a HikariCP connection pool with connection size from 5 to 20.

The SpringBoot command:

@Component
public class MyCityCommand2 implements CommandLineRunner {
    private static Logger logger = LoggerFactory.getLogger(MyCityCommand2.class);
    @Autowired
    @Qualifier("cityDao")
    private CityDao cityDao;

    @Override
    public void run(String... strings) throws Exception {
        while(true) {
            Thread.sleep(2000);

            JdbcTemplate jdbcTemplate = cityDao.getJdbcTemplateObject();//get a jdbcTemplate
            try {
            	//get metadata from the connection
                String dbName = jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName();
                logger.info("dbName:"+dbName);
            }catch (Exception ex) {
                logger.error("",ex);
            }finally {
            	//close the connection
                jdbcTemplate.getDataSource().getConnection().close();
            }
        }
    }
}

Pay attention to the line jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName(), and the line jdbcTemplate.getDataSource().getConnection().close().

First, we retrieve a connection from jdbcTemplate’s datasource , then we close it by call jdbcTemplate.getDataSource().getConnection().close().

When we run the code, we get this error:

INFO   | jvm 1    | 2019/07/15 09:10:00 | java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 3600000ms.

4. The solution

We can solve this problem by these ways:

Get and close specific connection In the above code sample, we can see that we get a connection from the datasource and then close a connection from the datasource, they may be different connection! e.g. We get a connection and close another one.

We can change the code as follows:

    @Override
    public void run(String... strings) throws Exception {
        while(true) {
            Thread.sleep(2000);

            JdbcTemplate jdbcTemplate = cityDao.getJdbcTemplateObject();
            try (Connection conn = jdbcTemplate.getDataSource().getConnection()) {
                String dbName = conn.getMetaData().getDatabaseProductName();
                logger.info("dbName:"+dbName);
            }
        }
    }

5. Summary

When we use a connection pool in SpringBoot Applications, pay attention to the connection leak problem, if it occurs, try to check if you have close the right connection when you use it.