Closing connection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tumurbaatar S. - 20 Jul 2005 05:56 GMT

Is it so important to close database connections?
As I understand, after processing a request and sending a response,
page object destroyed. So all used connections also destroyed.
Yes?

Reply to this Message

Peter Rilling - 20 Jul 2005 06:04 GMT

Theoretically objects should clean themselves and release any resources when
they are disposed of. There are a couple problems with this practice.

1) Finalization is non-deterministic. Meaning that you do not know when
the server destroys the objects.
2) Do you really want to trust the implementation of some black-box
component to clean itself. If you do it yourself, then you know that it is
done.

> Is it so important to close database connections?
> As I understand, after processing a request and sending a response,
> page object destroyed. So all used connections also destroyed.
> Yes?

Reply to this Message

Rob R. Ainscough - 20 Jul 2005 06:59 GMT

Peter,

But the problem with ADO.NET is that closing a connection (even explicitly)
may not immediately close the connection (you can see this in real time at
the server side). But then again, a more serious problem IMHO is that
developers have to deal with connection state at all -- cause as you said it
is now a black box that we "think" we have control over, but in reality we
"partially" have some control but not the final control.

I wish MS would offer those of us that are NOT interested in connection
management at least an alternative -- a nice property on the Connection
object -- perhaps "AutoManage" = True or False. For those that enjoy and
feel it necessary to manage connections can, and for those that really have
better things to do than connection management, don't have to. Both worlds
are now happy -- of course that means MS would need to more work to make
this a reality -- I think they can afford it ;)

Rob.

> Theoretically objects should clean themselves and release any resources
> when
[quoted text clipped - 11 lines]
>> page object destroyed. So all used connections also destroyed.
>> Yes?

Reply to this Message

Joerg Jooss - 20 Jul 2005 19:55 GMT

> Peter,
>
[quoted text clipped - 8 lines]
> I wish MS would offer those of us that are NOT interested in
> connection management at least an alternative

[...]

That basically translates to sloppy programming that produces resource
leaks.

Sorry.

Signature

http://www.joergjooss.de
mailto:news-reply@joergjooss.de

Reply to this Message

Rob R. Ainscough - 20 Jul 2005 20:39 GMT

Having MS manage the connection = resource leak?

I don't follow you -- how is closing a connection (and it not being really
closed on the server end) sloppy programming? Unless you're refering the MS
programming?

Forcing developers to perform connection management does OPEN the door for
sloppy programming (a door in which we should have the option to keep
closed) -- yes I agree with that and exactly why MS should provide an option
to deal with it automatically and efficiently -- rather than the
inconsistant approach they use now.

Rather than the current method to try to close a connection:
close the datareader
close the connection
loop thru connectionstate to ensure it gets closed or timesout
dispose of the connection
and for good measure set it to Nothing
and if you're really thorough, then initial GS (garbage collection)

Then and only then, can you be pretty sure your connection is closed.
That's a LOT of work to ensure a connection gets closed -- and since it is
recommended to close connections ASAP, the developer's are encouraged to
open/close many many many times -- opening a closing connections A LOT is
also not efficient.

Rob.

>> Peter,
>>
[quoted text clipped - 14 lines]
>
> Sorry.

Reply to this Message

Joerg Jooss - 21 Jul 2005 19:57 GMT

> Having MS manage the connection = resource leak?
>
> I don't follow you -- how is closing a connection (and it not being
> really closed on the server end) sloppy programming? Unless you're
> refering the MS programming?

No, *not* closing a connection (and relying on finalization) is sloppy
programming.

> Forcing developers to perform connection management does OPEN the
> door for sloppy programming (a door in which we should have the
> option to keep closed) -- yes I agree with that and exactly why MS
> should provide an option to deal with it automatically and
> efficiently -- rather than the inconsistant approach they use now.

[What inconsistent approach?]

There is simply no way of automagically closing (or rather releasing,
see below) connections efficiently in .NET Or Java as of today, due to
the unmanaged nature of these resouces. You can do it either explicitly
or rely on (read: hope for) finalization. The latter option is just
plain bad.

> Rather than the current method to try to close a connection:
> close the datareader
[quoted text clipped - 3 lines]
> and for good measure set it to Nothing
> and if you're really thorough, then initial GS (garbage collection)

Note that this is all boilerplate code that can be easily abstracted
away. It's just a matter of application architecture.

> Then and only then, can you be pretty sure your connection is closed.
> That's a LOT of work to ensure a connection gets closed -- and since
> it is recommended to close connections ASAP, the developer's are
> encouraged to open/close many many many times -- opening a closing
> connections A LOT is also not efficient.

Welcome to the world of connection pooling. A proper ADO.NET data
provider will not simply close and reestablish physical connections,
but pool them. In this context, "close" means actually "release and
return to the pool", whereas "open" means "obtain from the pool".

Cheers,

Signature

http://www.joergjooss.de
mailto:news-reply@joergjooss.de

Reply to this Message

Scott Allen - 21 Jul 2005 20:50 GMT

>I wish MS would offer those of us that are NOT interested in connection
>management at least an alternative -- a nice property on the Connection
[quoted text clipped - 3 lines]
>are now happy -- of course that means MS would need to more work to make
>this a reality -- I think they can afford it ;)

Most providers provide a way to turn off connection pooling, i.e.
'Pooling=false' in the connection string for SQLConnection.

I would never do this unless I had a really, really good reason. I'd
need a much better reason than "I enjoy managing connections".

--
Scott
http://www.OdeToCode.com/blogs/scott/

Reply to this Message

Eliyahu Goldin - 20 Jul 2005 09:28 GMT

The common consensus is that database connections, unlike other objects,
should be closed explicitly as soon as possible.

Eliyahu

> Is it so important to close database connections?
> As I understand, after processing a request and sending a response,
> page object destroyed. So all used connections also destroyed.
> Yes?

Reply to this Message

Closing connection

Free Magazines