CODEWITHSUNDEEP
hibernatewebsphere
Mahmoud Saleh
Mahmoud Saleh

Reputation: 33605

com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009

I have a Java application deployed on Websphere 8.5, and I am using hibernate-core-4.2.21.Final and hibernate-jpa-2.0-api-1.0.1.Final.

I use websphere datasource to connect to the database. Sometimes the application is unable to open connections to the datasource, and I get the error :

org.hibernate.exception.GenericJDBCException: Could not open connection
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
        at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
        at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:56)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:159)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:183)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:157)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1881)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1858)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
        at org.hibernate.loader.Loader.doQuery(Loader.java:906)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:348)
        at org.hibernate.loader.Loader.doList(Loader.java:2550)
        at org.hibernate.loader.Loader.doList(Loader.java:2536)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2366)
        at org.hibernate.loader.Loader.list(Loader.java:2361)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1623)
        at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
        at com.myapp.testApp.entities.RequestHome.findByUser(RequestHome.java:111)
        at com.myapp.jsf.beans.testApp.Tracking.loadUnderStudyTasks(Tracking.java:307)
        at com.myapp.jsf.beans.testApp.Tracking.inboxQueue(Tracking.java:232)
        at com.myapp.jsf.beans.testApp.Tracking.onload(Tracking.java:77)
        at sun.reflect.GeneratedMethodAccessor19066.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:328)
        at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:341)
        at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58)
        at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
        at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
        at com.sun.faces.facelets.el.TagMethodExpression.invoke(Unknown Source)
        at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(Unknown Source)
        at com.sun.faces.application.ActionListenerImpl.processAction(Unknown Source)
        at org.primefaces.application.DialogActionListener.processAction(DialogActionListener.java:45)
        at javax.faces.component.UIViewAction.broadcast(Unknown Source)
        at javax.faces.component.UIViewRoot.broadcastEvents(Unknown Source)
        at javax.faces.component.UIViewRoot.processApplication(Unknown Source)
        at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(Unknown Source)
        at com.sun.faces.lifecycle.Phase.doPhase(Unknown Source)
        at com.sun.faces.lifecycle.LifecycleImpl.execute(Unknown Source)
        at javax.faces.webapp.FacesServlet.service(Unknown Source)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1230)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
        at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:136)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:97)
        at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:100)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.filenet.ae.toolkit.server.servlet.filter.PostprocessorFilter.doFilter(PostprocessorFilter.java:38)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.filenet.ae.toolkit.server.servlet.filter.ContainerBasedFilter.doFilter(ContainerBasedFilter.java:218)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.filenet.ae.toolkit.server.servlet.filter.PreprocessorFilter.doFilter(PreprocessorFilter.java:91)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.filenet.ae.toolkit.server.servlet.filter.SecurityPluginFilter.doFilter(SecurityPluginFilter.java:202)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.filenet.ae.toolkit.server.servlet.filter.ThreadLocalCleanupFilter.doFilter(ThreadLocalCleanupFilter.java:50)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.myapp.web.filter.CoordinatorSecurityFilter.doFilter(CoordinatorSecurityFilter.java:208)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.dataserve.common.CompatibleFilter.doFilter(CompatibleFilter.java:53)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:964)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1104)
        at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3901)
        at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304)
        at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:981)
        at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1662)
        at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
        at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
        at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
        at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
        at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
        at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
        at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
        at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
        at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
        at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
        at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1865)
    Caused by: com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
        at com.ibm.ws.rsadapter.AdapterUtil.toSQLException(AdapterUtil.java:1680)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:661)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611)
        at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:301)
        at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
        ... 91 more

How I make transaction :

UserTransaction txn = EntityHome.getInstance().getTransaction();
txn.begin();
RequestHome requestHome = new RequestHome();
requestHome.doSomething();
txn.commit();

EntityHome class :

public class EntityHome {

    protected static final Log log = LogFactory.getLog(EntityHome.class);
    protected SessionFactory sessionFactory;
    private static ServiceRegistry serviceRegistry;
    private static EntityHome entityHome;

