Discarded overflow connection object object does not automatically close DB socket connection in Jython
Jaimy Azle
I am using SQLAlchemy with Jython, I found there is a different
SQLAlchemy behaviour between CPython and Jython. In Jython, discarded
overflow connection object does not automatically close the actual
database socket connection during gc, anybody could confirm this?
my temporary, simple workaround was to explicitly close those overflow
connection before discard it in sqlalchemy.pool.QueuePool.
def do_return_conn(self, conn):
try:
self._pool.put(conn, False)
except sqla_queue.Full:
if self._overflow_lock is None:
self._overflow -= 1
>> conn.close()
else:
self._overflow_lock.acquire()
try:
self._overflow -= 1
>> conn.close()
finally:
self._overflow_lock.release()
--
Salam,
-Jaimy Azle
Michael Bayer
This issue is fixed in 0.6.7 and 0.7.0 in aff95843c12f / ee4e79274f1f for both pools.
you can get at these from hg via:
http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz
http://hg.sqlalchemy.org/sqlalchemy/archive/rel_0_6.tar.gz
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Jaimy Azle
> uh yeah you totally nailed that one, QueuePool and
> SingletonThreadPool had similar issues.
it was quite hard to get into this. :)
> you can get at these from hg via:
> http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz
> http://hg.sqlalchemy.org/sqlalchemy/archive/rel_0_6.tar.gz
Thank you, I'll look into those links. However, I still have another
issue on those idle connection objects held in the pool. If there is
no activity related to the database, those connection would be kept
on idle state in the pool, until then it is checked out and
refreshed/recycled. I think i would need to extend QueuePool, and
Queue by adding a functionality to monitor those idle time connection
and dispose it from the pool when maximum idle time reached.
--
Salam,
-Jaimy Azle - http://jaim.log.web.id
Michael Bayer
the pool_timeout parameter manages this from a client perspective, in that a stale connection won't be used. An application with a decent amount of activity should clean these connections out regularly as they are closed the moment they are called up from the pool, and have passed their expiration time (if you aren't observing that, that's another bug).
If you're designing for the use case that the application is doing absolutely nothing for hours, then yes the stale connections will just sit there...but I would think that an app with such little usage would only have a few connections checked out anyway and these are of no consequence. If you really want to sever the TCP link proactively expecting a totally idle application, I'd look into getting the database server to perform this severing (such as what MySQL does automatically after 8 hours).
>
> --
> Salam,
>
> -Jaimy Azle - http://jaim.log.web.id
>
>
>
Jaimy Azle
> the pool_timeout parameter manages this from a client perspective,
> in that a stale connection won't be used. An application with a
> decent amount of activity should clean these connections out
> regularly as they are closed the moment they are called up from the
> pool, and have passed their expiration time (if you aren't observing
> that, that's another bug).
Yes I understand, some database connection pooling implementation in
java does have a functionality to enable programmer performing some
task to verify connection just after it checked out from the pool.
However, in my case this could be considered a rare situation in which
i could put this issue on a lower priority.
> If you're designing for the use case that the application is doing
> absolutely nothing for hours, then yes the stale connections will
> just sit there. but I would think that an app with such little
> usage would only have a few connections checked out anyway and these
> are of no consequence.
My application currently serving about 200-250 users only. However
some of them does performing intensive tasks which by this SA Pool
would automatically opening new connection to serve other user request.
Total number connection actived in the pool could reach 20 connection.
These 20 connection would kept in idle state in the pool for hours
(night) until another request being made tommorow morning and those
connection recycled one by one.
Indeed 20 connections is not a large number, but after few days
running, the database server complains not enough bufferpool to serve
another application. However I might be biased on this, I believe
those issue is more related to unclosed overflow connections rather
than this case.
> If you really want to sever the TCP link proactively expecting a
> totally idle application, I'd look into getting the database server
> to perform this severing (such as what MySQL does automatically
> after 8 hours).
Yes, however not all database have this functionality. afaik,
db2, and firebird does not.
--
Salam,
-Jaimy Azle