from the logs I see that the culprit for the long time is the instruction
Connection con = ds.getConnection();

I am on jboss, so I have variouse datasource.xml files including one for the jndiName specified...

what could be the possible cause for this occasional (but frequent) delay... it takes often 20 secs to get the connection, while usually less then 0.1...

do you see anything wrong with the code? how can I improve it? the pool size are set to 0 min and 3 max and this seems to work perfectly for other applications using the same db (but possibly schemas and datasources)

My guess is that the 20 second delay is what it takes to establish a connection with your database (it would help if you said what database you are using and which JDBC driver).

Another possibility is that with max connections set to 3, if all three connections are in use and another app needs a connection, it needs to wait for one of the connections to be freed. If the apps holding on to the connections hold on for a long time, or if they fail to close the connections and instead wait for the app server to time them out, then there could be the 20 second delay.

A min pool size of 0 is not a good idea. It means that every time the connections are not used for the timeout time that all connections are dropped. Then the next app that needs a connection suffers the connection delay. If there are already available connections in the pool , then the time to get a connection is very short.

By the way, this is more of a JBoss AS connection pooling question that a JDBC question, moving.

The DB is Oracle 10 and driver I believe (I'm home now so can't really check) is OracleDriver.
I am having same problem on a similar application still under development...
In this case I am reusing a datasource which was defined for testing purposes and that is not used by any other application...
The datasource is similar to the one described before, and most of the times I get a connection failry quickly.... but in some occasions it takes up to 5-7 MINUTES... with no exceptions or timeout...
I realized that although I was always causing the connection in a finally block, I was not always closing the result set and the statement...
could this be the cause? I have now modifed the code to close statement and result set, modified datasource to a minimum of 1 connection (maybe will set it to 3) and max 12 and it seems to behave correctly... but as the problem occurred rather randomly, I'll keep an eye on it...

In that case the connection does not return to the pool, and is thus not reusable, until the connection timeout forces the connection to close.

I recommend that you set your max-pool-size to a fairly large number and then track the number of connections actually allocated and use that information to define the pool sizes. The Jboss.jca:name=<dsname>,service=ManagedConnectionPool BMean maintains connection statistics.

Thanks very much Peter, just one little clarification...
when you say "In that case the connection does not return to the pool" do you mean because the statement is not closed or because the result set is not closed?
I have to admit that I just imagined that under such circumstances I was not releasing memory, but the garbage collector would take care of that... I did not expect this to prevent the system from returning the connection to the pool, and can't see why...

Mark E Hansen

Ranch Hand

Posts: 650

posted 7 years ago

Luca Zitto wrote:Thanks very much Peter, just one little clarification...
when you say "In that case the connection does not return to the pool" do you mean because the statement is not closed or because the result set is not closed?
I have to admit that I just imagined that under such circumstances I was not releasing memory, but the garbage collector would take care of that... I did not expect this to prevent the system from returning the connection to the pool, and can't see why...

The garbage collector will only consider objects that no longer have any reference to them. If you still have a variable with the value of your connection/statement/result set, etc., then it won't be considered.

Now, as far as just letting the GC clean up your leftovers, let me ask you this: When will the GC run? How long will your leftovers sit around holding up resources needlessly?

You really should get in the habit of cleaning up your resources as soon as you're finished with them. For example, consider the following code:

The above code is not complete, but you should give you the general idea. The finally block will make sure that no matter what happens in the try block, the connection object will get closed and cleared out.

Often, this kind of care & handling is left out of the books because it results in much longer examples.

Luca Zitto

Greenhorn

Posts: 24

posted 7 years ago

Mark,
I am sorry maybe I wasn't clear enough, but your code reflects 100% what I was doing... I was closing the connection in a finally block. The problem here seems to be with Statement and ResultSet. I do not understand why closing the connection is not enough to ensure that it is returned to the pool (please read comment from Peter above). Apparently to ensure that the connection returns to the pool, not only I have to close the connection (which I am doing) but also the statement and the result set... why?

Mark E Hansen

Ranch Hand

Posts: 650

posted 7 years ago

I'm not sure I can say whether or not it is required. My practice is that any object which has a 'close' method, I make sure I call it when I'm done with it. Because I've always done this, I've never been concerned with the internals.

Whether objects get collected or not often depends on the implementation of the JDBC driver. For example if the result set references the statement it came from, which in turn references the connection, then hanging on to the result set will keep all three objects in memory. One way to check this is to use a heap dump and examine it using jmap, VirtualVM, Eclipse MAT or some other tool.

As to whether the connection goes back into the pool after closing it, but hanging onto result sets or statements, that also depends on the implementation. I suspect that the connection does not get released because as your code gets the next row in the result set it might be required that the JDBC driver communicate with the database to get the next group of rows, in which case it needs the connection. Now two things can happen here. First, the connection close could mark the connection and any objects it has spawned (result set, statement) as unusable so that when you make a new request you get an exception. Second, the connection could remain intact and wait for the result set and statement to also be closed before actually releasing the connection to the pool. In the JDBC driver I designed, I used the first option. I also maintain backward references such that hanging on to a result set kept the statement and connection.

I recommend that you close the objects in the opposite order in which they are obtained - close the result set first, then the statement, then the connection. And set the references to them to null right after closing them (like Mark shows for the connection in his example code)