[C3p0-users] Connections not acquired for waiting threads

[John K. <jkr...@ne...>]

C3P0 acquires only one connection, I find, when several threads request
connections concurrently.  This is a problem: I want the threads to
proceed as fast as possible with a limited number of connections (that
is, the maxPoolSize).

Have I blundered?  Set the wrong configuration properties?  Written my
application wrong?

I enclose some output from a test program, also enclosed.  I'm using
C3P0 version 0.9.1-pre9.

- John Kristian

12:11:27.442 INFO   log.MLog: MLog clients using java 1.4+ standard
logging.
12:11:27.786 INFO   c3p0.C3P0Registry: Initializing c3p0-0.9.1-pre9
[built 23-September-2006 01:47:24; debug? true; trace: 10]
12:11:29.083 INFO   impl.AbstractPoolBackedDataSource: Initializing c3p0
pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement ->
2, acquireRetryAttempts -> 1, acquireRetryDelay -> 5000,
autoCommitOnClose -> false, automaticTestTable -> null,
breakAfterAcquireFailure -> true, checkoutTimeout -> 0,
connectionCustomizerClassName -> null, connectionTesterClassName ->
com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName ->
1256ea2, debugUnreturnedConnectionStackTraces -> false, description ->
null, driverClass -> oracle.jdbc.OracleDriver, factoryClassLocation ->
null, forceIgnoreUnresolvedTransactions -> false, identityToken ->
1256ea2, idleConnectionTestPeriod -> 0, initialPoolSize -> 0, jdbcUrl ->
jdbc:oracle:thin:@stdev.netflix.com:1521:STDEV,
maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime ->
120, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 6, maxStatements
-> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1,
numHelperThreads -> 3, preferredTestQuery -> null, properties ->
{user=3D******, password=3D******}, propertyCycle -> 0,
testConnectionOnCheckin -> false, testConnectionOnCheckout -> false,
unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies ->
false ]
12:11:35.224 INFO   TestC3P0.User: 1 busy connections
12:11:40.318 INFO   TestC3P0.User: 1 busy connections
12:11:45.365 INFO   TestC3P0.User: 1 busy connections
12:11:50.381 INFO   TestC3P0.User: 1 busy connections
12:11:55.412 INFO   TestC3P0.User: 1 busy connections
12:12:00.428 INFO   TestC3P0.User: 1 busy connections

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TestC3P0
{
    /** Exercise C3P0 with concurrent threads. */
    public static void main(String[] args) throws Exception
    {
        final ComboPooledDataSource pool =3D new =
ComboPooledDataSource();
        if (args.length > 0)
            pool.setDriverClass(args[0]);
        if (args.length > 1)
            pool.setJdbcUrl(args[1]);
        if (args.length > 2)
            pool.setUser(args[2]);
        if (args.length > 3)
            pool.setPassword(args[3]);

        pool.setInitialPoolSize(0);
        pool.setMinPoolSize(1);
        pool.setAcquireIncrement(2);
        pool.setMaxPoolSize(THREADS);
        pool.setMaxIdleTime(2 * MINUTES);
        pool.setAcquireRetryAttempts(1);
        pool.setAcquireRetryDelay(5 * SECONDS);
        pool.setBreakAfterAcquireFailure(true);

        final long startTime =3D System.currentTimeMillis() + 1000;
        for (int i =3D 1; i <=3D THREADS; ++i) {
            (new Thread(new User(i, startTime, pool))).start();
        }
    }

    /** seconds per minute */
    protected static final int MINUTES =3D 60;

    /** msec per second */
    protected static final int SECONDS =3D 1000;

    /** Number of concurrent users. */
    public static final int THREADS =3D 6;

    /**
     * Interval between usage requests [msec]. This prevents
simultaneous
     * requests, which might expose mere synchronization bugs. For more
rigorous
     * testing, set this to 0 and run this test on a multiprocessor.
     */
    public static final int STAGGER =3D SECONDS / 10;

    /** How long a connection is used [msec]. */
    public static final int USAGE =3D 5 * SECONDS;

    public static final class User implements Runnable
    {
        protected final Logger log =3D
Logger.getLogger(getClass().getName().replace('$', '.'));

        public User(int id, long startTime, ComboPooledDataSource
dataSource)
        {
            this.id =3D id;
            this.startTime =3D startTime;
            this.dataSource =3D dataSource;
        }

        protected int id;

        protected long startTime;

        protected ComboPooledDataSource dataSource;

        public void run()
        {
            try {
                // Staggered start:
                Thread.sleep(startTime - System.currentTimeMillis() +
(id * STAGGER));
                log.fine(id + " getting connection");
                Connection connection =3D dataSource.getConnection();
                try {
                    final long deadline =3D System.currentTimeMillis() +
USAGE;
                    log.fine(id + " got connection");
                    connection.createStatement().executeQuery("select 1
from DUAL");
                    log.finer(id + " executed query");
                    Thread.sleep(deadline - System.currentTimeMillis());
                } finally {
                    log.info(dataSource.getNumBusyConnections() + " busy
connections");
                    log.fine(id + " closing connection");
                    connection.close();
                }
            } catch (Exception e) {
                log.log(Level.WARNING, null, e);
            }
        }
    }

}