On Tue, Jan 16, 2018 at 12:33 PM, Joe Biggert <[email protected]> wrote: > We've got some legacy code using SQLAlchemy 0.8 (we're actively looking to > upgrade to the latest) and we've got a wrapper around our requests that > basically looks like this: > > > try: > # work > Session.commit() > except: > Session.rollback() > raise > finally: > Session.remove() > if Session.bind: > Session.bind.pool.dispose()
the dispose at the end is completely wrong, sorry. > > > Now there's a couple flavors of that using local session but considering we > are using scoped_session, that probably doesn't make a big difference. > Please correct me if I'm wrong, but the workflow goes like this: we're doing > whatever work we have then committing the transaction... if an exception > occurs, we're rolling back that transaction... and lastly, we're doing a > remove() which rollbacks any underlying transaction and then releases the > connection(s) back to the connection pool per > http://docs.sqlalchemy.org/en/latest/orm/contextual.html#contextual-thread-local-sessions. > > The last part is the one that I'm confused about whether it's intended > usage. it is not at all. you never need to use dispose() unless you are in a test suite that is testing connection pools and engines, or if you are starting up a new process using either os.fork() or multiprocessing. Reading the documentation on the dipose() method, > http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.Pool.dispose, > it leads me to believe our usage of this method isn't needed... and if > anything, could cause connection management problems: >> >> This method leaves the possibility of checked-out connections remaining >> open, as it only affects connections that are idle in the pool. > > > I believe the intent of the usage here is to explicitly close connections > after we're done with them because we've experienced some areas where > connections are left open and idle in transaction. idle in transaction is not related to the pool, it's related to not closing transactions and/or not returning connections to the pool correctly. when a connection is returned to the pool, it calls rollback() on that connection before making it available again, so that there is no idle in transaction (assuming you have not modified pool reset_on_return, which you should not have, if you did that, you need to take that out). calling pool.dispose() cannot fix this issue because dispose() only affects connections that were already returned to the pool successfully and would therefore not be idle in transaction. > I've been searching > around and haven't found anywhere suggesting this function is what should be > used... which is because...you almost never need to use it :) > this link, > https://groups.google.com/forum/#!topic/sqlalchemy/09h4az61oXo, also > suggested not to use it for this purpose. > > Can anyone clear this up for me? The only thing I can think of is that, in > 0.8, something finicky may have been going on so it was recommended at some > time to us this? at least make sure you are on the latest 0.8. the dispose() you are calling has the effect of essentially not using pooling at all. If the 0.8 pool is giving you problems, then just use NullPool in your create_engine: from sqlalchemy.pool import NullPool e = create_engine("...", poolclass=NullPool) with the above, you aren't pooling anything at all. if your application is still leaving sessions or connections hanging around outside of the pool, you still would get "idle in transaction", but if your issue is due to a race condition in the pool itself that would resolve it. I've reviewed the 0.8 and 0.9 changelogs and there are several pool-related bugs that are fixed, however none of them I think would affect the "reset on return" feature or cause connections to not be checked in correctly. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
