Tomcat JDBC Connection Pool and "java.sql.SQLException: Connection has already been closed"

Hibernate ORM
1

I need help troubleshooting a problem that has been recurring for years. It is about a web application, running on Tomcat 9. I am using Hibernate, Postgresql and Tomcat JDBC Connection Pool. The application is running in autocommit=false and CURRENT_SESSION_CONTEXT_CLASS=thread mode.

These are the settings of Tomcat data source (JDBC connection pool):

<Resource name="xxx" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          url="jdbc:postgresql://localhost:5432/db"
          username="xxx" password="xxx" maxActive="1000" maxIdle="500" minIdle="300"
          defaultAutoCommit="false" jmxEnabled="false"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport"

            removeAbandoned="true"
            removeAbandonedTimeout="30"
            logAbandoned="true"
            suspectTimeout="20"
            validationQuery="SELECT 1"
            testWhileIdle="true"
            timeBetweenEvictionRunsMillis="5000"
            minEvictableIdleTimeMillis="60000"
            testOnBorrow="true"
            validationInterval="10000"
            closeMethod="close"

/>

and Hibernate sessionFactory:

ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(...)
.applySetting( AvailableSettings.TRANSACTION_COORDINATOR_STRATEGY, "jdbc" )
.applySetting( AvailableSettings.CURRENT_SESSION_CONTEXT_CLASS, "thread" )
.applySetting( AvailableSettings.USE_NEW_ID_GENERATOR_MAPPINGS, false )
.applySetting( AvailableSettings.DIALECT, "org.hibernate.dialect.PostgreSQL10Dialect" )
.applySetting( AvailableSettings.DEFAULT_SCHEMA, "public" )
.applySetting( AvailableSettings.DRIVER, "org.postgresql.Driver" )
.applySetting( "hibernate.temp.use_jdbc_metadata_defaults", false )
.build();
Metadata metadata = new MetadataSources( serviceRegistry )
			.addAnnotatedClass( …)
.getMetadataBuilder().build();
sessionFactory = 	metadata.getSessionFactoryBuilder().build();

The pattern of executing commands to the database looks like this:

Session s = sessionFactory.getCurrentSession();
s.beginTransaction();
try {
  …
  …
  s.getTransaction().commit();
} catch (Exception x) {
  x.printStackTrace();
  s.getTransaction().rollback();
   ....
   // log and handle the exception
}

Once in a while it happens that Tomcat JDBC Pool closes or removes the connection. For example, because of an I/O error or because of a bug in the code (the transaction is not completed by either commit() or rollback() and connection is abandoned after 30s). These are obviously code bugs that need to be corrected. The problem is that Hibernate reacts badly to this, and in such a situation dozens of exceptions suddenly start being thrown:


org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [dispatcher] in context with path
 [] threw exception [Request processing failed; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: ] with root cause
        java.sql.SQLException: Connection has already been closed.
                at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:121)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
                at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
                at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:39)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
                at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
                at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
                at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
                at jdk.proxy2/jdk.proxy2.$Proxy92.setAutoCommit(Unknown Source)
                at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:72)
                at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:285)
                at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:246)
                at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:83)
                at org.hibernate.internal.AbstractSharedSessionContract.beginTransaction(AbstractSharedSessionContract.java:503)
                at jdk.internal.reflect.GeneratedMethodAccessor191.invoke(Unknown Source)
                at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.base/java.lang.reflect.Method.invoke(Method.java:568)
                at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:351)
                at jdk.proxy3/jdk.proxy3.$Proxy91.beginTransaction(Unknown Source)

How to force Hibernate to simply take a new open connection from the Tomcat connection pool in such a situation?

I see this code in org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor:

@Override
	public void begin() {
		try {
			if ( !doConnectionsFromProviderHaveAutoCommitDisabled() ) {
				log.trace( "Preparing to begin transaction via JDBC Connection.setAutoCommit(false)" );
				getConnectionForTransactionManagement().setAutoCommit( false );
				log.trace( "Transaction begun via JDBC Connection.setAutoCommit(false)" );
			}
			status = TransactionStatus.ACTIVE;
		}
		catch( SQLException e ) {
			throw new TransactionException( "JDBC begin transaction failed: ", e );
		}
	}

and I would like to force Hibernate here to obtain a new connection from the pool, in case e is “java.sql.SQLException: Connection has already been closed”. And report the warning that connection was not valid and Hibernate had to take another one.

Any suggestions on how to do this?

2

That’s simply not possible. If a transaction already started and after that the connection is closed, Hibernate can’t just replace the connection without violating consistency.

This sort of thing is usually handled on the connection pool level. The connection pool datasource usually runs a special liveness check (most of the time just a simple select 1 from dual) to check if the connection still works before handing out a connection.

3

Thank you @beikov. Please, take a look at the stack trace.
java.sql.SQLException: Connection has already been closed is raised on Session.beginTranscation
This happens very rarely, but if it happens, it is a disaster.

This can happen when:

  1. a connection is killed by an I/O event in the operating system
  2. there is a bug or leak in the code and some transaction is not commited, and Tomcat pool manager marks it as “abandoned” and removes the connection from the pool.

If I understand correctly, each Tomcat’s thread stores its own Hibernate session (in case of CURRENT_SESSION_CONTEXT_CLASS=“thread” configuration), and this session “holds” the connection to the database as long as the thread is alive. This means that it can “hold” an externally closed connection, and that’s where the SQLException Connection has already been closed problem arises.

I’m looking for ideas on how to remedy this.