Connection pooling Hibernate

Question

I have a Java application with Hibernate. I just implement it c3p0. Aperentemente everything works at first (as soon as I restart the Tomcat server in our host). But soon after a few customer access, it happens to not create more sessions for interaction with our BD mysql. Can anyone help me on this? I will be grateful.

following codes:

Hibernate.cfg.xml:

<property name="hibernate.connection.provider_class">
org.hibernate.connection.C3P0ConnectionProvider
</property>
<property name="hibernate.c3p0.min_size">7</property>
<property name="hibernate.c3p0.max_size">53</property>
<property name="hibernate.c3p0.timeout">100</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">1000</property>
<property name="hibernate.c3p0.validate">true</property>

HibernateUtil.java:

public final class HibernateUtil{  
     private static SessionFactory sessionFactory;  
        public static SessionFactory getSessionFactory()  throws MappingException  
        {  
            if(sessionFactory == null) {
                //sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
                sessionFactory = new Configuration().configure().buildSessionFactory();
            }  
            return sessionFactory;  
        } 

        public static Session openSession()  
        {  
            return getSessionFactory().openSession();  
        }  

}

CidadesDao.java:

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import ConnectionHibernate.ConnectionHibernate;
import ConnectionHibernate.HibernateUtil;
import br.com.cuponero.bean.Cidades;

public class CidadesDao {
    private static final String TABLE = "Cidades";
    Session session;

    public CidadesDao() {
        this.session = HibernateUtil.getSessionFactory().openSession();
        this.session.beginTransaction();
    }

    /**
     * Select todos
     * @return 
     **/
    public List<Cidades> getTodos(){
          List<Cidades> lCit = null;

          try{
             Query q = session.createSQLQuery("SELECT * FROM "+TABLE)
                     .addScalar("id", StandardBasicTypes.BIG_INTEGER)
                     .addScalar("nome", StandardBasicTypes.STRING)
                     .addScalar("ddd", StandardBasicTypes.STRING)
                     .addScalar("estaoId", StandardBasicTypes.INTEGER)
                     .addScalar("ativo", StandardBasicTypes.INTEGER)

                     .setResultTransformer(Transformers.aliasToBean(Cidades.class));
             //System.out.println("list 0: "+q.list().get(0));

             lCit = q.list();
             //tx.commit();

          }catch (HibernateException e) {
             if (session!=null) session.beginTransaction().rollback();
             e.printStackTrace(); 
          }finally{
              //closeMyConnection();
              //mySessionClose();

              session.beginTransaction().commit();
          }

          return lCit;
    }

...

following the LOG this Web Service:

mar 27, 2016 4:30:32 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run ADVERTÊNCIA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@172ea03 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User cupone_plinhares already has more than 'max_user_connections' active connections at sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1686) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2254) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44) at sun.reflect.GeneratedConstructorAccessor23.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)

mar 27, 2016 4:30:32 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run ADVERTÊNCIA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3ba80 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User cupone_plinhares already has more than 'max_user_connections' active connections at sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1686) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2254) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44) at sun.reflect.GeneratedConstructorAccessor23.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)

mar 27, 2016 4:30:32 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillAcquires ADVERTÊNCIA: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@16df536 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests. mar 27, 2016 4:30:32 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillAcquires ADVERTÊNCIA: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@16df536 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests. mar 27, 2016 4:30:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 0, SQLState: null mar 27, 2016 4:30:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Connections could not be acquired from the underlying database! mar 27, 2016 4:30:32 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run ADVERTÊNCIA: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2386af -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User cupone_plinhares already has more than 'max_user_connections' active connections at sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1686) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2254) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44) at sun.reflect.GeneratedConstructorAccessor23.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)

For an best help, Following the log in Android Studio of the my Application when i tries to connect the this Web Service UP:

org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:90) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:230) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:237) org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:213) org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52) org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1454) br.com.cuponero.dao.CidadesDao.<init>(CidadesDao.java:31) br.com.cuponero.actionsDaServlet.CidadesActionDaServlet.getJsonRegiao(CidadesActionDaServlet.java:35) br.com.cuponero.servlet.CidadesServlet.doPost(CidadesServlet.java:69) javax.servlet.http.HttpServlet.service(HttpServlet.java:650) javax.servlet.http.HttpServlet.service(HttpServlet.java:731) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause java.sql.SQLException: Connections could not be acquired from the underlying database! com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118) com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:689) com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) org.hibernate.c3p0.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:73) org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:382) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:87) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:230) org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:237) org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:213) org.hibernate.engine.

see that this log line she says the problem is here: br.com.cuponero.dao.CidadesDao. & lt; init & gt; (CidadesDao.java:31) what has this line is this code:

this.session.beginTransaction ();

Questions: How many instances of CidadesDao do you have? is it a singleton? How many more DAOs are there that connect to the database? Why are you using creating a session in the DAO constructor - do you do that in all your DAOs? — uncaught_exception, Mar 27 '16 at 23:59
So I have 4 more classes dao similar to this, and they also have the same logic to instantiate these sessions. What is your suggestion ? — Paulo Linhares - Packapps, Apr 04 '16 at 19:59
The suggestion is to create a session in the method and not maintain it as a member variable as Steve suggested too. I asked for classes and instances because I wanted to know why the connections were exhausted. If every instance is using a connection from the pool and not returning it they will get depleted. I wanted to get an idea of the count. — uncaught_exception, Apr 04 '16 at 20:23

score 1 · Answer 1 · answered Mar 28 '16 at 02:07

So this is straight from your stack traces:

User cupone_plinhares already has more than 'max_user_connections' active connections

An obvious thing to try is to increase max_user_connection for user cupone_plinhares.

Before looking at your stack traces, I thought that the issue was going to be a Connection leak. Your DAO creates a hibernate session as a member variable in its constructor, and nothing seems to close it. Also, your transaction management looks iffy.

A better idea would be NOT to have session as a member variable at all, but to have something like

Session session = null;
Transaction txn = null;
try {
  session = HibernateUtil.getSessionFactory().openSession();
  txn = session.beginTransaction();

  //do important stuff here

  txn.commit()
} catch ( Exception e ) {
  try { if (txn != null) txn.rollback() }
  catch (HibernateException he) { 
    he.printStackTrace(); 
    e.addSuppressed( he );
    throw e;
  }
} finally {
  try { if (session != null) session.close() }
  catch (SQLException sqle) { sqle.printStackTrace(); }
}

You can log things differently than printing to standard error, or make different decisions about which Exception to propagate if txn.rollback() fails. But you need to begin / commit / rollback a consistent transaction, and you should create Sessions as needed and destroy them promptly rather than holding them open in a member variable whose lifecycle is indeterminate.

The point of a Connection pool is to make Session creation cheap, so you don't have to deal with the complexity of managing a persistent session.

I will then test with an instance of Transition instead of Session as I am doing. But one thing that solved my problem was in part to close the Session Dao after printing the screen any results from the bank. ... out.print ("result of the bank"); myInstanceDao.closeMySessions (); And here in my method I: public void closeMySessions () { session.beginTransaction () commit ().; session.close (); } But my application is still consuming a lot of memory, and I believe it is something related to it. Who can continue helping there, thank ok. — Paulo Linhares - Packapps, Apr 04 '16 at 20:09

Connection pooling Hibernate

1 Answers1