java - Spring @Transactional not returning connection to Hikari pool

Question

I have a REST based application server built on embedded jetty, spring boot, and hibernate. It uses an MS SQL backend database.

My @Transactional methods work but do not appear to close the db connection eventually resulting in no available hikari pool members:

java.sql.SQLTransientConnectionException: HikariPoolProduct - Connection is not available, request timed out after 30003ms.

Shortly after the above I will see the following hikari stats:

10:29:24.774 [HikariPoolProduct housekeeper] DEBUG HikariPoolProduct - Pool stats (total=50, active=49, idle=1, waiting=0)
10:29:24.774 [HikariPoolProduct housekeeper] DEBUG HikariPoolProduct - Fill pool skipped, pool is at sufficient level.

I would expect active to be 1 or 0.

In my test case I have my hikari pool size set to 50. When I repeatedly have my client call the server CertificatePolicyEntityResource.get() via REST it works fine until it tries #51 and then it fails as indicated above.

I'm using @Transactional methods and I'm pretty sure the wrapper/proxy is suppose to free the hikari pool member (via a db virtual/proxy close) before returning. This doesn't seem to be happening.

My code call tree:

client -> [REST] -> CertificatePolicyEntityResource.get() ->
CertificatePolicyEntityServletAdapter.get() ->
DomainRegistryModelProxy.certificatePolicyService() // Use spring ApplicationContext to retrieve CertificatePolicyServiceRepositoryImpl
CertificatePolicyServiceRepositoryImpl.size() and .allCertificatePolicies() // These are @Transactional

Here is my service class CertificatePolicyServiceRepositoryImpl which has the @Transactional methods:

package cmb.domain.model.certpolicy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Accessors( fluent = true )
public class CertificatePolicyServiceRepositoryImpl implements CertificatePolicyService {
        @Getter
        private CertificatePolicyRepository             certificatePolicyRepository;

        public CertificatePolicyServiceRepositoryImpl(CertificatePolicyRepository certificatePolicyRepository) {
                this.certificatePolicyRepository = certificatePolicyRepository;
        }

        @Override
        @Transactional
        public CertificatePolicy certificatePolicyOfId(String id) {
                return certificatePolicyRepository().certificatePolicyOfId(id);
        }

        @Override
        @Transactional
        public List<CertificatePolicy> allCertificatePolicies() {
                return certificatePolicyRepository().allCertificatePolicies();
        }

    @Override
        @Transactional
        public List<CertificatePolicy> allActiveCertificatePolicies() {
                return certificatePolicyRepository().allActiveCertificatePolicies();
        }

        @Override
        @Transactional(readOnly = true)
        public int size() {
                return certificatePolicyRepository().size();
        }

}

CertificatePolicyEntityResource.java:

package cmb.cabridge.port.servlet.resource;

import javax.ws.rs.BeanParam;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.QueryParam;
    
@Path(CaBridgeCommandValues.RESOURCE_CERTIFICATEPOLICYENTITY)
public class CertificatePolicyEntityResource extends AbstractCaBridgeResource {
        @BeanParam
        private NetworkRequestContext                                   requestContext;

        @GET
        @Path(CaBridgeCommandValues.SUBRESOURCE_CERTIFICATEPOLICYENTITY_GET + "/{id}")
        public CertificatePolicyEntityResponse get(@PathParam("id") String idList,
                        @QueryParam(CertificatePolicyRequestFlags.FLAG_MINIENTITY) boolean miniEntityEnabled) {
                final String function = "Get by ID";
                ResponseHandler handler = createResponseHandler(m, function);

                try {
                        processRequest(requestContext,
                                        CaBridgeCommand.getInstance().create(CaBridgeCommandValues.CertificatePolicyEntityGet));
                        CertificatePolicyEntityResponse response = new CertificatePolicyEntityServletAdapter(caBridgeSessionContext())
                                        .get(idList, miniEntityEnabled);

                        handler.succeeded(response);
                        return response;
                } catch (Throwable e) {
                        CertificatePolicyEntityResponse response = new CertificatePolicyEntityResponse();
                        handler.failed(e, response);
                        return response;
                }
        }

}

Adapter:

package cmb.cabridge.port.servlet.adapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
    
@Component
public class CertificatePolicyEntityServletAdapter extends AbstractCaBridgeCommonServletAdapter {

