Hibernate not releasing connections from connection pool

Question

I'm creating an application with Hibernate JPA and I use c3p0 for connection pooling with MySQL. I have an issue with the number of connections to the MySQL database as it hits the 152 opened connections, this is not wanted since I define in my c3p0 config file the max pool size to 20, and of course I close every entity manager I get from the EntityManagerFactory after committing every transaction.

For every time a controller is executed, I notice more than 7 connections are opened, and if I refresh, then 7 connections are opened again without the past idle connections being closed. And in every DAO function I call, the em.close() is executed. I admit here that the issue is in my code, but I don't know what I am doing wrong here.

This is the Sondage.java entity:

@Entity
@NamedQuery(name="Sondage.findAll", query="SELECT s FROM Sondage s")
public class Sondage implements Serializable {

    private static final long serialVersionUID = 1L;

    public Sondage() {}

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;

    private byte needLocation;

    //bi-directional many-to-one association to ResultatSondage
    @OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
    @OrderBy("sondage ASC")
    private List<ResultatSondage> resultatSondages;

    //bi-directional many-to-one association to SondageSection
    @OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
    private List<SondageSection> sondageSections;
}

And here's my DAO class:

@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    EntityManager em = PersistenceManager.getEntityManager();
    List<Sondage> allSondages = new ArrayList<>();
    try {
        em.getTransaction().begin();
        Query query = em.createQuery("SELECT s FROM Sondage s");
        allSondages = query.getResultList();
        em.getTransaction().commit();
    } catch (Exception ex) {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        allSondages = null;
    } finally {
        em.close();
    }
    return allSondages;
}

As you see, em is closed. In my JSP, I do this: I know this is not the good way of doing thing in the view side.

<body>
    <div class="header">
        <%@include file="../../../Includes/header.jsp" %>
    </div>
    <h2 style="color: green; text-align: center;">الاستمارات</h2>
    <div id="allsurveys" class="pure-menu custom-restricted-width">
        <%
            List<Sondage> allSondages = (List<Sondage>) request.getAttribute("sondages");

            for (int i = 0; i < allSondages.size(); i++) {
        %>
        <a  href="${pageContext.request.contextPath }/auth/dosurvey?id=<%= allSondages.get(i).getId()%>"><%= allSondages.get(i).getName()%></a> &nbsp;
        <%
            if (request.getSession().getAttribute("user") != null) {
                Utilisateur user = (Utilisateur) request.getSession().getAttribute("user");
                if (user.getType().equals("admin")) {
        %>
        <a href="${pageContext.request.contextPath }/aauth/editsurvey?id=<%= allSondages.get(i).getId()%>">تعديل</a>
        <%
                }
            }
        %>
        <br />
        <%
            }
        %>
    </div>
</body>

I'm guessing that every time I call user.getType(), a request is established ? If so, how can I prevent this?

For c4p0 config file, I included it in persistence.xml, I saw several posts saying that I need to put the c3p0 config file in c3p0-config.xml, but with my setup the c3p0 is initialized with the values I pass in the persistence.xml file, also the mysql connections are reaching 152 connections but the maxpoolsize is at 20, here's the persistence.xml file

<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="CAOE" transaction-type="RESOURCE_LOCAL">
        <class>com.caoe.Models.ChoixQuestion</class>
        <class>com.caoe.Models.Question</class>
        <class>com.caoe.Models.Reponse</class>
        <class>com.caoe.Models.ResultatSondage</class>
        <class>com.caoe.Models.Section</class>
        <class>com.caoe.Models.Sondage</class>
        <class>com.caoe.Models.SondageSection</class>
        <class>com.caoe.Models.SousQuestion</class>
        <class>com.caoe.Models.Utilisateur</class>
        <properties>
            <property name="hibernate.connection.provider_class"
                      value=" org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />

            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.password" value=""/>

            <property name="hibernate.connection.url"
                      value="jdbc:mysql://localhost:3306/caoe?useUnicode=yes&amp;characterEncoding=UTF-8"/>

            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.show_sql" value="true" />

            <property name="hibernate.c3p0.max_size" value="50" />
            <property name="hibernate.c3p0.min_size" value="3" />
            <property name="hibernate.c3p0.max_statements" value="20" />
            <property name="hibernate.c3p0.acquire_increment" value="1" />
            <property name="hibernate.c3p0.idle_test_period" value="30" />
            <property name="hibernate.c3p0.timeout" value="35" />
            <property name="hibernate.c3p0.checkoutTimeout" value="60000" />
            <property name="hibernate.connection.release_mode" value="after_statement" />

            <property name="debugUnreturnedConnectionStackTraces"
                      value="true" />
        </properties>
    </persistence-unit>
