SQL Server 连接已关闭。

Question

SQL Server 连接已关闭。

javasql-serverspringhibernatesql-server-2012

7

我有一个连接到SQL Server 2012数据库的Java Spring应用程序。这是我正在使用的属性和数据源：

-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

并且由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"
        />

我有一个进程发送了很多JMS消息。但是在不同的时间，我的JMS监听器会收到相同的错误消息，指出连接已关闭。这是为什么导致连接关闭的原因呢？

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

编辑 25 / 02 / 2016：这是我实现的内容，但仍然不起作用：

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);
        }
    }

}

- Gaetan56

3个回答

0

如果您不使用Tomcat JDBC池，则可以将其从项目中排除，这样就可以解决重新连接的问题。

对于Gradle：

configurations.all {
  exclude group: 'org.apache.tomcat', module: 'tomcat-jdbc' 
}

- Jan Moravec

0

我曾经遇到过同样的问题，并找到了一个很好的解释和解决方案在这里。

简而言之，你的数据库或应用服务器（例如JBoss）连接池管理器由于默认设置或显式配置而使一些连接失效，同时，Hibernate的内部数据库连接池管理器将这些连接视为有效连接。因此，当你的应用程序尝试重复使用它们时，你会得到连接关闭错误，直到你重新启动应用程序服务器才能清除该错误。

要解决这个问题，只需将Hibernate连接池管理委托给一个名为hibernate-c3p0的辅助库，并按照示例配置进行操作即可。

<property name="hibernate.c3p0.min_size" value="5" />
<property name="hibernate.c3p0.max_size" value="20" />
<property name="hibernate.c3p0.timeout" value="300" />

这些属性在上面的链接中有很好的解释，也可以在这里找到。

- mikaelovi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- seneque · Accepted Answer

我曾经遇到过类似的问题。在出现IOException的情况下，sqlserver jdbc驱动程序将连接标记为已关闭，但这并没有被池检测到。因此，连接会被返回到池中，但是无法使用。解决这个问题的方法是编写一个新的JDBC拦截器来使用tomcatjdbc。当调用close时，拦截器必须调用底层连接上的"isClosed"方法。如果底层连接已关闭，则拦截器必须将PooledConnection标记为已丢弃。然后配置您的池以使用此拦截器。