connection pool stalling...

[Chris Montgomery]

I've used this connection pool for a few years now, but only recently has it been put to the test. Recently, a few bad queries were written to fuel some reports causing the system to come to a hault if run during a busy time. We found that once the queries were done running, the database became healthy again, but the connection pool wouldn't begin to deliver connection threads again.

Yes, the queries are gone, but I'm actually glad to have experienced this, because it exposed an issue with the connection pool.

Why won�t the connection pool �start up� again?

It's almost like there isn't a timeout and once the database goes down, all the threads get used up and they don't let go ever...

Any ideas?

Thanks!



Constructor

    
 
public DBCP( ) {
 // Load the Database SQL Driver
 try{
 Class.forName(JDBC_DRIVER);
 } 
 catch (java.lang.ClassNotFoundException e) {
 System.out.println ("Database SQL Driver not found...");
 }
 
 try {
 connectionPool = new GenericObjectPool(null);
 ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(JDBC_CONNECTION_STRING,null);
 PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
 dataSource = new PoolingDataSource(connectionPool);
 
 } 
 catch (Exception e){
 //add catch here
 }
 }
 

GetConnection Code

  
 
public static Connection getConnection() throws Exception{
 try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new SQLException("SQL Connection Error: " + e.toString());
}
 }
 

[Scott Selikoff]

I'm going to go to out on a limb and say your database needs some performance tuning for proper indexes. On a poorly tuned database, even the most simple queries could stall the entire system.

[Chris Montgomery]

Even if the database was terribly constructed, could that really be the reason why the connection pool doesn't allow you to connect even when the database is no longer over burdened?