org.hibernate.exception.GenericJDBCException: Cannot open connection

[hohokampeople]

Hi,

>I've been encountering this error for a week now. I've tried to increase the blocking timeout from 5sec to 1min however it didn't work.

  Our application is running with oracle 11g.

  Any suggestion on how to solve this?

  Is there additional configuration that we need to do?

root Exception Stack Trace : org.hibernate.exception.GenericJDBCException: Cannot open connection

org.hibernate.exception.GenericJDBCException: Cannot open connection

          at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)

          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)

          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)

          at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)

          at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

          at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)

          at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)

          at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)

          at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:497)

          at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:349)

          at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:255)

          at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:102)

          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

          at $Proxy73.getRecipientDetails(Unknown Source)

          at aero.champ.fusion.schedular.EmailSchedular.sendEmailsAndMessages(EmailSchedular.java:225)

          at aero.champ.fusion.schedular.EmailSchedular.sendEmailPerCarrier(EmailSchedular.java:170)

          at aero.champ.fusion.schedular.EmailSchedular.run(EmailSchedular.java:77)

          at java.util.TimerThread.mainLoop(Timer.java:534)

          at java.util.TimerThread.run(Timer.java:484)

Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/dataSourcePK

          at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)

          at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)

          at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)

          ... 16 more

Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/dataSourcePK

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)

          at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368)

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)

          at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)

          ... 18 more

Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (60000 [ms])

          at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)

          at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397)

          at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

          ... 21 more

datasource definition:

    <datasource jndi-name="java:/dataSourcePK" pool-name="dataSourcePK" enabled="true" use-java-context="true">

                    <connection-url>jdbc:oracle:thin:@ddi:2222:webcli</connection-url>

                    <driver>oracle</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                    <pool>

                        <min-pool-size>8</min-pool-size>

                        <max-pool-size>32</max-pool-size>

                        <prefill>true</prefill>

                        <use-strict-min>true</use-strict-min>

                        <flush-strategy>FailingConnectionOnly</flush-strategy>

                    </pool>

                    <security>

                        <user-name>PK</user-name>

                        <password>PK</password>

                    </security>

                    <validation>

                        <exception-sorter class-name="org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter"/>

                    </validation>

                    <timeout>

                        <blocking-timeout-millis>60000</blocking-timeout-millis>

                        <idle-timeout-minutes>5</idle-timeout-minutes>

                    </timeout>

                    <statement>

                        <prepared-statement-cache-size>100</prepared-statement-cache-size>

                        <share-prepared-statements>true</share-prepared-statements>

                    </statement>

                </datasource>

[sfcoy]

Does this happen under load, or with a single user?

[hohokampeople]

Hi Coy,

     this happen under load not with a single user.

[ctomc]

Hi,

it is telling you that pool is full, aka all 32 connections that you have configured for max-pool-size are used up

and wait time for getting it (hoping that something gets retured into pool) has expired.

I would recommend you to increse max-pool-size to something bigger, especialy if it only happens under load.

Otherwise there might be issue with not closing connections.

--

tomaz

[sfcoy]

How many concurrent users?

Do you have transactions that take a long time?

[hohokampeople]

Hi Coy,

         There are only few users using are application however, it listen from the queue and consume messages as well.

          messages also consume the connection.

         Yes there is a transaction that take a long time. it is the processing of the messages.

[sfcoy]

Normally, each message is processed in its own transaction.

Does it take a long time to process a single message?

Is it possible to optimise the database transaction at all?

ie. write a better query.

[hohokampeople]

Yup you're correct each message is processed in its own transaction.

the fastest <5secs and the slowest is > 2mins.

Actually we are currently migrating from SUN app server 8.2 to jboss AS 7.1 (with the same DB oracle 11g)

but we haven't encoutered this error from the old server and we copied the configuration from the old server (e.g min-pool-size, max-poo-size, timeouts and etc.)

[sfcoy]

Are you using the stock ("mdb-strict-max-pool") mdb pool?

If you're queue is saturated it will be using 20 of your 32 database connections all by itself.

You may want to consider a smaller mdb pool size.

[hohokampeople]

we're not using mdb pool.. we used spring jms (esp. messageListener).

if i increase the size of max-pool-size will this solve the issue or it will cause another problem?

[sfcoy]

Please show your Spring JMS configuration.

[hohokampeople]

Hi Stephen,

    Here's the jms configuration

