CODEWITHSUNDEEP
springhibernatejdbc
Alexandre Boudreault
Alexandre Boudreault

Reputation: 121

Hibernate JDBC connection blocking

I'm using spring + hibernate + tomcat jdbc in my java application. I want to be able to handle connection problem when , for exemple, a database crash occur. The problem I got is that hibernate block on trying to get a jdbc connection (when for exemple the mysql is down) and never timeout making the http request hanging indefinitly.

Here's my hibernate config : 

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
  <property name="dataSource">
    <ref bean="dataSource"/>
  </property>
  <property name="hibernateProperties">
       <props>
         <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
         <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
         <prop key="hibernate.connection.release_mode">auto</prop>
     </props>
  </property>
  <property name="mappingResources">
    <list>
      <value>detection/model/conf/rules.hbm.xml</value>
</list>
  </property>
</bean>

And my jdbc datasource config :

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">

    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="initialSize" value="10" />
    <property name="maxActive" value="100" />
    <property name="maxIdle" value="50" />
    <property name="minIdle" value="10" />
    <property name="testOnBorrow" value="true"/>
    <property name="testOnReturn" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="maxWait" value="2000"/>
    <property name="validationQuery" value="/*ping*/ SELECT 1"/>
    <property name="timeBetweenEvictionRunsMillis" value="2000"/>
    <property name="removeAbandonedTimeout" value="2000"/>
    <property name="removeAbandoned" value="true"/>
    <property name="validationInterval" value="5000"/>

 </bean>

Is there any way to tell hibernate to timeout waiting for a connection after a certain time ?

UPDATE :

I switch to c3p0 but i got the same behavior, but i got more debug info with c3p0. I can see that an exception is thrown but c3p0 seem to catch it and do nothing with it so the http request still hang till i restart the mysql server.

DEBUG 2012-12-07 09:07:46,994 : Opening Hibernate Session
DEBUG 2012-12-07 09:07:46,994 : opened session at timestamp: 13548892669
DEBUG 2012-12-07 09:07:46,994 : about to open PreparedStatement (open         PreparedStatements: 0, globally: 2)
DEBUG 2012-12-07 09:07:46,994 : opening JDBC connection
DEBUG 2012-12-07 09:07:46,994 : trace com.mchange.v2.resourcepool.BasicResourcePool@195b6aad [managed: 10, unused: 7, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@6b0ede6)
DEBUG 2012-12-07 09:07:46,994 : select rules0_.product as product2_, rules0_.rules as   rules2_, rules0_.type as type2_ from rules rules0_ where rules0_.product=? and rules0_.type=?
DEBUG 2012-12-07 09:07:46,995 : com.mchange.v2.c3p0.impl.NewPooledConnection@60487c5f handling a throwable.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Upvotes: 2

Views: 2941

Answers (2)

Alexandre Boudreault
Alexandre Boudreault

Reputation: 121

Finally got the behavior i wanted with c3p0. Here's the config i used : 

    <property name="acquireRetryDelay" value="5000"/>
    <property name="breakAfterAcquireFailure" value="true"/>
    <property name="checkoutTimeout" value="1"/>
    <property name="testConnectionOnCheckin" value="1" />

I think the key property was breakAfterAcquireFailure which will correctly remove a broken connection from the pool in contrast with the idleCheck test who don't close the connection and hang indefinitly when the database shut down. This make the checkoutTimeout work properly since the pool is correctly managed. The connection provider also correctly reconnect when the database is bringed back up.

Upvotes: 1

MarkOfHall
MarkOfHall

Reputation: 3353

This SO Post describes how to configure C3PO to throw exception when a Connection can't be acquired.

Upvotes: 0

Related Questions