</persistence>

EDIT: I'm deploying the Application to a red hat server with Tomcat and MySQL Installed. I'm just wondering why Hibernate is opening too much connections to MySQL, with all entity managers closed no connection will remain open, but this is not the case. I'm guessing and correct me if I'm true that the connections are opened when I do something like this:

List<Sondage> allSondages = SondageDao.getAllSondages();

for (Sondage sondage : allSondages) {
    List<Question> questions = sondage.getQuestions();
    //code to display questions for example
}

Here when I use sondage.getQuestions(), does Hibernate open a connection to the database and doesn't close it after, am I missing something in the configuration file that close or return connection to pool when it's done with it. Thanks in advance for any help.

EDIT2 : Since people are asking for versions, here they are : JAVA jre 1.8.0_25 Apache Tomcat v7.0 hibernate-core-4.3.10 hibernate c3p0 4.3.10.final hibernate-jpa 2.1 Thanks in advance

The mysql version is Mysql 5.6.17 if that can help...

EDIT 4: as people are getting confused about witch version of the code I posted is buggy, let me edit this so you'll know what happens exactly:

First I'll start by showing what's the buggy code, as you guys don't care about what's working:

@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    EntityManager em = PersistenceManager.getEntityManager();
    List<Sondage> allSondages = new ArrayList<>();
    try {
       em.getTransaction().begin();
       Query query = em.createQuery("SELECT s FROM Sondage s");
       allSondages = query.getResultList();
       em.getTransaction().commit();
    } catch (Exception ex) {
    if (em.getTransaction().isActive()) {
        em.getTransaction().rollback();
    }
    allSondages = null;
    } finally {
        em.close();
    }
    return allSondages;
  }

So this is basically what I did for all my dao functions, I know transaction is not needed here, since I saw questions pointing that transactions are important for connection to close. beside this , I getEntityManager from PersistenceManager class that has an EntityManagerFactory singleton Object, so getEntityManager creates an entityManager from the EntityManagerFactory singleton Object:=> code is better than 1000 word : PesistenceManager.java:

import javax.persistence.EntityManager;
    import javax.persistence.EntityManagerFactory;
    import javax.persistence.Persistence;

    public class PersistenceManager 
    {
    private static EntityManagerFactory emf = null;

    public static EntityManager getEntityManager()
    {
        return getEntityManagerFactory().createEntityManager();     
    }

    public static EntityManagerFactory getEntityManagerFactory()
    {
            if(emf == null) {
                    emf = Persistence.createEntityManagerFactory("CAOE");
                    return emf;
        }
            else
                    return emf;
        }
}

Yes this is cool and all good, but where's the problem?

The problem here is that this version opens the connections and never close them, the em.close() have no effect, it keeps the connection open to the database.

The noob fix:

What I did to fix this issue is create an EntityManagerFactory for every request, it mean that the dao looks something like this:

    @SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    //this is the method that return the EntityManagerFactory Singleton Object
    EntityManagerFactory emf = PersistenceManager.getEntitManagerFactory();
    EntityManager em = emf.createEntityManager();
        List<Sondage> allSondages = new ArrayList<>();
        try {
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT s FROM Sondage s");
            allSondages = query.getResultList();
            em.getTransaction().commit();
    } catch (Exception ex) {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        allSondages = null;
        } finally {
        em.close();
        emf.close();
    }
    return allSondages;
}

Now this is bad and I'll just keep it while I don't have answer for this question (it seems like forver :D ). So with this code basically All connections gets closed after hibernate doesn't need them. Thanks in advance for any efforts you put in this question :)

