Unable to commit against JDBC Connection; This connection has been closed.

[Swierkowski]

I made an upgrade of spring-batch from 5.2.2 to 5.2.3. After that, all my spring-batch related tests, which use Zonky, started to fail.

The exception is:

Hibernate transaction: Unable to commit against JDBC Connection; This connection has been closed.
org.springframework.dao.DataAccessResourceFailureException: Hibernate transaction: Unable to commit against JDBC Connection; This connection has been closed.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:132)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:269)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:567)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:795)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:758)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152)
at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
at com.example.zonky_demo.ZonkyDemoApplicationTest.contextLoads(ZonkyDemoApplicationTest.java:31)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:1012)
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:998)
at io.zonky.test.db.provider.support.BlockingDatabaseWrapper$BlockingConnectionWrapper.commit(BlockingDatabaseWrapper.java:165)
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:87)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:268)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:563)
... 8 more

The change which caused this error visible is here:
spring-projects/spring-batch@0ecaafc#diff-200c64590f43379663ed86d38ee57056c432313a1cf3674d8e859d860aa34e2eR181

Now, sping is using try with resources which is closing the stream.

A test reproducing this error:

package com.example.zonky_demo;

import io.zonky.test.db.AutoConfigureEmbeddedDatabase;
import org.junit.jupiter.api.Test;
import org.springframework.batch.core.JobInstance;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.support.TransactionTemplate;

import java.sql.ResultSet;
import java.util.stream.Stream;

import static io.zonky.test.db.AutoConfigureEmbeddedDatabase.DatabaseProvider.ZONKY;
import static io.zonky.test.db.AutoConfigureEmbeddedDatabase.RefreshMode.AFTER_EACH_TEST_METHOD;

@SpringBootTest
@AutoConfigureEmbeddedDatabase(provider = ZONKY, refresh = AFTER_EACH_TEST_METHOD)
class ZonkyDemoApplicationTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private TransactionTemplate transactionTemplate;

    @Test
    void contextLoads() {
        var jobName = "jobName";
        var jobKey = "jobKey";

        transactionTemplate.executeWithoutResult(transactionStatus -> {
            try (Stream<JobInstance> stream = jdbcTemplate.queryForStream(
                    """
                            SELECT JOB_INSTANCE_ID, JOB_NAME
                            FROM BATCH_JOB_INSTANCE
                            WHERE JOB_NAME = ?  AND JOB_KEY = ?
                            """,
                    (ResultSet rs, int rowNum) -> new JobInstance(rs.getLong(1), rs.getString(2)),
                    jobName,
                    jobKey)) {
            }
        });
    }

}

This error occurs only in tests. The application itself is running correctly as before.

My understanding is that in production code we use Hikari database connection pool and it is why it is working.

Zonky uses BlockingConnectionWrapper which does not proxy PreparedStatement. Hikari does proxy PreparedStatement. In JdbcTempalte#queryForStream the database connection is taken from PreparedStatement and for Zonky this is the target connection (not BlockingConnectionWrapper). Finally, when DataSourceUtils#releaseConnection is called it closes the target connection because the equality condition is not met:

	public static void doReleaseConnection(@Nullable Connection con, @Nullable DataSource dataSource) throws SQLException {
		if (con == null) {
			return;
		}
		if (dataSource != null) {
			ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
			if (conHolder != null && connectionEquals(conHolder, con)) {
				// It's the transactional Connection: Don't close it.
				conHolder.released();
				return;
			}
		}
		doCloseConnection(con, dataSource);
	}

What could be done with that?

Should you start proxying PreparedStatement as it is done in Hikari? Or maybe it could be fixed in other way?