Microsoft SQL Server 2005 JDBC, uncommited transactions. Pool Manager Help!
So we switched to Microsoft sql 2005 JDBC. However, in the Java if you close the connection before a transaction is commited, it gets recycled in to the connection pool still open.
Than means that the next piece of code that uses the connection will never be commited and our database locks up like crazy.
The solution here is to find an appropriate Pool Manager but I can not find one. Alternatively, is there some other kind of strategy for handling this problem?
Yes, it would be nice if our java code was perfect, but this is not going to happen. We can't have our site go down every time a developer makes a minor mistake and this problem is niegh impossible to track to it's source.
[715 byte] By [
Chaseja] at [2007-9-25]
> So we switched to Microsoft sql 2005 JDBC. However,
> in the Java if you close the connection before a
> transaction is commited, it gets recycled in to the
> connection pool still open.
So the solution is to close your connections every time in a finally block, after the transaction is committed.
> Than means that the next piece of code that uses the
> connection will never be commited and our database
> locks up like crazy.
It won't happen if you manage your resources properly.
> The solution here is to find an appropriate Pool
> Manager but I can not find one. Alternatively, is
> there some other kind of strategy for handling this
> problem?
Yes, write proper JDBC code. Or use something like Spring that will manage resources for you.
> Yes, it would be nice if our java code was perfect,
> but this is not going to happen. We can't have our
> site go down every time a developer makes a minor
> mistake and this problem is niegh impossible to track
> to it's source.
That's what standards and code review are for.How 'bout writing a utility class that does it the same way for everyone, every time? Don't tell me you still have developers writing that stuff for every query.
Use Spring. You'll never lose a connection. I've used it on a site that's been in production since Sep 2005 - not a single connection lost.
Have your container help you. WebLogic has a number of features that can help with pool management.
%
This is the same person...
We do. We either commit or roll back the connection, and then we call 'close()' which returns it to the pool.
try
{
begin transaction
commit transaction//this is the very last thing in the try clause
}
catch
{
rollback //this is the only thing in the catch clause
}
finally
{
connection.close()//this is the only thing in the finally clause
}
The thing is, is that we're still ending up with situations where neither the commit or the rollback are getting executed, yet the connection is still be returned to the pool. We even set up a trace on sql server to track this down and it's exactly what happened. The begin gets executed, then some other statements, and then... no commit or rollback! The connection is recycled and begins getting used by another process!
I have a feeling that our webserver is killing the thread for some reason, but is somehow still calling 'close' on the connection. This isn't a matter of standards or "managing resources properly". And even if it was, do you really want a minor bug to take down your server? Some of our code gets quite complex.
But in this case it isn't.There has got to be some setting, or strategy for handling this. Is there?
You say that you have not had any problems. Are you using the Microsoft JDBC driver?We did not have this problem with a previous driver (it had it's own problems).What driver do you use?
Is anybody else able to recommend a solid JDBC driver?
> The thing is, is that we're still ending up with
> situations where neither the commit or the rollback
> are getting executed, yet the connection is still be
> returned to the pool.
Then you have a bug in your code and you need to fix it.
Or stop using the pool.
>
> You say that you have not had any problems. Are you
> using the Microsoft JDBC driver?We did not have
> this problem with a previous driver (it had it's own
> problems).What driver do you use?
>
The Microsoft driver for MS SQL Server?
As reported here that has a number of problems. You would be better off even using the odbc bridge. You can find the TDS driver on sourceforge.
Note however that if you do not commit/rollback then your code is STILL wrong. It doesn't matter what driver you use.
I'd have the connection close in its own try/catch block, and I'd log any exceptions that are thrown. The only way the snippet you posted can compile is if the method throws SQLException, since close() can throw that exception. I'd make sure I knew it if that failed.
Which pool are you using? DBCP from Apache? Or is it a class that's home-grown?
Which app server are you using? WebLogic lets you do some more sophisticated things with pool management and connection settings. Worth exploring.
Why would the app server be killing the thread?
You really need some data. Got a profiler you can use? You need to see what's really happening with this error and stop guessing about root cause. Be a scientist - measure.
I've had no problems. We're running on WebLogic 9.1, Spring 1.2, and Oracle 9i.
Joe's suggestion of jTDS is a good one. I've never used it in production, because most of the places I've worked for required Oracle.
I see that MS SQL Server's JDBC driver has service pack 2 out. Is that any improvement?
%
>Then you have a bug in your code and you need to fix it.You've never had a bug with a third party product? Or you don't believe what I'm saying?Anyway, TDS had it's own issues. We were using that, but were having totally different problems.
> Which pool are you using?com.inet.pool.PoolManagerI was looking for an alternative when we switched drivers but could find none. And as far as I know, it should be able to work with any data source.
> >Then you have a bug in your code and you need to fix
> it.
>
> You've never had a bug with a third party product?
> Or you don't believe what I'm saying?
You expressed a specific scenario.
Basically the only way that scenario can come about in the driver is due to some random event. That sort of thing is common in C++. It isn't common in java.
However there are a large number of ways it can easily come about if there is a bug in your code.