Noticed you are specifying a release mode in your persistence.xml file. According to this doc: https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/transactions-connection-release.html doing so is generally a bad idea. Possibly removing that config line from your file will do it. This more by way of observation and SWAG than answer, so that's why I offer it is a comment and not an answer. — Matt Campbell, Sep 09 '15 at 20:30
Hi @MattCampbell, THANkS so much for answering , I wasn't doing this in the configuration file, however when I saw the property I tought this could free some connections to the pool, but this did not, so to reply to your guessing, it's not the problem. — Reda, Sep 09 '15 at 21:18
Where and how do you fetch `List` by calling `GetAllSondage()` in the target DAO? It is available as an HTTP request attribute on the target JSP. Is it already fetched in the included file `header.jsp` or somewhere else? — Tiny, Sep 12 '15 at 15:55
Which versions do you use for hibernate, JPA, tomcat and JAVA? Do you generate your classes with JPA? — Norbert, Sep 12 '15 at 16:11
@Tiny Yes i do this by calling GetAllSondage, and no it's not fetched in the header.jsp, header.jsp contains only the menu — Reda, Sep 12 '15 at 16:36
@NorbertvanNobelen I do generate classes with JPA with JPA Utils, the code in the question of the Entity sondage is just missing getters and setters, should I add something else to it ? — Reda, Sep 12 '15 at 16:36
Ok, sorry about the transaction remarks :). Yes, probably go back to the previous stable version (4.2?). Pool management on hibernate-release-4.3.6 (and later since you are using 4.3.10) seems to be broken. The OneToMany remarks from @emamedov do not matter: I have similar problems in simple selects. – — Norbert, Sep 13 '15 at 04:24
How did you determine that for every controller request, more than 7 connections are getting opened? Also, how did you determine that 152 connections get opened? — Bhashit Parikh, Sep 13 '15 at 05:53
@BhashitParikh I show processes list in mysql, simply enter SHOW processlist; in a mysql command and you get all opened connections to the database, when i do this it shows 152 connection, and after this, all request fail with too many connections exception. And something important to mention: all the 152 connections established are in sleep state — Reda, Sep 13 '15 at 11:29
Can you also share details of the PersistenceManager class please? — Filip, Sep 15 '15 at 08:33
For the PersistenceManager, all it does is return EntityManagerFactory object, and create an entitymanager from the already instanciated entitymanagerfactory — Reda, Sep 15 '15 at 08:56
Please show the code - I suspect (as does Filip?) that this class ight leak EntityManagerFactories — piet.t, Sep 15 '15 at 09:00
Can you specify mysql jdbc driver version and mysql server version? — sibnick, Sep 15 '15 at 18:12
Also can you add source of `PersistenceManager.getEntityManager()`? I suppose it is your class (not from library). Sometime people call `createEntityManagerFactory()` every time. Also you set `c3p0.max_size` to 50. It means that pool may contain up to 50 connections (you wrote 20 in question). — sibnick, Sep 15 '15 at 18:21
Could you provide a working minimal Gist@git so that we can reproduce your problem? — V G, Sep 16 '15 at 10:08
@AndreiI thanks for your comment, I updated the question, check edit4 :) — Reda, Sep 16 '15 at 20:24
@Reda I was asking for working code, so that we simply checkout it and reproduce it. Yuu could use https://gist.github.com/ for sharing your code. Also I have noticed that in `persistence.xml` value for the property `hibernate.connection.provider_class` has a space at the begin. Could you delete it and try again? — V G, Sep 17 '15 at 08:32
I have got a stable working solution for you (after a slight rebuild of one of my own applications). Still care to share? — Norbert, Oct 10 '15 at 15:46
I ended up creating and closing the EMF for each database query. it was slow as hell but worked, I'd suggest you use another version or another framework :D . The bounty answer is a bullshit answer.check out @NorbertvanNobelen comment use a stable version. best of luck — Reda, Apr 09 '18 at 17:40
Do you see c3p0 output logs? Verify it's actually using it :) Beyond that looks like a bug, update to the latest minor versions, then dive in to the dependency source code! :) — rogerdpack, May 18 '21 at 14:45
@rogerdpack Thank you that was 6 years ago :D and yes the problem was in the lib version — Reda, May 21 '21 at 18:36

score 12 · Answer 1 · answered Sep 17 '15 at 03:17

12

I think that Hibernate and C3P0 are behaving correctly here. In fact you should see that there are always at least three connections to the database open as per your C3P0 configuration.

When you execute a query Hibernate will use a connection from the pool and then return it when it is done. It will not close the connection. C3P0 might shrink the pool if the min size is exceeded and some of the connections time out.