<bean id="messageExceptionListener" class="integration.core.impl.DefaultExceptionListener"/>

          <!--Message Listener -->

          <bean id="messageListener"

                    class="integration.core.impl.DefaultMessageHandler"

                    init-method="initialize">

                    <property name="integrationService" ref="integrationService" />

                    <property name="marshaller" ref="jaxb2Marshaller" />

                    <property name="emailSenderUtil" ref="emailSenderUtil" />

          </bean>

          <bean id="listenerContainer"

                    class="org.springframework.jms.listener.DefaultMessageListenerContainer102">

                    <property name="concurrentConsumers" value="1" />

                    <property name="connectionFactory"

                              ref="internalJmsQueueConnectionFactory" />

                    <property name="destination" ref="recieverQueue" />

                    <property name="messageListener" ref="messageListener" />

                    <property name="exceptionListener" ref="messageExceptionListener" />

                    <property name="autoStartup" value="true" />

                    <property name="sessionTransacted" value="true" />

          </bean>

          <bean id="listenerContainerForGFX"

                    class="org.springframework.jms.listener.DefaultMessageListenerContainer102">

                    <property name="concurrentConsumers" value="5" />

                    <property name="connectionFactory"

                              ref="internalJmsQueueConnectionFactory" />

                    <property name="destination" ref="receiverGFXQueue" />

                    <property name="messageListener" ref="messageListener" />

                    <property name="exceptionListener" ref="messageExceptionListener" />

                    <property name="autoStartup" value="true" />

                    <property name="sessionTransacted" value="true" />

          </bean>

          <!-- JNDI Environment Template -->

          <bean id="jndiTemplate"

                    class="org.springframework.jndi.JndiTemplate">

                    <property name="environment">

                              <props>

                                        <prop key="java.naming.factory.initial">

                                                  com.sun.jndi.fscontext.RefFSContextFactory

                                        </prop>

                                        <prop key="java.naming.provider.url">

                                                  file:/opt/SUNWappserver/appserver/fusion/jndi

                                        </prop>

                              </props>

                    </property>

          </bean>

          <!-- JMS Queue Connection Factory -->

          <bean id="internalJmsQueueConnectionFactory"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_XAQCF</value>

                    </property>

          </bean>

          <!-- JMS Destination -->

          <bean id="destinationQueue"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_FQueue</value>

                              <!-- <value>JMS_FQueueDemo</value>-->

                    </property>

          </bean>

          <bean id="recieverQueue"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_TQueue</value>

                              <!-- <value>JMS_TQueueDemo</value>-->

                    </property>

          </bean>

          <!-- Priority Destination Queue -->

          <bean id="priorityDestinationQueue"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_FPriorityQueue</value>

                              <!-- <value>JMS_FPriorityQueueDemo</value>-->

                    </property>

          </bean>

          <bean id="priorityRecieverQueue"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_TPriorityQueue</value>

                              <!-- <value>JMS_TPriorityQueueDemo</value>-->

                    </property>

          </bean>

          <bean id="receiverGFXQueue"

                    class="org.springframework.jndi.JndiObjectFactoryBean">

                    <property name="jndiTemplate">

                              <ref bean="jndiTemplate" />

                    </property>

                    <property name="jndiName">

                              <value>JMS_TGFXQueue</value>

                              <!-- <value>JMS_TGFXQueueDemo</value>-->

                    </property>

          </bean>

          <!-- JMS Queue Templates -->

          <bean id="jmsTemplateProducer"

                    class="org.springframework.jms.core.JmsTemplate102">

                    <property name="connectionFactory">

                              <ref bean="internalJmsQueueConnectionFactory" />

                    </property>

                    <property name="defaultDestination">

                              <ref bean="destinationQueue" />

                    </property>

                    <property name="receiveTimeout">

                              <value>60000</value>

                    </property>

          </bean>

          <bean id="recieverJmsTemplateProducer"

                    class="org.springframework.jms.core.JmsTemplate102">

                    <property name="connectionFactory">

                              <ref bean="internalJmsQueueConnectionFactory" />

                    </property>

                    <property name="defaultDestination">

                              <ref bean="recieverQueue" />

                    </property>

                    <property name="receiveTimeout">

                              <value>30000</value>

                    </property>

          </bean>

          <!--JaxB Marshaller  -->

          <bean id="jaxb2Marshaller"

                    class="org.springframework.oxm.jaxb.Jaxb2Marshaller">

                    <property name="classesToBeBound">

                              <list>

                                        <value>

                                                  luc.LUCMessage

                                        </value>

                                        <value>

                                                  scm.SCMMessage

                                        </value>

                                        <value>

                                                  ucm.UCMMessage

                                        </value>

                                        <value>

                                                  rej.REJMessage

                                        </value>

                                        <value>

                                                  ack.ACKMessage

                                        </value>

                                        <value>

                                                  uldfltreq.ULDFltListReq

                                        </value>

                                        <value>

                                                  uldfltres.ULDFltListRes

                                        </value>

                                        <value>

                                                  gfx.GFXMessage

                                        </value>

                                        <value>

                                                  txt.TXTMessage

                                        </value>

                                        <value>

                                                  muc.MUCMessage

                                        </value>

                              </list>

                    </property>

                    <property name="marshallerProperties">

                              <map>

                                        <entry key="jaxb.encoding" value="UTF-8" />

                              </map>

                    </property>

                    <property name="schema" value="classpath:FusionMessage.xsd" />

          </bean>

          <bean id="jmsTracer"

                    class="integration.tracemessages.JMSTracerImpl">

                    <property name="jmsLoggerFacade" ref="jmsLoggerFacade" />

          </bean>

[sfcoy]

As far as I can tell from this, your queue is being processed by at most 5 messageListeners.

I think I would a need a thread dump in order to see what is going on.

Can you attach a thread dump taken at a time when you know the JDBC connection pool is exhausted?

[hohokampeople]

Hi Stephen,

     i cannot send you the thread dump anymore because we switched back again to the old server and then stopped the jboss AS (we are using load balancer).

     In case it happens again what we should look?

[sfcoy]

Presumably there are 32 threads that each have hold of a database connection.

It should be easy to pick from this whether it's the messaging system or just slow user transactions that is causing the problem.

Slow or long running transactions are the nemesis of scalability. Normally I would expect 32 database connections to be able to handle 100 users or more.