Closing a database connection stored in a threadlocal upond death of thread

[Hoss Spence]

Hi,

I have a situation where I open a db connection, store it in a
ThreadLocal object and need to close it on death of the Thread. Any
ideas on how to do this?

 

[Hoss Spence]

Hi,

I have a situation where I open a db connection, store it in a
ThreadLocal object and need to close it on death of the Thread. Any
ideas on how to do this?

My latest thinking on this is to do it in the finalize method of the
ThreadLocal object I'm referencing. Not sure if this is sufficient.

 

[Lew]

My latest thinking on this is to do it in the finalize method of the
ThreadLocal object I'm referencing. Not sure if this is sufficient.

It is not.

There is a finite probability that the finalizer will never be called.

Even it if it is, you won't know when - it could be quite a while.

Database connections tend to be precious resources. You don't want to abuse
them that way.

Always explicitly release your database connections, and ensure that that
happens. Do not leave it to chance or arbitrary scheduling. Control that it
happens, and control when it happens.

That said, finalize() is a place that can serve as a safety net to release
resources, but if they are still held by the time finalize() is called then
you have problems.

One way to ensure resource release is to have the Thread take itself down
rather than have some other Thread kill it. Then the takedown process can
handle resource management. However, if you still have a connection open as
of the Thread's senescence, you probably have suboptimal use of the resource.
It is likely that you should release the db connection much earlier.

That said, Thread cleanup when in it's in hospice can serve as a safety net to
release resources still extant, properly or otherwise.

 

[Hoss Spence]

It is not.

There is a finite probability that the finalizer will never be called.

Even it if it is, you won't know when - it could be quite a while.

Database connections tend to be precious resources. You don't want to abuse
them that way.

Always explicitly release your database connections, and ensure that that
happens. Do not leave it to chance or arbitrary scheduling. Control that it
happens, and control when it happens.

That said, finalize() is a place that can serve as a safety net to release
resources, but if they are still held by the time finalize() is called then
you have problems.

One way to ensure resource release is to have the Thread take itself down
rather than have some other Thread kill it. Then the takedown process can
handle resource management. However, if you still have a connection open as
of the Thread's senescence, you probably have suboptimal use of the resource.
It is likely that you should release the db connection much earlier.

That said, Thread cleanup when in it's in hospice can serve as a safety net to
release resources still extant, properly or otherwise.

I agree with what you're saying (initially I open/wrote/close the db
connection). Turns out mssql, or the connection handler (this is a
logger application per where connection is associated with thread) has
a problem when open/closing too often. 99% it works but there was one
app that it blows on. Anyway was hoping to avoid closing it explicitly
when thread exits as there are multiple apps and places this happens
but think I will do it anyway. Thanks for your reply.

 

[Eric Sosman]

Lew wrote On 06/15/07 12:35,:

It is not.

There is a finite probability that the finalizer will never be called.

Even it if it is, you won't know when - it could be quite a while.

Database connections tend to be precious resources. You don't want to abuse
them that way.

Always explicitly release your database connections, and ensure that that
happens. Do not leave it to chance or arbitrary scheduling. Control that it
happens, and control when it happens.

That said, finalize() is a place that can serve as a safety net to release
resources, but if they are still held by the time finalize() is called then
you have problems.

One way to ensure resource release is to have the Thread take itself down
rather than have some other Thread kill it. Then the takedown process can
handle resource management. However, if you still have a connection open as
of the Thread's senescence, you probably have suboptimal use of the resource.
It is likely that you should release the db connection much earlier.

That said, Thread cleanup when in it's in hospice can serve as a safety net to
release resources still extant, properly or otherwise.

Lew's remarks on finalize() are spot-on. As Bloch puts
it in "Effective Java," finalize() is about the garbage
collector, the garbage collector is about managing memory,
hence finalize() is about managing memory -- not resources
of other kinds.

One way to ensure the release of a DB connection (or
the closing of a file, or whatever) even in the event of
an unexpected termination is to do it in a finally block:

public void run() {
...
Thing precious = getPreciousThing();
try {
doTheWork(precious);
}
finally {
precious.hurlIntoCracksOfDoom();
}
}

The only ways this won't get executed are if your thread
gets into an infinite loop or deadlock somewhere and never
ends, or if the JVM runs so short of resources that it's
unable to run the hurlIntoCracksOfDoom() method. Either
way, you'll be in so much additional trouble that the small
matter of an un-closed DB connection will scarcely matter.

 

[Hoss Spence]

Lew wrote On 06/15/07 12:35,:












Lew's remarks on finalize() are spot-on. As Bloch puts
it in "Effective Java," finalize() is about the garbage
collector, the garbage collector is about managing memory,
hence finalize() is about managing memory -- not resources
of other kinds.

One way to ensure the release of a DB connection (or
the closing of a file, or whatever) even in the event of
an unexpected termination is to do it in a finally block:

public void run() {
...
Thing precious = getPreciousThing();
try {
doTheWork(precious);
}
finally {
precious.hurlIntoCracksOfDoom();
}
}

The only ways this won't get executed are if your thread
gets into an infinite loop or deadlock somewhere and never
ends, or if the JVM runs so short of resources that it's
unable to run the hurlIntoCracksOfDoom() method. Either
way, you'll be in so much additional trouble that the small
matter of an un-closed DB connection will scarcely matter.

That is what I landed up doing but not happy with it. Picture this...
a logging infrastructure that logs to the db on a per thread basis
e.g. Eclipse rich client spawns a new job/thread and a static
ThreadLocal keeps the database connection. Many different Eclipse rich
clients invoke this logging code. So have to hunt down all the places
threads are spawned. Would be much easier if I could catch an
arbitrary thread on it's deaththrows and close the dbconnection stored
in the ThreadLocal.

 

[Lew]

That is what I landed up doing but not happy with it. Picture this...
a logging infrastructure that logs to the db on a per thread basis
e.g. Eclipse rich client spawns a new job/thread and a static

Static bad. Instance good.

ThreadLocal keeps the database connection. Many different Eclipse rich
clients invoke this logging code. So have to hunt down all the places
threads are spawned.

No, have each Thread handle its own resources. Why hunt when the thing will
do it for itself?

Would be much easier if I could catch an arbitrary thread on it's [sic] deaththrows and close the dbconnection stored
in the ThreadLocal.

Sure, that's what you do. You just don't use finalize() for that.

Make it the finally{} of the inner workings of the run(), for example.

<incomplete>
public void run()
{
Connection cxn = obtain();
try
{
while ( ! atomicStop ) // look, I'm just avoiding a side discussion
{
serviceTheDbConnection( cxn );
}
}
finally
{
try{ cxn.close } catch (SQLException e) { logger.warn( exc ); }
}
}
</incomplete>

Now, the next question is why do you hold on to connections for the life of a
thread instead of the life of a transaction? You're going to have scalability
problems there.

 

[christopher]

think 'connection pooling' and open and close the connections as you
need them. The connection pooler handles the efficiencies for you --
that's its job

 

[Hoss Spence]

think 'connection pooling' and open and close the connections as you
need them. The connection pooler handles the efficiencies for you --
that's its job

absolutely right and I came to this conclusion belatedly on Saturday.
There appears to be a bug in the connection pool software and I'm
trying to get around it. Originally I wrote this to open the db
connection, write the log (to db) and close connection. This made it
blow up in one application that was doing an extraordinary amount of
logging. Will need to see if the connection pool software was locally
written (my fear) or a well known driver that can be upgraded.