In your final example you see the connections closed because you've shut down your entity manager factory and therefore your connection pool as well.

answered Sep 17 '15 at 03:17

Alex Barnes

7,174
1
30
50

2

Totally agree, C3P0 is maintaining the connections open even when em.close() is executed. Why? Simply, because it is its purpose – Javier Sánchez Sep 18 '15 at 08:32
Hi Alex, thank you for answering, I know C3P0 is maintaining the connection open to be used by who requests it after, but here, connections ARE NOT RELEASED TO THE POOL, read the title of my question – Reda Sep 19 '15 at 00:00
1

Hi again Alex, C3P0 is configured to open only 30 or something like this, when I monitor my Mysql server, 152 connections are opened. so it's not normal – Reda Sep 19 '15 at 14:13
This is defenetly not the answer to my question, so sytem sucks :/// – Reda Sep 22 '15 at 22:07

score 9 · Answer 2 · edited Dec 20 '17 at 14:10

9

You call Persistence.createEntityManagerFactory("CAOE") every time. It is wrong. Each call createEntityManagerFactory creates new (indepented) connection pool. You should cache EntityManagerFactory object somewhere.

EDIT:

Also you should manually shutdown EntityManagerFactory. You can do it in @WebListener:

@WebListener
public class AppInit implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {}

    public void contextDestroyed(ServletContextEvent sce) {
         PersistenceManager.closeEntityMangerFactory();
    }
}

Otherwise each case of redeploy is source of leaked connections.

edited Dec 20 '17 at 14:10

Stefan Endrullis

4,150
2
32
45

answered Sep 16 '15 at 03:57

sibnick

3,995
20
20

Hi sibnick, sorry but this code is the one I wrote to fix this issue, I basically instanciate entity Manager Factory for each request, and this fixes the problem, in my original code, emf is a singleton, getEntityManagerFactory() will check if it this.emf is null and create a new one, if it's not null it will return this.emf, i'll update the question, sorry about that – Reda Sep 16 '15 at 08:06
1

You do not store new object to emf variable in method `getEntityManagerFactory` . Also this code is not multithread safe. – sibnick Sep 16 '15 at 08:17
Can you confirm that problem is still exist(after fixing getEntityManagerFactory)? – sibnick Sep 16 '15 at 10:24
getEntityManagerFactory is correct, why I copy pasted when I edited the question, is the new version where I create and instance of getEntityManagerFactory() everytime I perform a request, this fixed the problem, (no more sleeping connection not released to mysql), but as you said this is wrong. So if I do what is in the current EDIT, and use an entityManager (not EntityManagerFactory) to perform getSondage or whatever, the connection will not close even if i do em.close() like shown in the question. – Reda Sep 16 '15 at 14:21
Now I can see correct code in your post (except multithread singleton issue - all JSP/Servlet applications are multithread in real life). Only one thing is not clear for me. Where do you call `closeEntityMangerFactory`? You should call it from application listener. Otherwise each case of redeploy is source of leaked connections. – sibnick Sep 16 '15 at 14:41

Norbert · Answer 3 · 2015-09-13T04:30:52.000

4

Can you try the following:

<property name="hibernate.connection.release_mode" value="after_transaction" />
<property name="hibernate.current_session_context_class" value="jta" />

instead of your current release mode?

edited Sep 13 '15 at 04:30

answered Sep 13 '15 at 04:24

Norbert

6,026
3
17
40

Hi again Norbert, I tried this one, even if in the docs, they point to it as a bad habbit, but nothing. – Reda Sep 13 '15 at 11:31

score 3 · Answer 4 · edited Nov 04 '19 at 07:29

Since sibnick has already answered the technical questions I'll try to address some points you seem to be confused about. So let me give you some ideas on how a hibernate application and connection-pool is intended to work:

Opening a database connection is an "expensive" operation. In order to avoid having to pay that cost for each and every request, you use a connection-pool. The pool opens a certain number of connections to the database in advance and when you need one you can borrow one of those existing connections. At the end of the transaction, these connections will not be closed but returned to the pool so they can be borrowed by the next request. Under heavy load, there might be too few connections to serve all requests so the pool might open additional connections that might be closed later on but not at once.
Creating an EntityManagerFactory is even more expensive (it will create caches, open a new connection-pool, etc.), so, by all means, avoid doing it for every request. Your response-times will become incredibly slow. Also creating too many EntityManagerFactories might exhaust your PermGen-space. So only create one EntityManagerFactory per application/persistence-context, create it at application startup (otherwise the first request will take too long) and close it upon application shutdown.

