nevermind, you've managed to find a case that trips it up for the connection pool
release today On Wed, May 13, 2020, at 11:51 AM, Mike Bayer wrote: > > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/b7ff30e4-722c-4324-98ca-6d1afa1693ba%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/95ebb408-7bad-4c0e-a14b-b6fce9a4ada3%40www.fastmail.com.
