On Jun 29, 12:21 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jun 29, 2010, at 1:47 PM, Wyatt Lee Baldwin wrote:
>
> > Short version: How do I completely dispose of all resources belonging
> > to an engine that uses a standard queue pool?
>
> I'll reference the docs below just so they're here, I can tell that you've
> found them already:
>
> http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?...http://www.sqlalchemy.org/docs/reference/sqlalchemy/pooling.html?high...
>
> > The problem is that checked-out connections won't be closed and will
> > be returned to their respective pools. The question then is, how do I
> > clean up these connections?
>
> This depends on how you are using your pool. The worst case scenario is that
> your application is multithreaded. One thread wants to "dispose" all
> connections, but you might have other threads that are still receiving
> results and commiting data on checked out connections. This is why
> SQLAlchemy can't provide such a function (even though there is one, see
> below) - the application has to make sure no concurrent threads are working
> with connections - otherwise, yanking all the currently open connections
> would cause your other threads to fail.
>
> Beyond that, you'd ensure that any ResultProxy objects returned from an
> execute() call have been closed, which is only necessary if they have pending
> rows remaining, otherwise they close automatically, and any usage of
> Connection from engine.connect() has been cleaned up. If you're using ORM
> sessions with autocommit=False (the default), they need to be closed out from
> connection resources by using one of: commit(), rollback(), close(), or with
> scoped_session remove().
It's a fairly standard multi-threaded Pylons Web app that calls
Session.remove() at the end of each request. All DB operations go
through the ORM.
Disregarding all the verbiage below, it sounds like Session.remove
already does what I want: "[When Session.remove is called], the
Session is thrown away, all of its transactional/connection resources
are closed out, everything within it is explicitly gone."
So, if the underlying connection is closed at the end of every
request, I don't actually need to do anything, right? I wouldn't even
need to call `dispose` on the engine because all of its connections
would already be closed.
This raises a question: isn't the point of using a pool to avoid
connecting to the database? Using `remove` seems like a brute force
approach and that it might be better--in terms of the number of times
we physically connect to the DB--to use `commit` or `rollback` as
appropriate; but if I did that, I'd be back to needing to call
dispose, etc.
So, perhaps what I already wrote below is still relevant...
> If you want to get more intrusive, you can monitor all connection checkouts
> with a listener and track all the checked out connections, and close those
> too. We have an object that does this in sqlalchemy.test.engines called
> ConnectionKiller, and its pretty easy to use.
One idea I toyed with was marking a pool as disposed when `dispose` is
called (with a simple PoolQueue subclass). A checkin listener would
then see that the pool is disposed and close the connection via the
connection record.
What I wanted to do was `if pool.disposed: connection_record.close()`
or even `if connection_record.pool.disposed: ...`, but __pool is a
private attribute on the connection record, which made the code feel
hackish (`connection_record._ConnectionRecord__pool`).
I semi-abandoned this idea when I noticed the connection is still
returned to the pool and is `put` back into the underlying queue. I
couldn't tell if this would cause a memory leak, even though the
connection was closed.
Here's the custom dispose implementation:
def dispose(self):
super(Pool, self).dispose()
# What if a checkin happens here?
self.disposed = True
Here's the listener implementation:
def checkin(self, dbapi_connection, connection_record):
pool = connection_record._ConnectionRecord__pool
if pool.disposed:
connection_record.close()
> > I have a Web service that creates a bunch of engines on demand (that
> > all use the default QueuePool). It needs to close inactive connections
> > after X seconds. I monitor access to these engines then `dispose` of
> > them after X seconds of inactivity. (Note: I'm dealing with legacy
> > architecture here; otherwise, I wouldn't choose to create all these
> > engines.)
>
> so if X is a small number here, the most efficient system would be just to
> not use a pool at all (i.e. use NullPool). Then you just make sure you're
> coding your database calls reasonably as above so that connections get closed
> when you're done with them (and using try/finally to ensure that "done"
> actually happens).
Right now, X is 15 minutes.
> > On a related note, why is that pool listeners don't have access to the
> > corresponding pool instance?
>
> no particular reason except the intended use case didn't anticipate pool
> access within a checkout itself. you wouldn't want to be calling connect()
> or dispose() from inside a connection listener and that's pretty much the
> whole API for a pool.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
