[FIXED] Why DBCP getNumIdle always return 0? When does active connection become idle?

Issue

I'm using DBCP2 BasicDataSource to manage database connections. When I test I use getNumActive() and getNumIdle() to print out the pool status. I end up active connections keep on increasing while idle connection always 0.

Here is my code:

    public static Connection getCnn() throws Exception {
    Connection cnn = null;
    if (ds.isClosed()){
        init();
        logger.warn("DataSource is closed. Rebuild BasicDataSource from getCnn");
    }
    logger.trace("Request for connection");
    cnn = ds.getConnection();
    // Monitor current connecton pool 
    // change to logger.trace in future for performance.
    logger.warn("Current Connection Pool: " 
        + "\n MaxTotal of connection - " + String.valueOf(maxTotal)
        + "\n Number of active connection - " + String.valueOf(ds.getNumActive())
        + "\n Number of idle connecton - " + String.valueOf(ds.getNumIdle()));
    return cnn;

My BasicDataSource configurations are

   MinIdle = "8"
   MaxIdle = "16"
   MaxTotal = "-1"
   maxOpenPreparedStatements = "256"
   RemoveAbandonedTimeout = "300"
   RemoveAbandonedOnBorrow = "true"

And the output is idle connection number is always 0, and active connection keep on increasing no matter how long I wait for a connection to become idle.

So in what condition a connection will become "idle"? I cannot find any timeout setup to force connection to idle.

Any insights will be much appreciated!

Solution

I am not sure if you are closing the connection object. Once u close your connection object the active connection will reduce and idle connections will increase.

When you do "ds.getConnection()", a connection (object) is created or an already created connection (object) which is closed is retrived and then returned. The recieved connection object is considered as active till close is called. Closed connection objects which are ready to be reused are idle connections.

Answered By - Adhil Roshan

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)