closing database connections

Discussion in 'Python' started by dustin lee, Jan 23, 2004.

  1. dustin lee

    dustin lee Guest

    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.
     
    dustin lee, Jan 23, 2004
    #1
    1. Advertising

  2. dustin lee

    andy Guest

    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
     
    andy, Jan 23, 2004
    #2
    1. Advertising

  3. dustin lee

    Brian Kelley Guest

    andy wrote:

    > 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.

    > hth,
    > -andyj
    >
    >
     
    Brian Kelley, Jan 23, 2004
    #3
  4. dustin lee

    Ken Guest

    (dustin lee) wrote in message news:<>...
    > 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
     
    Ken, Jan 24, 2004
    #4
  5. 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.
     
    Frank Bechmann, Jan 24, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Do
    Replies:
    2
    Views:
    371
    srinivas moorthy
    Dec 9, 2003
  2. Patrice
    Replies:
    3
    Views:
    7,137
    Michael D. Long
    Sep 22, 2004
  3. Michael D. Long

    Re: Opening / Closing SQL Server connections

    Michael D. Long, Sep 22, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    494
    Michael D. Long
    Sep 24, 2004
  4. Dave V
    Replies:
    0
    Views:
    446
    Dave V
    Jun 13, 2007
  5. jobs
    Replies:
    2
    Views:
    903
Loading...

Share This Page