Prevent keeping unused DB connection

Question

Problem description:

Lets have a service method which is called from controller:

class PaymentService {
    static transactional = false

    public void pay(long id) {
        Member member = Member.get(id)
        //long running task executing HTTP request
        requestPayment(member)
    }
}

The problem is if 8 users hit the same service in the same time and the time to execute the requestPayment(member) method is 30 seconds, the whole application gets stucked for 30 seconds.

The problem is even bigger than it seems, because if the HTTP request is performing well, nobody realizes any trouble. The serious problem is that availability of our web service depends on the availability of our external partner/component (in our use-case payment gateway). So when your partner starts to have performance issues, you will have them as well and even worse it will affect all parts of your app.

Evaluation:

The cause of problem is that Member.get(id) reserves a DB connection from pool and it keeps it for further use, despite requestPayment(member) method never needs to access DB. When next (9-th) request hits any other part of the application which requires DB connection (transactional service, DB select, ...) it keeps waiting (or timeouts if maxWait is set to lower duration) until the pool has an available connection, which can last even 30 seconds in our use case.

The stacktrace for the waiting thread is:

at java.lang.Object.wait(Object.java:-1)
      at java.lang.Object.wait(Object.java:485)
      at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1115)
      at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
      at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
      at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

Or for timeout:

JDBC begin failed
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1167)
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
        ... 7 more

Obviously the same issue happens with transactional service, however it makes much more sense since the connection is reserved for the transaction.

As a temporary solution its possible to increase the pool size with maxActive property on datasource, however it doesn't solve the real problem of holding an unused connection.

As a permanent solution its possible to enclose all DB operations to transactional behavior (withTransaction{..}, @Transactional), which returns the connection back to pool after commit (or to my surprise also withNewSession{..} works). But we need to be sure that the whole call chain from controller up to the requestPayment(member) method doesn't leak the connection.

I'd like to be able to throw an exception in the requestPayment(member) method if the connection is "leaked" (similar to Propagation.NEVER transactional behavior), so I can reveal the issue early during test phase.

Answer 1

After digging in the source code I've found the solution:

class PaymentService {
    static transactional = false
    def sessionFactory

    public void pay(long id) {
        Member member = Member.get(id)
        sessionFactory.currentSession.disconnect()
        //long running task executing HTTP request
        requestPayment(member)
    }
}

The above statement releases the connection back to pool.

If executed from transactional context, an exception is thrown (org.hibernate.HibernateException connnection proxy not usable after transaction completion), since we can't release such a connection (which is exactly what I needed).

Javadoc:

Disconnect the Session from the current JDBC connection. If the connection was obtained by Hibernate close it and return it to the connection pool; otherwise, return it to the application.

This is used by applications which supply JDBC connections to Hibernate and which require long-sessions (or long-conversations)