        private CertificatePolicyService certificatePolicyService() {
            /*
             * Retrieve bean using Spring ApplicationContext
             */
                return DomainRegistryModelProxy.certificatePolicyService();
        }

        public CertificatePolicyEntityResponse get(String searchValue, boolean miniEntityEnabled) {
                List<CertificatePolicyEntity> results = new ArrayList<>();
                entityConverter().setMiniEntityEnabled(miniEntityEnabled);

                if (searchValue == null || searchValue.equalsIgnoreCase(GlobalConstantStandard.ALL)) {
                        if (certificatePolicyService().size() > 0)
                                certificatePolicyService().allCertificatePolicies().stream().forEach(
                                                item -> results.add(entityConverter.toEntity(item))
                                                                );
                } else {
                        List<String> ids = StringTool.splitAsList(searchValue, ",");
                        for (String id : ids) {
                                CertificatePolicy item = certificatePolicyService()
                                                                                        .certificatePolicyOfIdOrName(id);
                                if (item != null)
                                        results.add(entityConverter.toEntity(item));
                        }
                }

                CertificatePolicyEntityResponse response = new CertificatePolicyEntityResponse();
                if (results.size() == 0) {
                        String message = "No " + what + " found for ID/Name(s) " + searchValue;
                        log.info("%s", message);
                        response.setMessage(message);
                        response.setResult(Result.FAILED);
                        response.setFailureType(FailureType.NOTFOUND);
                } else {
                        response.setEntities( results );
                        response.setResult(Result.SUCCEEDED);
                }

                return response;
        }
}

Datasource class:

package cmb.cabridge.infrastructure.persistence.hibernate;

import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import cmb.common.infrastructure.DatabaseConfigurationTool;
import cmb.domain.model.CmbDomainModelMarker;
import cmb.product.domain.model.CmbProductDomainModelMarker;

import org.hibernate.SessionFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * CAB Hibernate Product Database Configuration
 */
@Configuration
@EnableJpaRepositories(
    entityManagerFactoryRef = HibernateProductDatabaseConfiguration.PRODUCT_ENTITY_MANAGER,
    transactionManagerRef = HibernateProductDatabaseConfiguration.PRODUCT_TX_MANAGER
)
@EnableTransactionManagement
public class HibernateProductDatabaseConfiguration {
        private Trace                                                                   log = TraceFactory.create(this);
        /*
         * Packages that Spring should scan to find @Entity classes
         */
        private static final String [] PACKAGES_TO_SCAN = {
                        MangoDomainModelMarker.class.getPackage().getName(),
                        CmbDomainModelMarker.class.getPackage().getName(),
                        CmbProductDomainModelMarker.class.getPackage().getName()
        };

        public static final String                                              PRODUCT_ENTITY_MANAGER = "productEntityManager";
        public static final String                                              PRODUCT_TX_MANAGER = "productTransactionManager";
        public static final String                                              SESSION_FACTORY_PRODUCT = "sessionFactoryProduct";
        // Default size of 10 is too small for CAB
        private static final int                                                MAX_POOL_SIZE = 250;
        private static final int                                                MAX_LIFETIME_SECONDS = 2 * 60; // XXX debug value
        //private static final int                                              MAX_LIFETIME_SECONDS = 60 * 60; // 1 hour
        private static final int                                                IDLE_TIMEOUT_SECONDS = 15 * 60;
        private static final int                                                LEAK_DETECTION_THRESHOLD_SECONDS = 30;
        private static final int                                                CONNECTION_TIMEOUT_SECONDS = 30;

        @Bean(name = SESSION_FACTORY_PRODUCT)
        @Primary
        public SessionFactory sessionFactoryProduct() {
                return sessionFactoryProductBean().getObject();
        }

        @Bean
        @Primary
    public LocalSessionFactoryBean sessionFactoryProductBean() {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(productDataSource());
        sessionFactory.setPackagesToScan( PACKAGES_TO_SCAN );
        sessionFactory.setHibernateProperties(hibernateProperties());

        return sessionFactory;
    }

    @Bean
        @Primary
    public DataSource productDataSource() {
        /*
         * Get the PersistenceConfiguration from persistconfig.properties and
         * then build Creator to format the configuration data as needed.
         */
        PersistenceConfiguration cf = ProductConfig.getPersistenceConfiguration();
        if (cf == null || cf.getDataBaseName() == null)
                throw new ConfigurationException("Failed to load persistence configuration");
        HibernateP

Answer 1

Waitting for answers