Oracle Session is Blocking transactions

kolobear · **Joined:** Mon May 10, 2010 2:00 pm **Posts:** 22

I am getting the generic exception: org.hibernate.exception.GenericJDBCException: Cannot open connection

Typically when this happens, I go to the database guys and they tell me that there is an Oracle session that is open and hasn't completed and that there are other sessions under it that cannot complete their transactions because it is blocking them.

Any thoughts as to why I keep receiving this error?

Here is an example of my code:

Code:

public static Project_Account merge(Project_Account project_account) {
        Session session = null;
        Transaction trans = null;
        try {
            session = HibernateUtil.getSession();
            trans = session.beginTransaction();
            project_account = (Project_Account) session.merge(project_account);
            trans.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (!(trans.wasCommitted())) {
               trans.commit();
            }
            if (session.isOpen()) {
                session.flush();
                session.close();
            }
        }
        return project_account;
    }

And here is my hibernate.cgf.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>

    <!-- Hibernate Settings -->
        <!--property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property-->
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle9iDialect</property>
        <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@10.1.1.1:1521:cyclepops</property>
        <property name="hibernate.default_schema">cyclesticks</property>
        <property name="hibernate.connection.username">popman</property>
        <property name="hibernate.connection.password">curiousGe0rg3</property>
        <property name="hibernate.jdbc.batch_size">20</property>
        <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.show_sql">true</property>
    <!-- /Hibernate Settings -->

    <!-- C3P0 Settings -->
    <!-- ** Information on C3P0 can be found at http://www.mchange.com/projects/c3p0/index.html ** -->
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.max_size">100</property>
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds -->
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.min_size">10</property>
        <property name="hibernate.c3p0.timeout">1000</property> <!-- seconds -->
        <!--property name="hibernate.show_sql">true</property--> <!-- /show hibernate sql in tomcat log ;; use for debugging -->
    <!-- /C3P0 Settings -->

    </session-factory>
</hibernate-configuration>

I also am using a c3p0 properties file:

Code:

# http://www.mchange.com/projects/c3p0/index.html#maxAdministrativeTaskTime
c3p0.maxAdministrativeTaskTime = 0

# http://www.mchange.com/projects/c3p0/index.html#numHelperThreads
c3p0.numHelperThreads = 10

# included to assist with preventing memory leaks
#com.mchange.v2.c3p0.management.ManagementCoordinator=com.mchange.v2.c3p0.management.NullManagementCoordinator

# General Settings
c3p0.idleConnectionTestPeriod = 100
c3p0.acquireIncrement = 1
c3p0.maxStatements = 0
c3p0.maxPoolSize = 100
c3p0.minPoolSize = 0
c3p0.maxStatements = 0
c3p0.checkoutTimeout = 1000

sanne.grinovero · **Posted:** Fri Jul 02, 2010 8:24 pm

Quote:

100 is a lot, maybe it's higher that what your Oracle settings will permit? so c3p0 will be asking for more connections, but they won't be returned.