connection pooling issue in production environment -

[Vikrama Sanjeeva]

Hi all,

Our application servers are expecting to receive 800 concurrent request and for each request we have to fetch data from oracle db.

Oracle DB is configured such that it can give application server maximum of 40 connections from its connection pool

My question is how we can handle 800 concurrent request with available connections (40) from pool ?

Right now, our JDBC code gets connection in every method and closes it in same method in any case (closing connection in finally block - implemented proper connection closing pattern)

Please guide for this scenario.

Bye,
Viki.

[Jan Cumps]

You will be able to handle 40 concurrent requests (if there are no other limiting pieces in your application and architecture). The other requests will wait until connections are returned to the pool.
Did you get a requirement of how soon the 800 concurrent requests have to be finished?

[Vikrama Sanjeeva]

Jan Cumps wrote:You will be able to handle 40 concurrent requests (if there are no other limiting pieces in your application and architecture). The other requests will wait until connections are returned to the pool.
Did you get a requirement of how soon the 800 concurrent requests have to be finished?

The other requests will wait until connections are returned to the pool.

I want to know how request will wait ? Means
1: user has submitted request
2: application received request, but no connection available

Now how long user/request will wait ? Where we can set the waiting time ? In my case, I am getting ReadTimeOut error when no connections available.

Did you get a requirement of how soon the 800 concurrent requests have to be finished?
This is good idea. I will try to find average time spend on each request.


Bye,
Viki.

[Jan Cumps]

Vikrama Sanjeeva wrote:... I want to know how request will wait ? Means
1: user has submitted request
2: application received request, but no connection available

Now how long user/request will wait ? Where we can set the waiting time ? ....

Depends on the connection pool library you are using. For the Apache Commons DBCP, this is set by the maxWait parameter.

Vikrama Sanjeeva wrote:... In my case, I am getting ReadTimeOut error when no connections available....

Can you give us the details on that error?

[Vikrama Sanjeeva]

Jan Cumps wrote:Depends on the connection pool library you are using. For the Apache Commons DBCP, this is set by the maxWait parameter.

We are not using any specific library at application level for connection pooling. We are using JDBC and doing Lookup on database which is maintaining a pool of connections.

connection =
((DataSource)new InitialContext().lookup(Constants.DB_DS_JNDI_NAME)).getConnection();

Jan Cumps wrote:Can you give us the details on that error?

Ok, Its my mistake. Actually for testing purpose we made 2 connections available in db connection pool and simulated 3 simultaneous request. 3rd request got error: SEVERE: java.sql.SQLException: Attempt to use a closed handle : 'oracle_jdbc_driver_LogicalConnection_Proxy@aaa205'.

[Jan Cumps]

Vikrama Sanjeeva wrote:...We are not using any specific library at application level for connection pooling. We are using JDBC and doing Lookup on database which is maintaining a pool of connections.

connection =
((DataSource)new InitialContext().lookup(Constants.DB_DS_JNDI_NAME)).getConnection();

That lookup you do is to find a database connection pool that is configured somewhere (in your application sever maybe?) .

Vikrama Sanjeeva wrote:...3rd request got error: SEVERE: java.sql.SQLException: Attempt to use a closed handle : 'oracle_jdbc_driver_LogicalConnection_Proxy@aaa205'. ...

Can you show us the outline of how you get the connection, what you do with it, and how you return it to the pool?

[Vikrama Sanjeeva]

Jan Cumps wrote:That lookup you do is to find a database connection pool that is configured somewhere (in your application sever maybe?)

We are using oracle application server and I see there is a place where timeout settings can be set. Please see snapshot.

Jan Cumps wrote:Can you show us the outline of how you get the connection, what you do with it, and how you return it to the pool?

Ok I am mentioning here code snippets from our different classes. It not exact copy paste so ignore compile time errors:

