Database Connection Pool Not Releasing Connection to pool

[Joe Urbanek]

I have a Class which performs JDBC calls on a DB2 Database

The Database connections are pooled via the datasource,
There is a method called getdbconnection() which obtains a pooled connection.

The issue is that the method to obtain a connection and the method to release a connection are synchronized methods on the same object (DBConnectionPool). Therefore, at any given time, only one of those methods can be called.

When the data source connection pool runs out of connections calls to DBConnectionPool.getPooledConnection() block waiting for a connection to be returned. Calls to DBConnectionPool.releasePooledConnections() then have to wait for the calls to DBConnectionPool.getPooledConnection to unblock.

Because the calls to return a connection are waiting for the calls to get a connection no connections will ever be returned and therefore no connections will ever be given causing the dead lock.

Your assistance is appreciated.

Here is example of the code:

 
public class DBConnectionPool{
    // Constants:
    protected final static int MAX_POOL_ENTRIES = 512;
 
    // Class variables:

    // The 'pools' variable is initially an empty array, but it is
    //   redefined in
    // the 'initializeAllPools()' method.
    private static DBConnectionPool[]pools = new DBConnectionPool[0];

    /**
     *  Get a DBConnection object from one of the (up to) five databases; 
     */
     public synchronized DBConnection getPooledConnection(int poolIndex) 
        throws Exception {
    if ((poolIndex < 0) || (poolIndex > pools.length)) {
        throw new Exception("Invalid pool index specified; "
                   + poolIndex);
    }
    if (pools[poolIndex].isUnavailable()) {
        throw new Exception("This DBConnection pool is not 
                    available; " + poolIndex);
    }
    pools[poolIndex].connectionsGiven++;

return pools[poolIndex].getPooledDataSourceConnection();
     }
 
    private DatasourceDBConnection getPooledDataSourceConnection() 
        throws Exception {
    
    try {Connection dbconn = dataSource.getConnection(dbId, dbPw), 
             databaseNumber);
    
            dbconn.getConn().setAutoCommit(isAutoCommitPerPropertiesFile());
    
        log(WARN, getLoggingPrefix() + 
            "getPooledDataSourceConnection(); id=" + 
            dbconn.getConn().hashCode() + 
           " ac=" + dbconn.getConn().getAutoCommit());
    
           return dbconn;
        }catch (Exception ex) {
    String msg = "Unable to obtain a Connection from the 
                DataSource; " 
                + ex.getMessage(); 
    log(ERROR, getLoggingPrefix() + msg);
        }
          }
    
      private void releasePooledDataSourceConnection(
          DatasourceDBConnection connection) {

          try {
              if (connection.getConn().isClosed()) {
                 log(WARN, getLoggingPrefix() 
                 + "releasePooledDataSourceConnection(); 
                 + conn already closed.  id=" 
                 + connection.getConn().hashCode());
    }else {
                    connection.getConn().close();
        log(WARN, getLoggingPrefix() 
                    + "releasePooledDataSourceConnection();" 
                    + "conn closed.  id=" +
        connection.getConn().hashCode());
    }
    }
    catch (Exception ex) {
    log(FATAL, getLoggingPrefix() 
                + "Unable to close() a Connection from the DataSource; "
                + ex.getMessage());
    }
    }
 
}
 
   

[Tim LeMaster]

You need to look into wait() and notifyAll(). You should use wait() in the getting the connection if no connections are available. This will release the lock and block until another thread with the lock calls notifyAll().

Your synchronized method gets a lock on the instance of the DBConnectionPool that the thread is using to invoke the method. However inside of the method you are accessing a static array of DBConnectionPools - so the code could be trying to allocate a connection from a pool you do not have a lock on. Of course this doesn't seem to be the whole class so things could be different in the real code. Which is a reason to post the entire class.

[David O'Meara]

It looks like you're implementing your own ConnectionPool and you're obtaining Connections from a DataSource, where alternately you could register a Connection Pool like DBCP as your DataSource and other problems get managed by mature code.

I'm annoyed by Connection Pools. We have one written in house and it has never worked properly. My local dev environment is running DBCP with the old pool front end acting a facade to the DBCP DataSource. I look forward to kicking the old one out entirely.

[Joe Urbanek]

Thank you for your help your answers confirmed what I was thinking, I just needed a few more opinions, I will look into wait() and notifyAll() methods and into DBCP.