• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Server - Connection pool leak

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Greetings all.
I am currently running Weblogic 8.1 and a SQL Server 2000 database. I am using JNDI to grab a DataSource from my JDBC Connection pool and am using the weblogic.jdbc.mssqlserver4.Driver JDBC Driver. I have stepped through the configuration docs to *ensure* I set everything up correctly. I have also checked to make sure I am closing all connections when I am done with them. But, my connections max out and I am unable to grab any more connections which renders my application useless.
This application (exact code) ran great on Tomcat. The company just wants to migrate to a commercial product.
Any help would be greatly appreciated. Thanks in advance!
Blaine Mincey
blaine_mincey@mindspring.com
 
Ranch Hand
Posts: 977
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
that's strange, we have a commercial app using sqlserver2000 and it works really well. We're using the MSSqldriver from BEA(the non XA one, the second under the driver's list when you're configuring weblogic pool).
Anyway, I believe you should try to configure the advanced options for the pool and tell the container to shrink and also to test the connections. You can do that under the advanced options for connection pools on the console.
 
Ranch Hand
Posts: 2713
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you show us some sample JDBC code that handles the closing of Connections? I am convinced this is not a problem with WebLogic.
 
Blaine Mincey
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the replies. Chris, here is a snippet of the code....
I have implemented the DAO pattern so this is a method on the implementation class that I am using....I have the connection as a variable on the class that is initialized as part of the constructor. This is used within the getConnection() method below....This connection is just the basic grab datasource from JNDI which is pointing to a pool, and return the connection to me.

Ok, now the DBUtilities class is simply a class to close statements, connections, etc.....so, it will have a similar method for each type.

[Added code tags for formatting. - CM]
Thanks so much for looking at this....and please, no one be shy about telling me if I did something totally ridiculous.....seriously
Thanks again!
Blaine
[ April 22, 2004: Message edited by: Blaine Mincey ]
[ April 23, 2004: Message edited by: Chris Mathews ]
 
drifter
Posts: 1364
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, it looks like you're trying to close things, but are you?
Where you have code like this are you actually printing out an error message so you can see if something is failing?
catch( SQLException sqle )
{
// error message
}

We do all our closing in the finally block and have the helper class/method catch the exceptions and log them if desired. Only one set of close calls needed.
 
Blaine Mincey
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Carol. Thanks for the reply. Yeah, the reason I just have comments in the exception block is that I have been doing a LOT of verbose logging, etc. to see what is going on. I did not want everyone to know how crazy this is making me with all of my log statements
Is there any chance that is could be the driver itself? I have been digging through BEA's website to see if anything is posted about this problem but have been unlucky in finding anything. Does anyone know of any problems with the 8.1 release of Weblogic?
Thanks again!
Blaine
[ April 23, 2004: Message edited by: Blaine Mincey ]
 
Chris Mathews
Ranch Hand
Posts: 2713
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There are some minor issues with your database cleanup logic. For example, if the close() call to a ResultSet causes a SQL error then the Connection will never be closed. Therfore, all of the close() operations should occur in there own try-catch blocks. However, this shouldn't really be causing you that many problems.
Are you sure that in some area of your code the Connection.close() call is not being forgotten? This is really a common problem and is definitely motivation enough to use a framework like Spring-JDBC to prevent things like this from ever happening.
For completeness, here is how I handle closing database resources on the few occassions that I code plain vanilla JDBC directly (very seldom now):

Sample JDBC code would then be:

Even when doing straight JDBC I used to typically abstract out of the Connection, Statement, and ResultSet stuff from the developer so that they couldn't forget to close resources. This is of course a bit more complicated but ultimately leads to a better solution. It is all unnecessary now I because I just use Spring-JDBC when doing any JDBC code because they have essentially done the same abstraction... only better.
 
Blaine Mincey
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Chris.
Thanks SO much for your response and pointing out the errors I had made. You are totally correct about handling all of the closing for the various resources together. I modified my DBUtilities class and modelled it after the example you provided and I am no longer experiencing exhausting the connection pool prematurely or holding on the connections...i.e., no more connection leaks.
Having implemented the DAO pattern, it should not be a problem with implementing the Spring Framework and utilizing their JDBC implementation as you suggested. That is the next thing on my list!
But, many, many thanks for assisting me in solving this problem....and helping my coding skills to improve
Blaine
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic