DBCP could not obtain an idle db connection, pool exhausted

[Justin Howard]

Hi All,
I am using Hibernate.
I am getting this exception in the logs and sometime it does not connect to the database.
Is there any free tool or software I could use to debug unclosed connections.
From the code I cannot find any unclosed connection. All the conections are colsed in the finally block.
How can I debug this?
This is in my hibernate.cfg.xml

    
<hibernate-configuration>
        <session-factory>
                <!-- Database connection settings -->
                <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
  				<property name="connection.datasource">java:comp/env/jdbc/dsnFreelancerdb</property>
				<property name="connection.pool_size">1</property>
                <!-- Echo all sql to sdtout -->
                <property name="show_sql">false</property>

               <!-- List all the Java classes we have mapped here -->
				<mapping resource="com/aexp/freelancerdb/emp/Freelancer.hbm.xml"/>
				<mapping resource="com/aexp/freelancerdb/emp/Position.hbm.xml"/>
				<mapping resource="com/aexp/freelancerdb/emp/Extension.hbm.xml"/>		
				<mapping resource="com/aexp/freelancerdb/emp/Mail.hbm.xml"/>
				<mapping resource="com/aexp/freelancerdb/emp/Attachment.hbm.xml"/>
				<mapping resource="com/aexp/freelancerdb/emp/FreelancerDetails.hbm.xml"/>
		   </session-factory>
</hibernate-configuration>

This is in the context.xml
<Context debug="5" reloadable="true">
<Logger className="org.apache.catalina.logger.FileLogger" prefix="jndi_ds_log" suffix=".txt" timestamp="true"/>
<Resource
name="jdbc/dsnFreelancerdb"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="db url"
username="user"
password="pass"
maxActive="100"
maxIdle="30"
maxWait="10000"
removeAbandoned="true" />
</Context>
Thanks

[Bosun Bello]

What Happens if you bump up the pool size.

[Justin Howard]

Thanks for the reply.

When I increase the pool size to 200 it works fine.
But this is only QA. Around 3 people testing at a time.
In production I dont know how many people will hit the site at the same time.

Thanks

[Bosun Bello]

I know you said you did already. However, I will suggest you check closely for connection leaks?

[Justin Howard]

Is there a tool to find the exact statement causing the problem.

I see this sometimes in the logs, but when I look at the code it has the closing portion in the finally block.

 
running Session.finalize()
29 Oct 2009 13:53:27,144 DEBUG JDBCContext:247 - running Session.finalize()
29 Oct 2009 13:53:27,144  WARN JDBCContext:259 - unclosed connection, forgot to call close() on your session?  

Is it a problem with the this code?

    
public class HibernateUtil {
        private static final SessionFactory sessionFactory;
        
        static {
                try {
                        // Create the SessionFactory from hibernate.cfg.xml
                        sessionFactory = new Configuration().configure().buildSessionFactory();
                } catch (Throwable ex) {
                        // Make sure you log the exception, as it might be swallowed
                        throw new ExceptionInInitializerError(ex);
                }
        }

        public static SessionFactory getSessionFactory() {
                return sessionFactory;
        }
}

   
public class FreelancerDAO {
	
	
	private Session hibernateSession;
    
    public FreelancerDAO() {
        super();
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
        hibernateSession  = session;
    }
public void closeSession(){
    	hibernateSession.close();
    }
}

[Paul Clapham]

You'll need something that closes the Hibernate session. I see a method which would close the session if you called it, but I don't see any code which actually calls that method.

[Justin Howard]

Thanks for the reply.
I use it like this in the servlet or any other code

    
public void doPost(HttpServletRequest request, HttpServletResponse response){
    
try{	    
            FreelancerDAO contractorDAO = new FreelancerDAO();
            ...some logic here
     }
catch(Exception e){
}
finally{
            contractorDAO.closeSession();
}

Also I changed the hibernate.cg.xml to this, cause I read in the documentation that hibernates connection pooling is rudimentary.

    
<hibernate-configuration>
        <session-factory>
                <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
  		<property name="connection.datasource">java:comp/env/jdbc/dsnFreelancerdb</property>
  		<property name="hibernate.c3p0.min_size">5</property>
                <property name="hibernate.c3p0.max_size">200</property>
                <property name="hibernate.c3p0.timeout">1800</property>
                <property name="hibernate.c3p0.max_statements">50</property>
  		 <property name="show_sql">false</property>
               	<mapping resource="com/aexp/freelancerdb/emp/Freelancer.hbm.xml"/>
        </session-factory>
</hibernate-configuration>

Does anyone have any idea what is the max size of hibernate.c3p0.max_size.

Thanks

[Bosun Bello]

Looks like your code is closing the Hibernate session correctly.

If you are using hibernate 2, this has to be 0 <property name="hibernate.c3p0.min_size">0</property>

[Justin Howard]

Thanks,

what about the max size?