closing database connections

[dustin lee]

Over the years I've gotten out of the habit of explicitly closing file
objects (whether for reading or writing) since the right thing always
seems to happen auto-magically (e.g. files get written to disk with no
missing data). I've recently started do the same thing with database
connections. I'm wondering if anyone has had trouble with this sort
of "lazy" programming style. My assumption is the the database
connection will get closed auto-magically when the interpreter closes.
I'd be interested to hear if any one has been bitten by not
explicitly closing database connections and under what cirumstances.
My main fear is that I will "run out of" database connections or in
some other way adversely affect server performance. I'm using recent
versions of both mysql and oracle.

Thoughts?

dustin

ps. If someone wants to try to talk me into explicitly closing files
I'd be interested to hear wisdom on that as well.

 

[andy]

I seem to remember reading somewhere that as client connections are a finite
(and often scarce) resource, it's not the client but the server that will
suffer when you do this.

hth,
-andyj

 

[Brian Kelley]

I seem to remember reading somewhere that as client connections are a finite
(and often scarce) resource, it's not the client but the server that will
suffer when you do this.

I think there might be two issues here. One is using the reference
counting scheme to close files and databases, the other is to keep
connections open, which andy seems to be referring too.

This discussion has occured a lot in the python community. I'll give a
case in point. The python interface to metakit has no close method. It
can only be closed through reference counting and by going out of scope.

The problem with this, is that if some piece of code has a reference to
the metakit database, it will *never* be closed and this causes hard to
find bugs with metakit. In C-python reference counting closes files and
database connections "soon" after they go out of scope. This may or may
not be true in java python. I haven't tested this.

The upside is that if you explicitly close the database, it will be
closed when you expect and you don't have to worry about other objects
having a reference to your cursor or connection or what not.

That being said, just relying on the reference count and letting python
clean up has worked very well for me with metakit.

 

[Ken]

I'd be interested to hear if any one has been bitten by not
explicitly closing database connections and under what cirumstances.
My main fear is that I will "run out of" database connections or in
some other way adversely affect server performance. I'm using recent
versions of both mysql and oracle.

Yep, you should definitely and explicitely close database connections.
Failure to do so can easily leave connections open and resources
locked - not much unlike closing your ssh sessions by killing the
client rather that logging out.

Kind of a coincidence my seeing this email actually - just two minutes
ago I read an email from a team member instructing others on the team
to close their database connections (php/db2) because some connections
occasionally hang and interfere with our loads.


ken

 

[Frank Bechmann]

there are two extra features to be taken into account when working
with database-connections (assuming you are working on problems that
require non-sequential DB-access):

as already mentioned DB-connections are valuable resources, they are
not only limited, their creation also is performance-relevant. so
quite soon you will find yourself working w/ connection pooling.

even more complicated are transactional problems because connections
"wrap" transactions; so there might be times when you must make sure
that consequent calls to the DB use the same transaction whereas in
other cases you need to access the DB with parallel but isolated
transactions.

So in conclusion I don't think that there is just one golden rule for
DB-connection creation and release, your golden rule will most of the
time be golden just for your actual problem.