[Zope-dev] Re: SQLAlchemy integration experiment

[Laurence Rowe]

2008/6/17 Michael Bayer <mike_mp at zzzcomputing.com>:
>
> 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.

What connection pooling is used by default? e.g. with
create_engine('sqlite:///:memory:')

I think we are only talking about the difference between using four
pools of one connection versus one pool of four connections (assuming
the standard four threads in zope). I don't see that making a lot of
difference in practice.

Laurence