Does Bitronix TransactionManager and C3P0 Connection Pool works together?

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Does Bitronix TransactionManager and C3P0 Connection Pool works together?

mariaprabudass
This post has NOT been accepted by the mailing list yet.
We have planned to configure the C3P0 settings to avoid DB connectivity issue for both application server side and java standalone application. For server side application we have configured 'AS7TransactionManagerLookup' as Transaction Manger and C3P0 as Connection Pool, also for java standalone application we have configured 'BTMTransactionManagerLookup' as Transaction Manger and C3P0 as Connection Pool.

In jBoss application server C3P0 settings working fine. But for standalone application its not working. Could you please advise, can I use BTM as Transaction Manger and C3P0 as Connection Pool for java standalone? I have submitted my configuration for your references.

We have used spring version "3.1.1.RELEASE", hibernate version "3.6.9.Final" and c3p0 version "0.9.5.1".

applicationContext.xml:-
 p:idleConnectionTestPeriod="10"                    
          p:checkoutTimeout="3000"
          p:maxIdleTimeExcessConnections="1"
          p:maxStatementsPerConnection="1000"
          p:testConnectionOnCheckout="true"
          p:preferredTestQuery="SELECT 1;"          
          p:numHelperThreads="1000"
          p:testConnectionOnCheckin="true"
          p:unreturnedConnectionTimeout="7" />

        <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource">
            <ref local="dataSource"/>
        </property>
        <property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration"/>
        <property name="packagesToScan">
            <list>
                <value>com.test.management.domain</value>
                <value>com.test.user.domain</value>
                <value>com.test.workflow.domain</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
                <prop key="hibernate.hbm2ddl.auto">validate</prop>
            </props>
        </property>           
    </bean>

   
    <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
        <property name="sessionFactory">
            <ref local="sessionFactory"/>
        </property>
        <property name="flushModeName" value="FLUSH_COMMIT" />
    </bean>

persistence.xml:-
<persistence-unit name="org.jbpm.persistence.jpa.elseviercon">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:jboss/datasources/testDS</jta-data-source>
        <mapping-file>META-INF/JBPMorm.xml</mapping-file>
        <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>
        <class>org.drools.persistence.info.SessionInfo</class>
        <class>org.drools.persistence.info.WorkItemInfo</class>
        <class>org.jbpm.process.audit.ProcessInstanceLog</class>
        <properties>
            <property name="hibernate.max_fetch_depth" value="3"/>
            <property name="hibernate.hbm2ddl.auto" value="validate" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup" />
        <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
        <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/>
        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/test_qa"/>
            <property name="hibernate.connection.username" value="xxx"/>
            <property name="hibernate.connection.password" value="xxx"/>
        <property name="hibernate.c3p0.acquireRetryDelay" value="10000"/>
            <property name="hibernate.c3p0.acquireRetryAttempts" value="7"/>
            <property name="hibernate.c3p0.acquire_increment" value="7"/>
        <property name="hibernate.c3p0.idleConnectionTestPeriod" value="600" />
        <property name="hibernate.c3p0.hibernate.c3p0.preferredTestQuery" value="select 1;" />
        <property name="hibernate.c3p0.maxStatements" value="150" />
        <property name="hibernate.c3p0.maxIdleTime" value="20" /> 
        <property name="hibernate.c3p0.testConnectionOnCheckin" value="true" />     
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="false" />
        <property name="debugUnreturnedConnectionStackTraces" value="true" />
        </properties>
    </persistence-unit>

Error Log:-
Feb 22, 2016 6:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
Feb 22, 2016 6:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
Feb 22, 2016 6:17:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
Feb 22, 2016 6:17:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:38 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
Feb 22, 2016 6:17:44 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:44 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
Feb 22, 2016 6:17:44 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: null
Feb 22, 2016 6:17:44 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: An attempt by a client to checkout a Connection has timed out.
===============org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; An attempt by a client to checkout a Connection has timed out.; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; An attempt by a client to checkout a Connection has timed out.; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:683)
    at com.test.management.dao.BaseDao.create(BaseDao.java:43)
    at com.test.workflow.dao.MessageQErrorLogDaoImpl.create(MessageQErrorLogDaoImpl.java:25)
    at com.test.workflow.dao.MessageQErrorLogDaoImpl$$FastClassByCGLIB$$d1d23e85.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
    at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
    at com.test.workflow.dao.MessageQErrorLogDaoImpl$$EnhancerByCGLIB$$8d1e0984.create(<generated>)
    at com.test.workflow.services.MessageQErrorLogServiceImpl.create(MessageQErrorLogServiceImpl.java:33)
    at com.test.messageq.IntegrationConsumer.createMessageQErrorLog(IntegrationConsumer.java:908)
    at com.test.messageq.IntegrationConsumer.createAndInitiateArticle(IntegrationConsumer.java:662)
    at com.test.messageq.IntegrationConsumer.main(IntegrationConsumer.java:128)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.simontuffs.onejar.Boot.run(Boot.java:340)
    at com.simontuffs.onejar.Boot.main(Boot.java:166)
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
    at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.prepare(IdentityGenerator.java:90)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:54)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
    at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
    at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
    at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697)
    at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:686)
    at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:1)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 22 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@7eafc552 -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    ... 47 more

Thanks for looking into this..