On Wed, May 13, 2020, at 11:39 AM, Kent Bower wrote: > In this script, conn.close() does *not *call rollback on the transaction. It > isn't just a logging issue as I've verified from the database that the > session was not rolled back.
I can confirm that in master only where things have changed dramatically. Should not be the case for any released version, please confirm > > On Wed, May 13, 2020 at 11:31 AM Mike Bayer <mike...@zzzcomputing.com> wrote: >> __ >> Haven't looked deeply but so far what you need to know is that conn.close() >> *ALWAYS* rolls back the transaction, just not at the Engine level, it's at >> the connection pool level so you won't see it when logging / event hooking >> on the Engine. turn on echo_pool and you will see this, in modern versions: >> >> 2020-05-13 11:25:45,106 DEBUG sqlalchemy.pool.impl.QueuePool Connection >> <connection object at 0x7f660c367b90; dsn: 'user=scott password=xxx >> dbname=test host=localhost', closed: 0> being returned to pool >> 2020-05-13 11:25:45,107 DEBUG sqlalchemy.pool.impl.QueuePool Connection >> <connection object at 0x7f660c367b90; dsn: 'user=scott password=xxx >> dbname=test host=localhost', closed: 0> rollback-on-return, via agent >> >> your DBSession is not going to close the connection because you have it >> bound directly to that connection, rather than to the engine, so it assumes >> it is participating in a larger transaction. 1.4 does amend this behavior to >> be more clear cut as we are doing away with the "nested" behaviors of >> Connection. So yes I would not be relying upon DBSession.close() as a means >> of transaction control if the session is bound to a connection directly. If >> the session is bound to a connection I would advise ensuring that connection >> is in a transaction on the outside that you are managing. >> >> >> >> >> >> >> >> On Wed, May 13, 2020, at 10:51 AM, Kent wrote: >>> Mike, et al., >>> >>> I've got some questions about closing connections. I suspect my framework >>> may be at fault, but there is potentially a sqlalchemy issue here as well. >>> >>> See attached script with nested transaction and explicit connection.close(). >>> >>> Things are even more complex because versions have handled this differently >>> in the past: >>> * on rel_0_9_1 and ealier, the conn.close() *always *actually emitted a >>> DBAPI ROLLBACK, but on rel_0_9_2+, the previous call to .begin_nested() now >>> prevents the DBAPI ROLLBACK call, even though the close() is on the >>> connection itself. I'm not sure if that was an intended change, but it >>> seems .close() on a connection should always cause ROLLBACK, no? >>> * rel_1_3_9 and earlier this code raises >>> sqlalchemy.exc.ResourceClosedError on the last DBSession.close() as it >>> invokes the registered 'rollback' event with an already-closed connection, >>> but on current master (1.4.0b1) there is no exception since a rollback >>> isn't attempted, leaving the db connection in idle transaction. >>> >>> On all versions since rel_0_9_1, even after both of the script's finally >>> clauses (close() statements) but before the program terminates, *the >>> transaction is still left in transaction in the database, though the >>> connection's been checked back into the pool.* >>> >>> As far as whether my code here is badly formed, my question is: is it wrong >>> to mix session closing and connection closing or should that be fine? >>> >>> (My actual application is obviously more complex, with zope.sqlalchemy & >>> transaction and frameworks; I boiled it down to this script for demo >>> purposes and removed those libraries, making this code look weirder.) >>> >>> Thanks in advance! >>> Kent >>> >>> -- >>> 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 sqlalchemy+unsubscr...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/a9d73e26-84c6-4dcf-bb6d-82f541fa1f6d%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/a9d73e26-84c6-4dcf-bb6d-82f541fa1f6d%40googlegroups.com?utm_medium=email&utm_source=footer>. >>> >>> >>> *Attachments:* >>> * connection-close.py >> >> >> -- >> 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 a topic in the >> Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/Lit5HWFiC0U/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> sqlalchemy+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/6f84e3a9-7d28-44e6-9e13-3f541aac95e4%40www.fastmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/6f84e3a9-7d28-44e6-9e13-3f541aac95e4%40www.fastmail.com?utm_medium=email&utm_source=footer>. > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CANnF6aFqH7nrtDVd5CrchnoP%2Bz2pY-utazLAcQm%2BfBje785gTA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CANnF6aFqH7nrtDVd5CrchnoP%2Bz2pY-utazLAcQm%2BfBje785gTA%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b7ff30e4-722c-4324-98ca-6d1afa1693ba%40www.fastmail.com.
