Hibernate/c3p0 connection leak

Question

We're running a spring/hibernate/c3p0 application under load. When I reduce the c3p0 maxPoolSize to some far, far lower than the number of concurrent users, our application just hangs. There are no error messages in the log, but it doesn't proceed forward either.

I expect the application to slow down, but not to stop altogether.

Here is our c3p0 configuration:

<bean id="coreDataSource" 
          class="com.mchange.v2.c3p0.ComboPooledDataSource"
          destroy-method="close"
          p:driverClass="${core.jdbc.driver}"
          p:jdbcUrl="${core.jdbc.url}"
          p:user="${core.jdbc.user}"
          p:acquireIncrement="5"        
          p:acquireRetryAttempts="10"
          p:acquireRetryDelay="5000"
          p:initialPoolSize="52"
          p:maxIdleTime="3600"
          p:maxIdleTimeExcessConnections="300"
          p:minPoolSize="52"
          p:maxPoolSize="125"
          p:numHelperThreads="6"
          p:unreturnedConnectionTimeout="0">
          <property name="password">
              <bean class="com.docfinity.util.encryption.SpringStringDecrypter"
                  p:decryptFlag="${core.jdbc.decryptPasswordFlag}"
                  p:encryptedString="${core.jdbc.password}" />
          </property>
    </bean>

This will lock up if I throw a 160 users at it.

I tried setting unreturnedConnectionTimeout to something positive (120 seconds), and looked at the stack traces that show up in our application. The stack traces are from various different methods in our application. It's not like there's one method we can point to and say that it's leaking connections.

Any help debugging this issue would be most appreciated.

Flicker

Answer 1

I doubt that Hibernate or Spring are leaking connections, I suspect a configuration problem somewhere that is making your application running our of connections. Here is what I would do:

• Lower the number of concurrent users and the pool size, I'm not sure the problem is load related.

• Set unreturnedConnectionTimeout to a value greater than 0 in combination with debugUnreturnedConnectionStackTraces to true to figure out where Connections are being checked-out and not returned to the pool and post some of the generated stacktrace.

• Identify one business flow (one use case scenario) on which the problem occurs and run your test on this scenario only until you find out the problem.

Also, I'd update the question with one or two stacktraces, maybe someone will spot something obvious.

Bottomry

Answer 2

Hibernate and Spring are not the ones leaking connections, somewhere in your app is leaking. I'm not sure about C3P0 but BoneCP (http://jolbox.com) has support to detect unclosed connections (and give you stack traces of where you opened them) + will close off any leaking connections for you when a thread dies off without proper cleanup.

Potsdam

Answer 3

this post describes how to debug c3p0 connection leak issue using the parameters and stacktrace. Hope this helps

Taction

Answer 4

Query your database:

select * from pg_stat_activity;

And check which queries are long lasting with idle in transaction status. Try to locate them in your code and research why transaction is not completed.

---

Few things to check in a code/configuration:

• Commit transactions explicitly or use @Transactional. Please note that @Transactional works only for public methods.

• If you're using Hibernate 5.1.0.Final, then persistence.xml should contain:

<property name="hibernate.connection.provider_class" value="org.hibernate.c3p0.internal.C3P0ConnectionProvider" />

Instead of:

<property name="connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />

• If you're using

<property name="hibernate.enable_lazy_load_no_trans" value="true" />

it may cause connection leaks when lazy loading. Related discussions:

• org.hibernate.LazyInitializationException - could not initialize proxy - no Session
• Solve Hibernate Lazy-Init issue with hibernate.enable_lazy_load_no_trans

---

Check related articles:

• The best way to detect database connection leaks
• How we fixed all database connection leaks

Demand