Hi,

I have a problem with a datasource getting "stuck". The application that is 
using it does not longer works and I see this in the logs:

[4/28/06 15:11:08:920 EEST] 25a33be6 FreePool      E J2CA0045E: Connecti
on not available while invoking method queueRequest for resource jdbc/HIBKDa
taSource.
[4/28/06 15:11:08:923 EEST] 25a33be6 ConnectionMan E J2CA0020E: The Conn
ection Pool Manager could not allocate a Managed Connection: com.ibm.websphe
re.ce.j2c.ConnectionWaitTimeou
tException: Connection not available, Timed out waiting.
at com.ibm.ejs.j2c.poolmanager.FreePool.createOrWaitForConnection(FreePool.j
ava(Compiled Code))

In Tivoli Performance Viewer I see that there are 4 connections in pool, fre
e pool size 0, number of connections allocated 0, and timeouts 1-3 (that wou
ld be all the requests made in the period of time during 2 consecutive calls
 made by TPV). We also trie
d killing the connection from database (oracle) but this didn't solve the pr
oblem, there were no connections closed, although I'd expect them to be clos
ed by WebSphere since they are not anymore on the database. We also tried re
starting the application bu
t this doens't help either. The only way to make it functional again is to r
estart  WebSphere.

This seems rather weird because we have on the same server another datasourc
e that is configured exactly like this one (database/user/pass/connection po
ol settings/etc) which we use for another application and that one doesn't g
et stuck.

So there could be 2 problems...
1. there is a connection leak at application level, which gets connections f
rom the pool but doesn't return them; but in this case I don't understand wh
y Websphere doesn't "grow" the pool, maximum connections in the pool is 4 (a
lthough min connections for
this datasource is set to 5).

2. a bug in websphere. (because the connection pool doesn't grow although th
ere are not any free connections left, restarting the app doesn't solve the 
problem).

Or a mix between the two of them; maybe there is a piece of code in the appl
ication which causes the datasource to get stuck. Another weird thing is tha
t sometimes the applications works ok for a week, and other times it gets st
uck several times a day.

The config:
Websphere 5.0.2.12
Datasource connection pool settings:
Connection Timeout: 60 seconds
Max Connections: 30 connections
Min Connections: 5 connections
Reap Time: 180 seconds
Unused Timeout: 2000 seconds
Aged Timeout: 0 seconds
Purge Policy: FailingConnectionOnly

Do you have any ideea why this is happening ?

Thank you,
George

[ Post a follow-up to this message ]

georgeav@gmail.com wrote:
> Hi,
>
> I have a problem with a datasource getting "stuck". The application that i
s using it does not longer works and I see this in the logs:
>
> [4/28/06 15:11:08:920 EEST] 25a33be6 FreePool      E J2CA0045E: Connec
tion not available while invoking method queueRequest for resource jdbc/HIBK
DataSource.
> [4/28/06 15:11:08:923 EEST] 25a33be6 ConnectionMan E J2CA0020E: The Co
nnection Pool Manager could not allocate a Managed Connection: com.ibm.websp
here.ce.j2c.ConnectionWaitTimeou
> tException: Connection not available, Timed out waiting.
>         at com.ibm.ejs.j2c.poolmanager.FreePool.createOrWaitForConnection(
FreePool.java(Compiled Code))
>
> In Tivoli Performance Viewer I see that there are 4 connections in pool, free pool
 size 0, number of connections allocated 0, and timeouts 1-3 (that would be all the 
requests made in the period of time during 2 consecutive calls made by TPV). We also
 tr
ied killing the connection from database (oracle) but this didn't solve the 
problem, there were no connections closed, although I'd expect them to be cl
osed by WebSphere since they are not anymore on the database. We also tried 
restarting the application
but this doens't help either. The only way to make it functional again is to restart  WebSph
ere.
>
> This seems rather weird because we have on the same server another datasou
rce that is configured exactly like this one (database/user/pass/connection 
pool settings/etc) which we use for another application and that one doesn't
 get stuck.
>
> So there could be 2 problems...
> 1. there is a connection leak at application level, which gets connections from th
e pool but doesn't return them; but in this case I don't understand why Websphere do
esn't "grow" the pool, maximum connections in the pool is 4 (although min connection
s f
or this datasource is set to 5).
>
> 2. a bug in websphere. (because the connection pool doesn't grow although 
there are not any free connections left, restarting the app doesn't solve th
e problem).
>
> Or a mix between the two of them; maybe there is a piece of code in the applicatio
n which causes the datasource to get stuck. Another weird thing is that sometimes th
e applications works ok for a week, and other times it gets stuck several times a da
y.

>
> The config:
> Websphere 5.0.2.12
> Datasource connection pool settings:
> Connection Timeout: 60 seconds
> Max Connections: 30 connections
> Min Connections: 5 connections
> Reap Time: 180 seconds
> Unused Timeout: 2000 seconds
> Aged Timeout: 0 seconds
> Purge Policy: FailingConnectionOnly
>
> Do you have any ideea why this is happening ?
>
> Thank you,
> George

Just guessing - are you trying to open more real connections to Oracle
than it's configured for or licensed for?

Ken

[ Post a follow-up to this message ]

> Just guessing - are you trying to open more real
> connections to Oracle
> than it's configured for or licensed for?
>
> Ken

Our DBA told me that the license is per CPU, and we don't have a connection 
limit.

George

[ Post a follow-up to this message ]

georgeav@gmail.com wrote: 
>
> Our DBA told me that the license is per CPU, and we don't have a connectio
n limit.
>
> George

This error message really looks like WAS asked Oracle for a connection
and didn't get one.
Ken

[ Post a follow-up to this message ]

> This error message really looks like WAS asked Oracle
> for a connection
> and didn't get one.
> Ken

Aha, so you're saying that actually websphere tried to increase the connecti
ons pool for this datasource but it couldn't create another connection to or
acle.
I'll take a look when I get back to work to see what happens with the other 
datasource when this one has problems, to see if that pool is growing/shrink
ing freely.

Another thing that I'm thinking about now is the preTestSQLString thing; I'l
l take a look at this setting. Maybe there's a problem with it.

Also, I'll ask the DBA to check the Oracle logs to see if there's anything w
eird there.

Thanks,
George

[ Post a follow-up to this message ]