c3p0 doesn't acquire a connection, but there are idle connections

Question

My JAVA application processes a number of requests at a time using multithreading. So, different requests are processed at the same time with different threads.

I'm accessing my Oracle database with hibernate and C3P0, using the following hibernate.properties:

hibernate.bytecode.use_reflection_optimizer=false
hibernate.connection.driver_class=oracle.jdbc.OracleDriver
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.search.autoregister_listeners=false

hibernate.connection.url=${jdbc.url}
hibernate.default_schema=${jdbc.schema}
hibernate.connection.username=${jdbc.username}
hibernate.connection.password=${jdbc.password}

hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=10
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50

And c3p0.properties:

c3p0.preferredTestQuery=SELECT 1 from dual
c3p0.testConnectionOnCheckin=true
c3p0.idleConnectionTestPeriod=10
c3p0.driverClass=oracle.jdbc.driver.OracleDriver

(I've also tested it with testConnectionOnCheckout instead of testConnectionOnCheckin).

My java code does the following:

    Session session = sessionFactory.openSession();
    try{                
        session.beginTransaction();

        Log.debug(localizator + "Start");
        processCounters(id, user, session);
        Log.debug(localizator + "Stop");
        session.getTransaction().commit();

    } finally{
        session.close();
    }

When I run this it prints "Start" for every thread, but gets "locked" in one persist to the database, and no "Stop" is printed.

If I watch the opened sessions at database, there're 10 opened sessions (the max number configured at c3p0), but all of them are idle. Is there a way to make c3p0 releases some idle connections so, at least, one of the thread ends its process (appart from increasing the number of max connections)?

Answer 1

Changing the connection pool to BoneCP has solved the issue. I've used the following configuration (which I think it could be tuned to improve even more the speed of the process):

bonecp.idleMaxAge=240
bonecp.idleConnectionTestPeriod=60
bonecp.partitionCount=3
bonecp.acquireIncrement=1
bonecp.maxConnectionsPerPartition=5
bonecp.minConnectionsPerPartition=2
bonecp.statementsCacheSize=50
bonecp.releaseHelperThreads=3

Now it doesn't hang and, what is better, it has increased the performance of the queries.