// every request will call this method
public Clob getDetails((final MyObj obj ) throws SQLException {
        Connection connection = null;
        CallableStatement cs = null;
//getting connection 
connection = DbProcessor.getInstance().getManagedDBConnection(); // this will get connection from pool. see below for exact LOC


cs = DbProcessor.getInstance().getCallableStmt(call, connection);
                    cs.registerOutParameter(1, java.sql.Types.CLOB);
                    cs.setString(2, obj.getNo());
... 
....
cs.execute();
c = cs.getClob(1);

finally {
            JDBCCloser.close(cs); // see at the end these two methods for cs and connection
            JDBCCloser.close(connection); // this will return connection to pool
        }


} // method end


public static DbProcessor getInstance() throws ClassNotFoundException, 
                                                   SQLException, IOException {
        // synchronized (DbProcessor.class) {
        if (instance == null) {
            instance = new DbProcessor();
        }
        // }
        return instance;
    } // method end

public Connection getManagedDBConnection() throws SQLException {
      
        try {
                   if (connection == null || connection.isClosed() == true) {
              // getting connection from pool
                connection = 
                        ((DataSource)new InitialContext().lookup(Constants.DB_DS_JNDI_NAME)).getConnection();
            }
  }catch (SQLException e) { /* left empty for now*/ }
}// method end


    public CallableStatement getCallableStmt(final String call, 
                                             Connection connection) throws SQLException {
        CallableStatement cs = null;
        if (connection != null) {
            cs = connection.prepareCall(call);
        } else {
            log.error("Connection is null");
        }
        return cs;
    } // method end


//JDBCCloser class starts

    public static void close(CallableStatement statement) {
        if (statement == null) {
            return;
        } else {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error(e);
            }
        }
        log.info("Closed callable statement ");
    }// method end

    public static void close(Connection connection) {
        if (connection == null) {
            return;
        } else {
            try {
                connection.close(); // returning connection to the pool
            } catch (SQLException e) {
                log.error(e);
            }
        }
        log.info("Closed connection ");
    }// method end

[Jan Cumps]

Do you hold a reference to the pooled connection somewhere in your class?

In that case, if you call: if (connection == null || connection.isClosed() ...

The connection will not be null, because it still exists in the pool, and you don't set the variable to null after closing..
The connection is not closed, because calling close() on a pooled connection does not close the connection, but returns the still open connection to the pool.

[Jan Cumps]

You have a finally block without a try block. Does that ever execute?

[Vikrama Sanjeeva]

Jan Cumps wrote:Do you hold a reference to the pooled connection somewhere in your class?

In that case, if you call: if (connection == null || connection.isClosed() ...

The connection will not be null, because it still exists in the pool, and you don't set the variable to null after closing..
The connection is not closed, because calling close() on a pooled connection does not close the connection, but returns the still open connection to the pool.

You have a finally block without a try block. Does that ever execute?

We have different methods for DB accessing. Each method gets new connection by calling:

connection = DbProcessor.getInstance().getManagedDBConnection();

and closes connection by calling:

JDBCCloser.close(connection);

where:

connection: is local variable of each method, therefore its scope is finished as soon as method finishes and thus object connection is available for GC (my understanding)
JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...) code: Please see note.

    public static void close(Connection connection) {
        if (connection == null) {
            return;
        } else {
            try {
                connection.close(); 
                //connection = null; // Note: Do you think we should set it to null after every close ? 
            } catch (SQLException e) {
                log.error(e);
            }
        }
        log.info("Closed connection ");
    }

Yes, we are calling try before finally. I just skipped try part and highlighted finally to emphasize that all resources are ensured to be closed by finally block.

[Jan Cumps]

...

connection is available for GC (my understanding)

JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...)

...

Answer to both points is: no.

1: The connection is not available for garbage collection. It is returned to the pool, and will remain a living object.

2: calling close() on a pooled connection does *not* close it. It sends the connection back to the pool, without closing it.

[Vikrama Sanjeeva]

Jan Cumps wrote:

...

connection is available for GC (my understanding)

JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...)

...

Answer to both points is: no.

1: The connection is not available for garbage collection. It is returned to the pool, and will remain a living object.

2: calling close() on a pooled connection does *not* close it. It sends the connection back to the pool, without closing it.

Do you think connection = null after connection.close() will close the connection instead of sending it back to the pool ?

connection.close();   
connection = null;

If yes, then, what is the better strategy w.r.t performance (i) do connection = null after connection.close() (ii) just do connection.close()

[Jan Cumps]

No. You don't want to *really* close the connection. That is the whole point of a connection pool. It is a pool of open connections.

[Vikrama Sanjeeva]

Jan Cumps wrote:No. You don't want to *really* close the connection. That is the whole point of a connection pool. It is a pool of open connections.

In other words, you are saying in connection pooling one should not do connection=null and connection.close() is enough ?