Thanks for confirming my apprehensions about using engine.dispose(). So I gather it is just a matter of me tweaking the pool_recycle and pool_size options to something more appropriate for my application?
Can you offer any guidance as to some appropriate values for a threaded REST application? Would pool_size be able to be quite small like 2 and pool_recycle something like 2 mins? On Saturday, May 26, 2012 9:31:55 AM UTC+10, Michael Bayer wrote: > > > On May 24, 2012, at 11:28 PM, Bradley Mclain wrote: > > Hi, > > Currently working on a project that use SQL alchemy with mod_wsgi, > webapp2 and prestans (python REST framework). The backend is Oracle XE > using cx_Oracle. > > What is the appropriate way to manage sessions in a multithreading app > such as this? Currently I have been following the guide found > here<http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#contextual-thread-local-sessions> > to > implement a contextual/thread-local session. > > > scoped_session produces a session factory/proxy object which maintains a > single Session per thread, and as the guide indicates you can allow this > factory to automatically create a Session on first use, then at request end > time you do an unconditional remove() so that the scoped_session closes the > Session it may or may not be handling, and disposes of it. > > > > I have then made a session that is automatically cleaned up by the request > handler. The code for this looks something like: > > def dispatch(self): > self.db_session = db.Session() > super(BaseHandler, self).dispatch() > self.db_session.close() > db.Session.remove() > > > db.Session.remove() should be enough here, it calls close() for you. > > > However this doesn't seem to clean up all database connections as viewed > in netstat > > > > The Session uses an Engine as a source of connections. The Engine is > introduced at http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html . > Intrinsic to its behavior is that it uses a small connection pool which by > default holds onto five connections out of the total accumulated. Full > information on how this pooling is configured or disabled is at > http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html. > > and I get a connections exhausted error from Oracle XE after a certain > time of using the application. The same thing also happens when I use MySQL > as well. > > > one of the many options here is the "pool_recycle" option which places a > time limit on the age of connections. > > > > If I add a call to db.engine.dispose() after the handler has returned > this appears to fix the problem but the SQL Alchemy documentation indicates > that this is only for usage with dropped connections or in test suites. > > > I'm glad the documentation made clear that dispose() is not generally > appropriate, as the Engine is a factory/registry for connections, not a > connection itself. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Q0KUApyOoQAJ. To post to this group, send email to sqlalchemy@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.
