Managing SQL Server Connection in Tomcat 6
We are having problems with a Java web application running in Tomcat 6 that uses JDBC to connect to a SQL Server database.
After a few requests, the application server dies and exceptions related to database connection failures are found in the log files.
We are not using connection pooling right now, and we are using the standard JDBC / ODBC / ADO driver bridge to connect to SQL Server.
Should we consider using a connection pool to fix the problem?
Also, should our driver be changed to something like jTDS?
source to share
This is correct behavior as long as you do not close JDBC connections.
You must call the close () method on each JDBC resource when you are finished using it, and any other JDBC resources you got with it.
This applies to Connection, Statement / PreparedStatement / CallableStatement, ResultSet, etc.
If you don't, you are running potentially huge and probably very limited resources on the SQL server to begin with.
In the end, no connections will be provided, getting execution requests and returning results will fail or hang.
You may also notice that your INSERT / UPDATE / DELETE statements hang if you cannot commit () or rollback () at the end of each transaction, unless you set the autoCommit property to true.
I've seen that if you apply the strictness above to your JDBC code client, JDBC and your SQL server will work just fine. If you write shit, then everything behaves like shit.
A lot of people write JDBC calls waiting for "something" else to release every thing by calling close () because it's boring and the application and server don't immediately fail when they leave it.
It's true, but these programmers wrote their own programs to play 99 Beers on the Wall with their server.
Resources will be depleted and queries will tend to result in one or more of the following: connection requests do not fire immediately, SQL statements fail immediately or hang forever, or until some legitimate long transaction timeout expires etc.
So the fastest way to solve these SQL problems is not to blame the SQL server, application server, web container, JDBC drivers, or the disappointing lack of artificial intelligence built into the Java garbage collector.
The quickest way to solve them is to pick up the guy who wrote JDBC calls in his application talking to your SQL server with a Nerf dart. When he says, "What have you done for ...?!" Just point to this post and tell him to read it. (Remember not to shoot in the eyes, things in his hands, things that might be dangerous / fragile, etc.)
As for the connection pool solving your problems ... no. Sorry, connection pools are just speeding up the call to get a connection in your application by passing it a pre-allocated, possibly reworked connection.
The Tooth Fairy puts money under your pillow, the Easter Bunny puts eggs and candy under your bushes, and Santa Claus puts gifts under your tree. But, sorry to shatter your illusions - the SQL server and JDBC driver are not shutting down everything because you "forgot" to shut down everything you set aside.
source to share
I would definitely give jTDS a try. I've used it in the past with Tomcat 5.5 with no problem. This appears to be a relatively quick and ineffective change as a debug step. I think you will find it faster and more stable. This also has the advantage of being open source.
Long term, I think you will want to look into connection pooling for performance reasons. When you do, I recommend taking a look at c3p0 . I think it's more flexible than Tomcat's built-in pooling capabilities, and I generally prefer out-of-container solutions so that it is less painful to switch containers in the future.
source to share
It's hard to tell because you provided so little information about the actual failure:
After several requests the server application dies and in the log files we find exceptions related to the database connection errors.
Can you tell us:
- exactly what the error is. you see
- give us a small sample code where you connect and serve one of your requests
- is it after the sequential number of transactions it fails or it seems random
I wrote a lot of database related java code (almost all of my code is database related) and used the MS driver, jdt driver and one from jnetDirect.
I am sure that if you can provide us with more details, we can help you.
source to share