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 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/6f84e3a9-7d28-44e6-9e13-3f541aac95e4%40www.fastmail.com.
