Connections not closing?

Hi, hoping someone can offer some explanations.
I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which open
s a single connection to the database and keeps that connection open.
Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
Thanks for any help.
I'd have to see the code, but it hints at
a multithreading issue. I'll bet it goes away
if you *define* the connection as a method-scoped
variable, in the method(s) where it's used, just
before the try block that sets it. That way,
two simultaneous threads won't set the same variable
with two newly-obtained connections, with the last one
overwriting the first, both using and closing the second,
and the first hangs till GC'ed and maybe never closed...
Joe Weinstein at BEA
Chris Baird wrote:

> Hi, hoping someone can offer some explanations.
> I've rigorously checked my java code and confirmed that every place I open a connection is done in a try {} block and the connection is closed in the follwoing finally {} block. There is one exception to this. I'm using the log4j JDBCAppender which op
ens a single connection to the database and keeps that connection open.
> Looking at the performance monitor, I can see that I have many, many open connections. Looking at SQLServer, though, I see that these are all sleeping.
> Any thoughts on this? I did notice this page http://support.microsoft.com/default...;en-us;313220. Could that be what I'm seeing? If so, will additional writes over that connection re-use the implicitly opened connections?
> Thanks for any help.
|||Thanks, Joe.
Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
Connection connection = null;
try {
connection = getConnection();
// do stuff
} finally {
closeConnection(connection);
}
Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
|||Ok, then
Chris Baird wrote:
> Thanks, Joe.
> Actually, my only connection definitions are method scoped. The only one that does not fit this is for the log4j JDBCAppender; however, only one instance of that class is created. All of my methods that use connections look like:
> Connection connection = null;
> try {
> connection = getConnection();
> // do stuff
> } finally {
> closeConnection(connection);
> }
> Where getConnection() does the normal DriverManager call and closeConnection() just ensures that the passed connection object is not null before trying to close it.
Ok. The code looks good then. Here's what I would do: Whenever I got a connection,
I would record or print out the spid, and whenever I closed a connection I would
again print out it's spid before closing. In fact I might query *all* the spids
before closing one, and after (via another connection). The only suspect might
be the driver itself pooling connections. Ah, but note that this driver, unless you
set selectMethod=cursor, will create cloned connections under the covers to
implement multiple concurrent statements. If you're sure to use only one statement
at a time, and close it before opening another, it might go away.
Joe Weinstein at BEA
sqlsql