Bottom line: When using a connection-pool you should expect a certain number of DB-connections to remain open for the lifetime of your application. What must not happen is that the number increases with every request. If you insist on having the connections closed at the end of the session don't use a pool and be prepared to pay the price.

thank you piet.t, but I already know all of this, the connections that are opened are not what the connection pool needs. proof 152 opened connections, and my server configuration only supports 151 — Reda, Sep 17 '15 at 08:31

Willem van Rooyen · Answer 5 · 2017-02-03T07:32:18.507

I ran into the same problem and was able to fix it by creating a singleton wrapper class for the EntityManagerFactory and creating the EntityManager where it's needed. You're having the connection overload problem because you're wrapping the EntityManager creation in the singleton class, which is wrong. The EntityManager provides the transaction scope (should not be re-used), the EntityManagerFactory provides the connections (should be re-used).

from: https://cloud.google.com/appengine/docs/java/datastore/jpa/overview

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public final class EMF {
    private static final EntityManagerFactory emfInstance =
        Persistence.createEntityManagerFactory("CAOE");

private EMF() {}

public static EntityManagerFactory get() {
    return emfInstance;
    }
}

and then use the factory instance to create an EntityManager for each request.

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import EMF;

// ...
EntityManager em = EMF.get().createEntityManager();

score 0 · Answer 6 · answered Jul 24 '18 at 05:53

In my application property i have some datasource related parameter. Those are given bellow:

# DataSource Parameter
minPoolSize:5
maxPoolSize:100
maxIdleTime:5
maxStatements:1000
maxStatementsPerConnection:100
maxIdleTimeExcessConnections:10000

Here, **maxIdleTime** value is the main culprit. It takes value in second. Here maxIdleTime=5 means after 5 seconds if connection is not using then it will release the connection and it will take the minPoolSize:5 connection. Here maxPoolSize:100 means it will take maximum 100 connection at a time.

In my DataSource Configuration class i have a bean. Here is the example code:

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.core.env.Environment;
import org.springframework.beans.factory.annotation.Autowired;

@Autowired
    private Environment env;

 @Bean
    public ComboPooledDataSource dataSource(){
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        try {
            dataSource.setDriverClass(env.getProperty("db.driver"));
            dataSource.setJdbcUrl(env.getProperty("db.url"));
            dataSource.setUser(env.getProperty("db.username"));
            dataSource.setPassword(env.getProperty("db.password"));
            dataSource.setMinPoolSize(Integer.parseInt(env.getProperty("minPoolSize")));
            dataSource.setMaxPoolSize(Integer.parseInt(env.getProperty("maxPoolSize")));
            dataSource.setMaxIdleTime(Integer.parseInt(env.getProperty("maxIdleTime")));
            dataSource.setMaxStatements(Integer.parseInt(env.getProperty("maxStatements")));
            dataSource.setMaxStatementsPerConnection(Integer.parseInt(env.getProperty("maxStatementsPerConnection")));
            dataSource.setMaxIdleTimeExcessConnections(10000);

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

Hope this will solve your problem :)

score -4 · Answer 7 · answered Sep 12 '15 at 17:52

-4

Looks like issue is related to Hibernate bug. Please try to specify fetch strategy EAGER in your OneToMany annotations.

@OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL, fetch = FetchType.EAGER)

answered Sep 12 '15 at 17:52

emamedov

96
1
2

Hi emamedov, I need the FetchType to be LAZY because all my entities are relying on it, when i add a survey that have many sections that have many questions, i only do SurveyDAO.addSurvey , and all the questions and sections that are not in the database gets added automatically with only one DAO call, so changing the FetchType to eager for me is remaking the application – Reda Sep 12 '15 at 20:59
1

Hi Reda. FetchType shouldn't affect insert into database. You still can use one method call and hibernate will add whole object graph by itself. But I agree that LAZY type can be preferable. I proposed solution just to check that it is Hibernate bug. In case of yes we will think about usage EAGER type or found workaround for LAZY type – emamedov Sep 13 '15 at 08:39

Hibernate not releasing connections from connection pool

7 Answers7