aspose file tools*
The moose likes JDBC and the fly likes connection pooling issue in production environment - Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "connection pooling issue in production environment - " Watch "connection pooling issue in production environment - " New topic
Author

connection pooling issue in production environment -

Vikrama Sanjeeva
Ranch Hand

Joined: Sep 02, 2001
Posts: 756
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.


Count the flowers of your garden, NOT the leafs which falls away!
Prepare IBM Exam 340 by joining http://groups.yahoo.com/group/IBM340Exam/
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

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?


OCUP UML fundamental and ITIL foundation
youtube channel
Vikrama Sanjeeva
Ranch Hand

Joined: Sep 02, 2001
Posts: 756
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
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

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
Ranch Hand

Joined: Sep 02, 2001
Posts: 756
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.


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
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

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.

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
Ranch Hand

Joined: Sep 02, 2001
Posts: 756
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:



[Thumbnail for app_settings.png]

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

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
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

You have a finally block without a try block. Does that ever execute?
Vikrama Sanjeeva
Ranch Hand

Joined: Sep 02, 2001
Posts: 756
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:


and closes connection by calling:

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.



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
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

...
  • 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
    Ranch Hand

    Joined: Sep 02, 2001
    Posts: 756
    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 ?
    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
    Bartender

    Joined: Dec 20, 2006
    Posts: 2477
        
        7

    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
    Ranch Hand

    Joined: Sep 02, 2001
    Posts: 756
    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 ?
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: connection pooling issue in production environment -
     
    Similar Threads
    recreating damaged connection in pool
    JDBC connection in Oracle db
    JDBC in JSP/Servlet question
    Re: Closing database connection in destroy metod()?
    JDBC connection in Oracle db