Jboss connection pool not releasing the connection from pool

[Baiju Varugese]

Hi,

I am using JBoss 4.0.2RC1 for my application, to get the database connection i am using jboss datasource, i configured the oracle-ds.xml for my connection pool setting,

in my application i am closing the connection when ever it is not required, but after some time if i monitor the connection pool in the jmx-console it is show all the available connection in use, then i increased the number of connection in the pool to 200 but the in the console it showing only 100 connection is create and all the connection is active, the connection is still active if the application is idle for more than 30 minutes.... if i tried to access my application I am getting the following exception when i try to get the connection from the connection pool
(I set the max-connection pool size to 100)

No ManagedConnections available within configured blocking timeout ( 20000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 20000 [ms] ))

Following is the datasource file:

<local-tx-datasource>
<jndi-name>defaultSource</jndi-name>
<connection-url>jdbc racle:thin:@localhost:1521:host</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>username</user-name>
password
<min-pool-size>5</min-pool-size>
<max-pool-size>100</max-pool-size>
<max-idle-timeout>0</max-idle-timeout>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>1</idle-timeout-minutes>
<track-statements>false</track-statements>
</local-tx-datasource>

Thanks

please help me if i missed some thing...

[Sanju Thomas]

I don't think that JBoss connection pool will leak. Look at your code again

[Reid M. Pinchback]

To begin with, make sure your Oracle database is willing to allow that many simultaneous connections. Not sure what the current default is, but I think around the 8i timeframe the default was something like 50. I tend to be suspicious of what is going on in the code if you need a couple of hundred connections for a single application.

Unless you are hosting "Amazon.com", a well-tuned application usually does well with 10 or 20, depending on the number of simultaneous threads of execution. More than that usually indicates that your transactions are way too long, you are leaking connections, or you have some kind of long-lived I/O block or deadlock.

[Dave Salter]

I've not seen this problem before with JBoss.

How are you getting and releasing connections from the pool?

[Baiju Varugese]

thank you for your valuable comments.

Today only i digged (i am not the person who coded it, i am now porting an application which is running in JRUN to JBoss) into the code, and identfied the problem when jboss is not releasing the connection.

The code issue is

We need to run a (100 - 1000) bacth contains multiple connection so we are running the batch in a loop so at the begining of the loop we are begining the transaction and at the end of the loop we are commit the transaction

while(importBatch.hasNext())
{
userTransaction.begin();
//....
other jdbc calls comes here
//..
userTransaction.commit();
}

while monitoring the above code in the jmx-console we identified that the jboss is taking only one connection for one iteration and till the end of the loop none of the connection is released to the pool. If the batch contains 1000 imports it tries to open 1000 connection but it should be in 1000 transaction...

please tell me where i am going wrong ....

is transaction should be between the begin and commit of user transaction...

[Dave Salter]

Are you closing your connections/resultsets/prepared statements correctly ?

[Baiju Varugese]

Yes, i am closing the connection then and there

[Reid M. Pinchback]

Closing the connection isn't going to necessarily cause the connection to be released if the container somehow things the database transaction is waiting on something else (the connection close is just a release of the connection back to the pool, not a commit). I'd start by focusing on things that might stop the database transaction from being coordinated with your JTA transaction. Two things I know of that you can try:

1. Because you are using Oracle, be sure to set the xid padding to 'true' in jboss-service.xml.
2. I'd try switching to an XA datasource instead of local one.

The first issue might be the entire problem, so I would try that first. If still stuck, move on to the second one.

[Paul Sturrock]

2. I'd try switching to an XA datasource instead of local one.

Can I ask Reid, what are you trying to solve by suggesting that?

[Reid M. Pinchback]

A couple of reasons, but basically just educated guesswork.

Depending on how a system is configured, the JTA transaction is either wrapped around the database transaction, or it is actually a separate server artifact that has to be coordinated with the database transaction. Not sure how JBoss deals with this internally, but with Weblogic you clearly configure your transactionality to be one or the other. I'm not sure if it makes sense to think of coordinating between a server transaction and a local database transaction; generally transaction coordination should be between XA resources to cover all the various cases (commit, rollback, sharing vs isolation, etc.). Sometimes with Weblogic you can choose wrap an XA proxy around a local resource to make the right thing happen, but I've never seen wording in JBoss docs that indicates a similar feature.

Second reason is that while JDBC datasources aren't strictly required to be implemented as JCA, basically their architecture is the same. How JCA connections are shared or released is actually required to be different for local versus XA versus non-transactional situations. XA connections tend to play nicer. Just operating on the guess that the JDBC connection pools in JBoss might have similar issues because in the specific case of JBoss the server deploys and manages them the same way as other resource adaptors.

[Baiju Varugese]

Hi,

Thanks Paul & Reid,

We reached an workaround instead of using the JTA, we changed to JDBC transaction, now the application is working fine....

[Reid M. Pinchback]

Glad you got the problem fixed. For that to work, it sounds very much like the Xid padding wasn't configured.

[Wiley Snyder]

this was your original error ...
javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 20000 [ms] ))

and all you had to do was ...

<blocking-timeout-millis>20000++[add]</blocking-timeout-millis>

in your datasource

[LeoKing]

Well i have the same problem as of Baiju. I tried changing BlockingTimeoutMillis to 50000 in the JMX console view

Reid, Also XID padding in jboss-service.xml is already set to TRUE.

Baiju, can you please explain how you Solved your Problem?

Thanks.
[ June 26, 2006: Message edited by: LeoKing ]

[Arun Kumar]

I am precisely facing the same issue.

The parameters seems alright to me all the connections are closed properly.


It seems to me that jboss creates the connection with the minimum pool settings goes on creating more connections and never looks back to pick up the old connections.

[Jaikiran Pai]

Arun,

You will have to check your code to see whether the connections are being released properly. You can take help from this to see if there are any connection leaks in your application.