Getting HikariPool-2 Connection is not available, request timedout

Question

We have a spring boot application with spring JPA and Sybase ASE database. We have used Hikari as the connection pool. We have 1 stored procedure written which gets called every 4 minute.

Hikari configuration is:

datasource:
        type: com.zaxxer.hikari.HikariDataSource
        data-source-class-name: com.sybase.jdbc4.jdbc.SybDataSource
        driver-class-name: com.sybase.jdbc4.jdbc.SybDriver
        url: xxx-yyy-zzz
        username: xxx-yyy-zzz
        password: xxx-yyy-zzz
        hikari:
            maximum-pool-size: 10
            minimum-idle: 1

The problem is, I am getting below error after every 10 calls to the stored procedure as I have configured pool size 10.

> Caused by: java.sql.SQLTransientConnectionException: HikariPool-2 -
> Connection is not available, request timed out after 30007ms.     at
> com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669)
> 
> org.springframework.dao.DataAccessResourceFailureException: Unable to
> acquire JDBC Connection; nested exception is
> org.hibernate.exception.JDBCConnectionException: Unable to acquire
> JDBC Connection

Code is written as follows:

@Autowired
    @Lazy
    Smsxyz smsxyzImpl;

    @PostConstruct
    @Scheduled(fixedRate = 240000)
    public void completeStatus() {
        try {
            smsxyzImpl.checkStatus(Constants.STATUS_PER_SCAN, Constants.STATUS_EXPIRY_DURATION, -1L);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


@Autowired
    private EntityManager entityManager;
    
    @Override
    //@Transactional
    public Integer checkStatus(Integer count, Integer expiry, Long userId) {
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("status.check");
        query.setParameter("@status_count", count);
        query.setParameter("@status_expiry", expiry);
        query.setParameter("@user_id", userId);
        Integer outputValue= (Integer) query.getOutputParameterValue("@return_status");
        return outputValue;
    }

If I remove @Transactional then it runs for 10 times (as hikari pool size is 10) and after that it throws an exception as mentioned above
If I keep @Transactional then it runs fine with no exception but it does not update the database.
If I run the stored procedure manually directly from the database, then it works perfectly fine that means no issue in the Stored procedure

My suspect is, it is not releasing the connection. So I closed the connection by entitymanager.close() but it didn't work I spent many days for this issue but no luck. Could anyone let me know what I am doing wrong here?

Do you actually also execute the stored procedure? Because I can't find a line in your code snippet? — Michael Altenburger, Aug 06 '20 at 12:14
For point 2, you may take a look on [this question](https://stackoverflow.com/questions/5443876/spring3-s-transactional-scheduled-not-committed-to-db) — samabcde, Aug 06 '20 at 15:22
@MichaelAltenburger Yes following line executes the procedure StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("status.check"); — pan1490, Aug 07 '20 at 06:44
@pan1490 With this line you just create the query, you also need to execute it using `query.execute()` - see https://www.logicbig.com/tutorials/java-ee-tutorial/jpa/named-stored-procedure.html for an example — Michael Altenburger, Aug 07 '20 at 06:47
@MichaelAltenburger But it is executing as mentioned in point 1. The code is working only thing is hikari is not closing the connection / not reusing the pool — pan1490, Aug 07 '20 at 06:50

Getting HikariPool-2 Connection is not available, request timedout

0 Answers0