On Thursday, 9 January 2014 09:41:40 UTC-8, Jeff Dairiki wrote:
>
> Okay, I've traced things out a bit more.
>
> If the session state is not STATUS_INVALIDATED (aka STATUS_CHANGED),
> SessionDataManager.commit() does a self._finish('no work'). That is
> where self.tx gets set to None (this --- correctly --- then
> causes .tpc_vote() and .tpc_finish() to be no-ops.)
>
> So here's the crux of the biscuit: in two-phase-commit mode (at least
> with MySQL) the sqlalchemy session (or session transaction) must be either
> committed or explicitly rolled back before it is closed.
> SessionDataManager.commit() does not do a rollback.
>
> Example code:
>
> import sqlalchemy as sa
>
> engine = sa.create_engine('mysql://guest@furry/test',
> echo='debug',
> echo_pool='debug')
> Sess = sa.orm.sessionmaker(bind=engine, twophase=True)
> sess = Sess()
> sess.query(sa.null()).scalar()
> #sess.rollback()
> sess.close()
>
> Edited log output:
>
> DEBUG:sqlalchemy.pool.QueuePool:Connection <_mysql.connection open to
> 'furry' at 29a3370> checked out from pool
> INFO:sqlalchemy.engine.base.Engine:BEGIN TWOPHASE (implicit)
> INFO:sqlalchemy.engine.base.Engine:XA BEGIN %s
>
> INFO:sqlalchemy.engine.base.Engine:('_sa_a38de3b7234d5fe8ad55d2bb13ec195c',)
>
> INFO:sqlalchemy.engine.base.Engine:SELECT NULL AS anon_1
> DEBUG:sqlalchemy.pool.QueuePool:Connection <_mysql.connection open to
> 'furry' at 29a3370> being returned to pool
> DEBUG:sqlalchemy.pool.QueuePool:Connection <_mysql.connection open to
> 'furry' at 29a3370> rollback-on-return
> INFO:sqlalchemy.pool.QueuePool:Invalidate connection
> <_mysql.connection open to 'furry' at 29a3370> (reason:
> OperationalError:(1399, 'XAER_RMFAIL: The command cannot be executed when
> global transaction is in the ACTIVE state'))
> DEBUG:sqlalchemy.pool.QueuePool:Closing connection <_mysql.connection
> open to 'furry' at 29a3370>
>
> Here, no attempt is made to terminate the two-phase (XA) transaction
> until the connection is returned to the pool, at which point a
> plain one-phase 'ROLLBACK' is issued. MySQL does not like this,
> thus the XAER_RMFAIL error.
>
> Uncommenting the 'sess.rollback()' in the above example results in an
> 'XA END" and 'XA ROLLBACK' being emitted before the connection is
> returned to the pool, properly ending the two-phase (XA) transaction.
> This eliminates the XAER_RMFAIL error, and results in proper recycling
> of the pooled connection.
>
Yup, your analysis here looks correct to me:
- The SessionTransaction.close() does not call transaction.close() and in
turn transaction.rollback() because the connection's autoclose is True as
``conn is not bind``
-
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L431
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/orm/session.py#L297
When the connection is returned to the pool, it will rollback-on-return and
that's throwing the exception as it is simply calling
``pool._dialect.do_rollback(fairy)`` -
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/pool.py#L408
When you call session.rollback() the engine's ``do_rollback_twophase()`` is
being called which executes the appropirate "XA ROLLBACK :xid"
-
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_1/lib/sqlalchemy/dialects/mysql/base.py#L2086
I think the ``pool._dialect.do_rollback`` and ``pool._dialect.do_commit``
calls in _finalize_fairy need to take account of the twophase nature of the
connection.
I don't think zope.sqlalchemy should be involved here, as the pool
configures how a connection should be closed with reset_on_return:
http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#sqlalchemy.pool.QueuePool.__init__.params.reset_on_return
Worth reporting as a SQLAlchemy bug, though fixing it cleanly looks rather
tricky.
You might be able to rig something together with event listeners, but it
looks tricky. For the pools reset event you won't have access to the xid
and is_prepared of the TwoPhaseTransaction, so you'll need to store these
on the connection during:
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.PoolEvents.reset
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.begin_twophase
http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.prepare_twophase
I think this should do it (untested):
from sqlalchemy import event
@event.listens_for(SomeEngine, 'begin_twophase')
def receive_begin_twophase(conn, xid):
conn._note_xid_prepared = (xid, False)
@event.listens_for(SomeEngine, 'prepare_twophase')
def receive_prepare_twophase(conn, xid):
conn._note_xid_prepared = (xid, True)
@event.listens_for(SomeEngineOrPool, 'reset')
def receive_reset(dbapi_con, con_record):
conn = con_record.connection
if not hasattr(conn, '_note_xid_prepared'):
return
xid, is_prepared = conn._note_xid_prepared
del conn._note_xid_prepared
conn.dialect.do_rollback_twophase(xid, is_prepared)
Hope that helps,
Laurence
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