    public static EntityHome getInstance() {
        if (entityHome == null) {
            try {
                entityHome = new EntityHome();
            } catch (Exception e) {
                AppLogger.log("########### unable to configure hibernate,exception is: " + e.getMessage());
                entityHome = null;
            }
        }
        return entityHome;
    }

    private EntityHome() throws Exception {
        try {
            configureHibernate();
        } catch (Exception e) {
            throw e;
        }
    }

    private void configureHibernate() throws HibernateException {

        Configuration configuration = new Configuration().configure("hibernate-efada.cfg.xml");
        serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties())
                .buildServiceRegistry();
        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
    }

    public SessionFactory getSessionFactory() throws NamingException {
        if (sessionFactory == null || sessionFactory.isClosed()) {
            configureHibernate();
        }
        return sessionFactory;
    }

    public UserTransaction getTransaction() {

        UserTransaction transaction = null;

        try {
            Object o = new InitialContext().lookup("java:comp/UserTransaction");
            transaction = (UserTransaction) o;
            transaction.setTransactionTimeout(30);
            return transaction;
        } catch (Exception e) {
            e.printStackTrace();
            AppLogger.log("########### unable to getTransaction,exception is: " + e.getMessage());
            throw new IllegalStateException("Could not locate java:comp/UserTransaction in JNDI");
        }
    }
}

Hibernate config file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
  
    <property name="hibernate.session_factory_name">EfadaSessionFactory</property>
    <property name="hibernate.connection.isolation">1</property> <!-- READ UNCOMMITTED -->
    <!-- <property name="hibernate.hbm2ddl.auto">validate</property> -->
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.connection.datasource">EfadaDS</property>
    <property name="hibernate.default_schema">dbo</property>
    <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
    <property name="hibernate.connection.autocommit">false</property>
    <property name="hibernate.jndi.class">com.ibm.websphere.naming.WsnInitialContextFactory</property>

        
    <property name="hibernate.jndi.url">corbaloc::myserver-prod-fnicn1.mydomain.com:2809,:myserver-prod-FNICN2.mydomain.com:2809,:myserver-prod-fnicn1.mydomain.com:2811,:myserver-prod-FNICN2.mydomain.com:2811,:myserver-prod-fnicn1.mydomain.com:2812,:myserver-prod-FNICN2.mydomain.com:2812</property> 

    <property name="hibernate.transaction.auto_close_session">true</property>
    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
    <property name="hibernate.transaction.flush_before_completion">true</property>

    <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.WebSphereExtendedJTATransactionLookup</property>

    <property name="hibernate.current_session_context_class">jta</property>
    <property name="jta.UserTransaction">java:comp/UserTransaction</property>
    
    <mapping resource="gov/mci/myapp/entities/User.hbm.xml"/>

 </session-factory>
</hibernate-configuration>

I increased the maximum connections in the datasource from 100 to 300 and still getting this error. Do I have to change something else in my datasource configuration or in hibernate configuration?

Upvotes: 0

Views: 2062

Answers (2)

Mahmoud Saleh
Mahmoud Saleh

Reputation: 33605

The issue disappeared after making the following changes to the websphere datasource :

  • webSphereDefaultIsolationLevel : 1 // which is READ UNCOMMITTED
  • webSphereDefaultQueryTimeout : 30 // default query timeout for datasource

Upvotes: 0

Gas
Gas

Reputation: 18020

Your application isleaking connections, and not returning them properly to the datasource.

You can use WebSphere PMI (perf monitoring infrastructure) to monitor your datasource. Most probably you will see many 'creates' but no 'closes' of the connections.

You can also enable connection leak logic trace to see where the connections are being held: https://www.ibm.com/support/pages/how-troubleshoot-j2ca0045e-connection-pooling-problems

In Trace Specification, enter the following string: ConnLeakLogic=all

Few additional comments to your application design:

  1. Do not use Hibernate - WebSphere already provides JPA provider and you should code to JPA API and not use Hibernate objects. Use JPA like this - https://openliberty.io/guides/jpa-intro.html
  2. Do not manage transactions manually - you should rely on container to manage your transactions. Handling transactions by hand is error prone.

Upvotes: 1

Related Questions