Java ComboPooledDataSource exceeds pool size and does not reuse

Question

I have the following code that gets called by an other application (That I can not change) to read form a Database.
The method is called in a loop very often and DOSes the DB.
At The DB I can see that there are many connecetions opend ... increasing to some hundred ... and than the DB crashed due to the load.

// Called in a loop
private <T> T execute(String query, PostProcessor<T> postProc, PreProcessor preProcs) throws OperationFailedException {
    try (Connection conn 
            = Objects.requireNonNull(dataSourceRef.get(), "No Connection").getConnection();
         PreparedStatement smt = conn.prepareStatement(query)) {
        preProc.process(smt);
        return postProc.process(smt.executeQuery());
    } catch (SQLException e) {
        throw new OperationFailedException(e.getMessage(), e);
    }
}

the date source gets initialised before ...

// class variable
// AtomicReference<PooledDataSource> dataSourceRef = new AtomicReference<PooledDataSource>();

// Init method
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass(config.getConnectionDriver());
cpds.setJdbcUrl(config.getConnectionString());
cpds.setUser(config.getConnectionUser());
cpds.setPassword(config.getConnectionPassword());
// cpds.setMaxPoolSize(10);
dataSourceRef.getAndSet(cpds);

My question why is this happening.
I thought due to the pooling not for each query a new connections should be used. As well by setting the max pool size this is not working.

As well I tried it with try-catch-finally construct and closing the stm and the conn after use. (As I've read somewhere that finally might get called delayed under high load scenarios ... I thouht that might be the case)

But still why is the pool size exceeded? How can I limit that and block the method untill a connection is avalible again before continuing?

@ScaryWombat: Isn't that done by the try() syntax implicitly? — PaulEdison, Mar 14 '19 at 19:24
Paul, you are right, I thought only the connection was in the try - I should read twice before posting. — Scary Wombat, Mar 15 '19 at 00:25

Md. Sajedul Karim · Answer 1 · 2019-03-14T05:37:31.140

During connection polling in c3p0 you have to consider some options. Those are given bellow application.property file:

db.driver: oracle.jdbc.driver.OracleDriver // for Oracle
db.username: YOUR_USER_NAME
db.password: YOUR_USER_PASSWORD
db.url: DATABASE_URL
minPoolSize:5 // number of minimum poolSize
maxPoolSize:100 // number of maximum poolSize
maxIdleTime:5 // In seconds. After that time it will realease the unused connection.
maxStatements:1000
maxStatementsPerConnection:100
maxIdleTimeExcessConnections:10000

Here, maxIdleTime is the main points. It defines how many seconds this will release unused connection. It is in second.

Another is minPoolSize . It defines how many connection it will hold during idle mode.

Another is maxPoolSize . It defines how many maximum connection it will hold during loaded mode.

Now, how you configure ComboPooledDataSource? Here is the code:

@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;
    }

For details implementation, please check this thread . Here i added practical implementation

Edit (Getting connection)

You can get connection using bellow way:

Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> doSomeStuffWith(connection));

How you get EntityManager?

@PersistenceContext
private EntityManager entityManager;

Hope this will help you.

Thanks :)

Java ComboPooledDataSource exceeds pool size and does not reuse

1 Answers1