On Jun 26, 2006, at 12:09 PM, Jonathan Ellis wrote:
> The one (?) place that sqlalchemy pool isn't really transparent is
> wrt closing connections.
>
> Consider this function --
>
> def foo():
> conn = psycopg2.connect(...)
> c = conn.cursor()
> c.execute('select * from users')
>
> To keep this "correct" in a pooled environment, I have to manually
> add "conn.rollback()" or I'll have a connection stuck in an open
> transaction. In a non-pooled environment, rollback is implicitly
> performed.
SA's connection in pool in 0.2 does this also ( i.e. does a rollback()
on every connection returned). it turns out it is pretty necessary
for at least postgres which is pretty strict about locking behavior.
> Also, with a non-pooled connection, you can manually call close()
> instead of relying on refcounts to GC it eventually. In a pooled
> situation this should probably return the connection to the pool,
> instead of really closing it.
you can call close() on a connection that you get back from the pool,
and it will return it to the pool. if you have retrieved the same
connection from the pool multiple times, such as in a thread-locally
controlled environment, it will count how many retrievals there are
and match the corresponding close() operations to that count. the
object youre actually dealing with is a wrapper called
ConnectionFairy (since its ephemeral....).
Wow, I have no idea what I was smoking when I thought I tested these. You're right, of course. Sorry!
> (Other than issuing a blind rollback on every re-pool, which seems
> like an undesireable performance hit.)
do we know that this is a performance hit ? if theres no state on
the connection, i.e. it was already rolled back or committed, then i
think a rollback() is a negligible operation (but thats only my
impression/qualitative observation....its worth testing).
It's doubtless negligible to the db, but you're still doing network round trips, which can add up.
Thinking about it though, this is the right way to go because if someone is moving from a non-pooled situation to a pooled one, the wins from pooling are going to be vastly more than any inefficiency here. So designing for transparency is the right thing to do. In the unlikely event that this becomes a problem he can always customize the pooling mechanism, e.g. with a "commit" flag to ConnectionFairy, but this kind of complexity isn't warranted for mainstream use.
I'd still like to see this mentioned in the docs, though. :)
--
Jonathan Ellis
http://spyced.blogspot.com
Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
