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....).
if you dont call close() on the ConnectionFairy, when you remove all
references to it, the __del__() method gets called, and then it
internally does a close()/return connection to pool.
> (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). if there
is state on the connection, then we definitely want to roll that
back. also, the database dialects can be given the chance to perform
database-specific operations (which might include skipping the
operation for a database where it doesnt matter) during this step if
we are looking for that.
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
