Java Spring Number of database connections per thread-CodePudding

I was wondering whether we have a limitation on number of connection each Java thread can open to the database. Because when I try to execute a transaction using Propagation.REQUIRES_NEW which I believe opens a new connection to the database, I get following error:

java.sql.SQLTransientConnectionException: HikariPool Connection is not available, request timed out after

Based on my search it seems that Hikari is not giving my thread a new connection. and when I try to manually close the previous connection I no longer get this error. How I close the current (previous) connection :

c = DataSourceUtils.getConnection(dataSource);
c.close();

So what is the workaround? and if there is a limitation for number of connections per thread can I change this setting ?

CodePudding user response：

if you are using spring boot add below property in the application.property to define pool size

spring.datasource.hikari.minimum-idle= 10 
spring.datasource.hikari.maximum-pool-size= 100

CodePudding user response：

How I close the current (previous) connection :
c = DataSourceUtils.getConnection(dataSource); 
c.close(); 

didn't get that point, if you are using spring-tx why do not use it's JdbcTemplate? Moreover, make sure that you are closing connection in finally block:

Connection c = null;
try {
  c = DataSourceUtils.getConnection(dataSource);
  ...
} finally {
  if (c != null) {
     c.close();
  }
}

or using try-with-resources:

try (Connection c = DataSourceUtils.getConnection(dataSource)) {
   ...
}

in regards to @Transactional(Propagation.REQUIRES_NEW) - consider refactoring you code.