views:

1818

answers:

3

We are having trouble with a Java web application running within Tomcat 6 that uses JDBC to connect to a SQL Server database.

After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.

We are not using any connection pooling right now and we are using the standard JDBC/ODBC/ADO driver bridge to connect to SQL Server.

Should we consider using connection pooling to eliminate the problem?

Also, should we change our driver to something like jTDS?

+1  A: 

I would definitely give jTDS a try. I've used it in the past with Tomcat 5.5 with no problems. It seems like a relatively quick, low impact change to make as a debugging step. I think you'll find it faster and more stable. It also has the advantage of being open source.

In the long term, I think you'll want to look into connection pooling for performance reasons. When you do, I recommend having a look at c3p0. I think it's more flexible than the built in pooling options for Tomcat and I generally prefer "out of container" solutions so that it's less painful to switch containers in the future.

Robert Simmons
has anyone given jTDS a try? I'm runing Tomcat 5.5 with the sql server 2000 jdbc connector and I'm running into issues handling special characters. its a pretty painful problem.
phill
A: 

It's hard to tell really because you've provided so little information on the actual failure:

After a few requests, the application server dies and the in the log files we find exceptions related to database connection failures.

Can you tell us:

  • exactly what the error is that you're seeing
  • give us a small example of the code where you connect and service one of your requests
  • is it after a consistent number of transactions that it fails, or is it seemingly random

I have written a lot of database related java code (pretty much all my code is database related), and used the MS driver, the jdt driver, and the one from jnetDirect.

I'm sure if you provide us more details we can help you out.

Dwayne King
+3  A: 

That is the correct behavior if you are not closing your JDBC connections.

You have to call the close() method of each JDBC resource when you are finished using it and the other JDBC resources you obtained with it.

That goes for Connection, Statement/PreparedStatement/CallableStatement, ResultSet, etc.

If you fail to do that, you are hoarding potentially huge and likely very limited resources on the SQL server, for starters.

Eventually, connections will not be granted, get queries to execute and return results will fail or hang.

You could also notice your INSERT/UPDATE/DELETE statements hanging if you fail to commit() or rollback() at the conclusion of each transaction, if you have not set autoCommit property to true.

What I have seen is that if you apply the rigor mentioned above to your JDBC client code, then JDBC and your SQL server will work wonderfully smoothly. If you write crap, then everything will behave like crap.

Many people write JDBC calls expecting "something" else to release each thing by calling close() because that is boring and the application and server do not immediately fail when they leave that out.

That is true, but those programmers have written their programs to play "99 bottles of beer on the wall" with their server(s).

The resources will become exhausted and requests will tend to result in one or more of the following happening: connection requests fail immediately, SQL statements fail immediately or hang forever or until some godawful lengthy transaction timeout timer expires, etc.

Therefore, the quickest way to solve these types of SQL problems is not to blame the SQL server, the application server, the web container, JDBC drivers, or the disappointing lack of artificial intelligence embedded in the Java garbage collector.

The quickest way to solve them is to shoot the guy who wrote the JDBC calls in your application that talk to your SQL server with a Nerf dart. When he says, "What did you do that for...?!" Just point to this post and tell him to read it. (Remember not to shoot for the eyes, things in his hands, stuff that might be dangerous/fragile, etc.)

As for connection pooling solving your problems... no. Sorry, connection pools simply speed up the call to get a connection in your application by handing it a pre-allocated, perhaps recycled connection.

The tooth fairy puts money under your pillow, the Easter bunny puts eggs & candy under your bushes, and Santa Clause puts gifts under your tree. But, sorry to shatter your illusions - the SQL server and JDBC driver do not close everything because you "forgot" to close all the stuff you allocated yourself.

JohnnySoftware