[Zope-dev] Re: SQLAlchemy integration experiment

[Michael Bayer]

On Jun 17, 2008, at 10:09 AM, Laurence Rowe wrote:
>
> I'm not sure connection pooling is really useful in a threaded  
> environment with recycled sessions. You want n threads = n  
> connections. If we started creating new sessions each request then  
> things would be different.


For this to be efficient in SQLA (if there were no pool), you'd need  
to bind each Session to a Connection individually.    If you  
create_engine() using a pool like NullPool then you'd get an unpooled  
engine that creates new connections on each checkout.     In theory  
this would be fine, although we don't have any current test coverage  
for the "auto-reconnect" logic using NullPool with persistently- 
checked-out Connections so we'd have to build that up and ensure its  
working.

But if you are binding Sessions directly to Connections, theres still  
not really any disadvantage to the usual QueuePool implementation  
remaining in place.   If you set it with max_overflow=-1 it will just  
hand out any number of connections.

I still prefer the Session bound to Engine approach, whereby each  
transaction uses a pooled connection, and returns the connection on  
rollback/commit.   That way you don't need to worry about stale  
connections hanging around for sessions/threads that fall out of use.