Sql Server The connection is closed

Question

I have a java spring app connected to a database under SQL Server 2012. THis is the properties and the datasource i am using :

-datasource.initialSize=34

-datasource.minIdle=89

-datasource.maxIdle=233

-datasource.maxActive=377

-datasource.maxWait=50000

-datasource.abandonWhenPercentageFull=50

-datasource.jmxEnabled=true

-datasource.removeAbandoned=true

-datasource.removeAbandonedTimeout=120

-datasource.logAbandoned=true

-datasource.testOnBorrow=true

-datasource.timeBetweenEvictionRunsMillis=60000

-datasource.minEvictableIdleTimeMillis=80000

-datasource.validationInterval=40000

and the datasource managed by spring :

 <bean id="myDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close" 

            p:driverClassName="${datasource.driverClass}"
            p:url="${datasource.url}"
            p:username="${datasource.user}"
            p:password="${datasource.pass}" 
            p:maxWait="${datasource.maxWait}" 
            p:minIdle="${datasource.minIdle}"
            p:maxIdle="${datasource.maxIdle}"
            p:maxActive="${datasource.maxActive}"

            p:jmxEnabled="${datasource.jmxEnabled}"
            p:removeAbandoned="${datasource.removeAbandoned}"
            p:removeAbandonedTimeout="${datasource.removeAbandonedTimeout}"
            p:logAbandoned="${datasource.logAbandoned}"
            p:testOnBorrow="${datasource.testOnBorrow}"
                 p:timeBetweenEvictionRunsMillis="${datasource.timeBetweenEvictionRunsMillis}"
            p:minEvictableIdleTimeMillis="${datasource.minEvictableIdleTimeMillis}"
            p:validationInterval="${datasource.validationInterval}"
            p:abandonWhenPercentageFull="${datasource.abandonWhenPercentageFull}"
            p:validationQuery="SELECT 1" 
            p:jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.ConnectionState"
        />

I have got a process which send a lot JMS Messages. I get the same error message at various time saying that the The Connection is Closed in my JMS Listenner. Any idea why it closes the connection ?

org.springframework.transaction.TransactionSystemException: Could not commit Hibernate transaction; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
    at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:585)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at com.spectrags.fundhive.interceptor.PerfInterceptor.invoke(PerfInterceptor.java:34)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    at com.spectrags.fundhive.business.api.messaging.WorkflowJmsListener$$EnhancerBySpringCGLIB$$704b2c20.onMessage(<generated>)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:746)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:684)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:651)
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:315)
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:253)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1150)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1142)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:1039)
    at java.lang.Thread.run(Unknown Source) Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:86)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
    at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:581)
    ... 21 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.commit(SQLServerConnection.java:1936)
    at sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:152)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:70)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:70)
    at org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer.invoke(ResetAbandonedTimer.java:62)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:40)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
    at com.sun.proxy.$Proxy21.commit(Unknown Source)
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:80)
    ... 24 more

EDIT 25 / 02 / 2016 : this is what i've implemented but it still doesn't work :

public class ConnectionJDBCInterceptor extends JdbcInterceptor {

    protected static Logger log = Logger.getLogger(ConnectionJDBCInterceptor.class.getName());

    @Override
    public void reset(final ConnectionPool pool, final PooledConnection pooledConnection) {

        try {
            if (pooledConnection.getConnection().isClosed()) {
                ConnectionJDBCInterceptor.log.info("Closed connection in the pool is being reconnected");
                pooledConnection.reconnect();
            }
        } catch (final SQLException e) {
            ConnectionJDBCInterceptor.log.error(e, "Error in JDBC Interceptor", e);
        }
    }

}

Answer 1

I had similar issues. In case of IOException, the sqlserver jdbc driver marks the connection as closed, but this is not detected by the pool. So the connection is returned in the pool, while unusable. A work around to this is to write a new JDBCInterceptor for tomcatjdbc. The interceptor must when close is invoked, call "isClosed" on the underlying connection. If the underlying connection is closed, the interceptor must marked the PooledConnection as discarded. Then configure your pool to use this interceptor.