Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Basics - Troubleshooting Hibernate / JDBC Connection Pool Issue

What is Hibernate's responsibility in regards to database connections it gets from an underlying connection pool. Does it test to see if a connection is closed before it uses it? and if so get another connection from the pool?

I've included error and confirmation info below. Any ideas of where I can start to troubleshoot this would be very helpful. And any advice on the SQL Server driver settings we are using.

from the Catalina log:

04-Nov-2010 21:54:52.691 WARNING org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection[ConnectionID:8]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:926)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:681)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:545)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:166)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:106)

from our application log:

2010-11-04 21:54:52,705 [tomcat-http--18] WARN  util.JDBCExceptionReporter  - SQL Error: 0, SQLState: 08S01
2010-11-04 21:54:52,707 [tomcat-http--18] ERROR util.JDBCExceptionReporter  - Socket closed
2010-11-04 21:54:52,708 [tomcat-http--18] ERROR transaction.JDBCTransaction  - JDBC rollback failed
java.sql.SQLException: Connection has already been closed.
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:112)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94)
    at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:132)
    at $Proxy38.rollback(Unknown Source)
    at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:217)
    at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:196)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:676)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:412)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:111)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625)

The configuration:

<Resource defaultAutoCommit="false" defaultReadOnly="false"
        defaultTransactionIsolation="SERIALIZABLE"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        fairQueue="false" initialSize="10"
        jdbcInterceptors="ConnectionState;StatementFinalizer"
        jmxEnabled="true" logAbandoned="true" maxActive="100"
        maxIdle="10" maxWait="30000"
        minEvictableIdleTimeMillis="10000" minIdle="10"
        name="com.ourcompany.ap.shoppingcart/datasource"
        password="somePassword" removeAbandoned="true"
        removeAbandonedTimeout="60" testOnBorrow="true"
        testOnReturn="false" testWhileIdle="false"
        timeBetweenEvictionRunsMillis="5000"
        type="javax.sql.DataSource"
        url="jdbc:sqlserver://approd\approd;databaseName=prod"
        useEquals="false" username="AccessPointNet"
        validationInterval="30000" validationQuery="SELECT 1"/>`
like image 404
BuddyJoe Avatar asked Nov 05 '10 15:11

BuddyJoe


People also ask

What are some of the main issues with using connection pools?

One of the most common issues undermining connection pool benefits is the fact that pooled connections can end up being stale. This most often happens due to inactive connections being timed out by network devices between the JVM and the database. As a result, there will be stale connections in the pool.

Does Hibernate use connection pooling?

Hibernate supports a variety of connection pooling mechanisms. If you are using an application server, you may wish to use the built-in pool (typically a connection is obtaining using JNDI).

Which is best connection pool for Hibernate?

The default connection pool in hibernate is c3p0 named after the star wars character. But hibernate supports also proxool and used to also advertise apache dbcp. For a while DBCP was dormant and fell out of grace. C3P0 is actually used in production in many projects.

What is Hibernate connection pool size?

The Hibernate Connection Pool Size property establishes the number of connections that are permitted between the Model repository and the Model Repository Service database. The default value is 10. In deployments with many concurrent connections, you can increase the property to increase performance.


4 Answers

I had a similar problem which was solved by increasing the removeAbandonedTimeout value to a higher number. The problem we faced was due to the query which took longer time that the above mentioned timeout.

like image 172
Cid Avatar answered Oct 18 '22 06:10

Cid


What is Hibernate's responsibility in regards to database connections it gets from an underlying connection pool.

Not much, releasing it when the Session gets closed.

Does it test to see if a connection is closed before it uses it? and if so get another connection from the pool?

No, Hibernate doesn't, checking the validity of connection(s) is the responsibility of a connection pool if you want to.

I've included error and confirmation info below. Any ideas of where I can start to troubleshoot this would be very helpful.

What kind of process are you running exactly? A long transaction? Does it timeout? What does the Caused by: say? About the trace:

2010-11-04 21:54:52,705 [tomcat-http--18] WARN util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01 
2010-11-04 21:54:52,707 [tomcat-http--18] ERROR util.JDBCExceptionReporter - Socket closed
2010-11-04 21:54:52,708 [tomcat-http--18] ERROR transaction.JDBCTransaction - JDBC rollback failed java.sql.SQLException: Connection has already been closed.

Can you reproduce it in a deterministic way? Any networking problem?

And any advice on the SQL Server driver settings we are using.

I've added a great resource about Tomcat and connection pool configuration below. Not specific to SQL Server though.

Resources

  • Configuring jdbc-pool for high-concurrency
like image 24
Pascal Thivent Avatar answered Oct 18 '22 05:10

Pascal Thivent


We usually work around this by using dbcp, and providing a validationQuery when definining our data source. Then, dbcp will verify the usability of pooled connections by issuing that query (and transparently recreate the connection should it no longer work), prior to returning them to the application.

Check out http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html for more details.

like image 32
meriton Avatar answered Oct 18 '22 06:10

meriton


I am currently using liquibase(v1.9) in my project, and when the changeSets run against a blank schema it always takes longer than 60 seconds which results in the thread being marked abandoned I'm not thrilled with increasing the removeAbandonedTimeout value, but this is the only solution I've been able to find to prevent this issue; however, after the initial schema population is complete this is seldom a problem so I set the value back to 60 seconds.

like image 2
mjj1409 Avatar answered Oct 18 '22 04:10

mjj1409