[TriLUG] mysql close statements

[Jeremy Portzer]

On Fri, 2004-02-20 at 15:29, Ben Pitzer wrote:
> In any case (I'm speaking for Perl, but I can't imagine it would be much
> different in PHP, C, C++, Python, Ruby, Java, etc.), it's a good idea to
> close the connection, just for the purposes of being thorough.  It's not
> that much extra overhead to issue the close statement, and it makes sure to
> avoid any potential locking issues, etc.  Even if it's just a 'select'
> statement, depending on the library, a table or database could be locked by
> the command while the query is run.  Without a close, that lock could remain
> either until the lock times out, or forever, depending on the db in
> question.  Most dbs will close or timeout locks after the accessing program
> exits, and/or a timeout value is reached, but it's probably best not to
> count on that, not to mention that until it does time out, or if the script
> hangs and never exists, the lock remains in place.  I recommend it in just
> about any event.

I agree with everything you said, Ben, however there is a case in which
you may not want to close a database connection.  What if the
application is making a lot of database queries, and wants to hold a
connection open, to avoid the overhead of starting a new connection for
every page load?  The connection handle variable can be kept with a
session mechanism when required.   The original poster did specify
MySQL, which has table-level locking during the excecution of the
query.  So, we do know that simply holding a connection to the database,
after the query itself is done, is not going to cause a deadlock and
prevent other users.  So holding a connection could be very useful. 
There are even more complex "connection pooling" techniques available so
that a given application server (such as PHP) doesn't open too many
connections to a database; this becomes even more complex.

But the original poster really should provide more information about the
exact situation, most importantly the language, and also information
about the type and needs of the application.

--Jeremy

-- 
/---------------------------------------------------------------------\
| Jeremy Portzer        jeremyp at pobox.com      trilug.org/~jeremy     |
| GPG Fingerprint: 712D 77C7 AB2D 2130 989F  E135 6F9F F7BC CC1A 7B92 |
\---------------------------------------------------------------------/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: <http://www.trilug.org/pipermail/trilug/attachments/20040220/00477377/attachment.pgp>