connection leaks

[infprime]

Hi,
In my usage, I'm delegating all database access to dropwizard-entitymanager and not managing database connectivity manually anywhere (I'm also using C3P0 for pooling) After running the app for a while, I saw a MySQLNonTransientConnectionException: Too many connections exceptions (log here: https://gist.github.com/bladecatcher/a297171f8fedbbadbcac50478fb91e79)

When I saw this for the first time, I realised that the mysql instance I was running could only accept a maximum of 66 connections (AWS RDS micro tier) and I had hibernate.c3p0.max_size set to 100. I then reduced the hibernate.c3p0.max_size to 10.

After running the new instance for a while, I now observe (upon taking a thread dump) that C3P0 hangs at

#2939 prio=5 os_prio=0 tid=0x00007f02e0086000 nid=0x5412 in Object.wait() [0x00007f02afcfa000]
   java.lang.Thread.State: WAITING (on object monitor)
        at java.lang.Object.wait(Native Method)
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1414)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
        - locked <0x0000000083a8b600> (a com.mchange.v2.resourcepool.BasicResourcePool)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
        at org.hibernate.c3p0.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:73)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:84)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:109)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:227)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:234)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
        at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52)
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1525)
        at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:45)
        at com.scottescue.dropwizard.entitymanager.UnitOfWorkAspect.beginTransaction(UnitOfWorkAspect.java:90)
        at com.scottescue.dropwizard.entitymanager.UnitOfWorkAspect.beforeStart(UnitOfWorkAspect.java:47)
        at com.scottescue.dropwizard.entitymanager.UnitOfWorkAwareProxyFactory.lambda$create$0(UnitOfWorkAwareProxyFactory.java:81)
        at com.scottescue.dropwizard.entitymanager.UnitOfWorkAwareProxyFactory$$Lambda$33/2083925590.invoke(Unknown Source)

My hibernate configuration is as follows:

    hibernate.hbm2ddl.auto: validate
    hibernate.format_sql: true
    hibernate.use_sql_comments: true
    hibernate.dialect: org.hibernate.dialect.MySQLDialect
    hibernate.current_session_context_class: thread
    hibernate.cache_provider: org.hibernate.cache.NoCacheProvider
    hibernate.ejb.naming_strategy: org.hibernate.cfg.ImprovedNamingStrategy
    hibernate.archive.autodetection: none

    hibernate.connection.provider_class: org.hibernate.connection.C3P0ConnectionProvider
    hibernate.connection.driver_class: com.mysql.jdbc.Driver

    hibernate.c3p0.max_size: 10
    hibernate.c3p0.min_size: 1
    hibernate.c3p0.acquire_increment: 1
    hibernate.c3p0.idle_test_period: 300
    hibernate.c3p0.max_statements: 0
    hibernate.c3p0.timeout: 3000

    hibernate.connection.isolation: 2
    hibernate.connection.url: <>
    hibernate.connection.username: <> 
    hibernate.connection.password: <> 

This seems to indicate a possible connection leak. Found this Stack Overflow link http://stackoverflow.com/questions/14105932/c3p0-hangs-in-awaitavailable-with-hibernate which discusses this error.

[scottescue]

I've found something similar in one of my apps when running scheduled jobs that aren't triggered by a web request.

Are you using @UnitOfWork for transaction management? Could you provide a simple example of how your app is using dropwizard-entitymanager?

[julienwilliams]

Hello,

I just ran into a similar issue. I'm trying to multi thread queries therefore I'm using UnitOfWorkAwareProxyFactory to have access to an EntityManager on my runner threads. This leads to a connection leak. After debugging, it seems that the issue is because the afterEnd() method in UnitOfWorkAspect does not close the entityManager and therefore does not release the connection.

There is a comment indicating that the entityManager should not be closed in case the session is needed while serializing the response for lazy loading purposes. The reason why it does not leak on web requests is that the UnitOfWorkApplicationListener calls EntityManagerContext.unBindAll which closes all the entityManagers in the current thread context. UnitOfWorkAwareProxyFactory created proxies do not have the same behavior and therefore never close the entityManagers.