On Wed, Jan 27, 2021, at 2:23 PM, Thierry Florac wrote: > Hi, > I'm actually using two databases connections: one is my "main" connection, > opened on a ZODB (with RelStorage), and **sometimes** I have to open another > connection on another database (and event more sometimes); the two > transactions have to be synchronized: if one of them is aborted for any > reason, the two transactions have to be aborted.
OK, then two phase it is > I have always thought that the two-phase transaction was created to handle > this kind of use case, but if there is another better solution, I would be > very happy to know about it! if you need the ORM to call prepare() then you need the XID and there you are. This is all stuff that I think outside of the Zope community (but still in Python) you don't really see much of. If someone's Flask app is writing to Postgresql and MongoDB they're just going to spew data out to mongo and not really worry about it, but that's becasue mongo doesn't have any 2pc support. It's just not that commonly used because we get basically nobody asking about it. > > @jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github and > made a pull request. But I don't know how to provide a test case as a > two-phase commit is not supported by SQLite... > I'll try anyway to provide a description of a "method" I use to reproduce > this! So interesting fact, it looks like you are using Oracle for 2pc, that's what that tuple is, and we currently aren't including Oracle 2pc in our test support as cx_Oracle no longer includes the "twophase" flag which I think we needed for some of our more elaborate tests. At the moment, create_xid() emits a deprecation warning. I've been in contact with Oracle devs and it looks like we should be supporting 2pc as I can get help from them now for things that aren't working. I've opened https://github.com/sqlalchemy/sqlalchemy/issues/5884 to look into this. you should have been seeing a deprecation warning in your logs all this time though. > > Best regards, > Thierry > -- > https://www.ulthar.net -- http://pyams.readthedocs.io > > > Le mer. 27 janv. 2021 à 19:19, Mike Bayer <mike...@zzzcomputing.com> a écrit : >> __ >> >> >> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote: >>> >>> Hi, >>> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages. >>> My main database connection is a ZODB connection and, when required, I >>> create an SQLAlchemy session which is joined to main transaction using this >>> kind of code: >>> >>> * from *sqlalchemy.orm *import *scoped_session, sessionmaker >>> >>> * from *zope.sqlalchemy *import *register >>> * from *zope.sqlalchemy.datamanager *import* join_transaction >>> >>> _engine = get_engine(*engine*, *use_pool*) >>> if *use_zope_extension*: >>> factory = scoped_session(sessionmaker(*bind*=_engine, >>> *twophase*=*True*)) >>> else: >>> factory = sessionmaker(*bind*=_engine, *twophase*=*True*) >>> session = factory() >>> if *use_zope_extension*: >>> register(session, *initial_state*=*STATUS_ACTIVE*) >>> if *join*: >>> join_transaction(session, *initial_state*=*STATUS_ACTIVE*) >>> >>> Everything is working correctly! >>> >>> So my only question is that I also use Pyramid_debugtoolbar package, which >>> is tracking many SQLAlchemy events, including two-phase commits >>> transactions, and which in this context receives transaction IDs as a three >>> values tuple instead of a simple string (like, for example: (4660, >>> '12345678901234567890123456789012', '00000000000000000000000000000009'), >>> which is raising an exception)! >>> Is it normal behaviour, and what does this value mean? >> >> I would ask if you really really want to use the "twophase=True" flag, and I >> would suggest turning it off if you aren't in fact coordinating against >> multiple RDBMS backends (and even if you are, maybe). I'm not really sure >> what that tuple is, I'd have to look but it seems likely to be related to >> the XID stuff, which is really not something anyone uses these days. >> >> >>> >>> Best regards, >>> Thierry >>> >>> -- >>> https://www.ulthar.net -- http://pyams.readthedocs.io >>> >>> -- >>> 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/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%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/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%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/CAPX_VWBZ8B%3DqVd6J7_pg3yyZdvAcDfOjA86Pfn0XMpXKRu%2BT_A%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWBZ8B%3DqVd6J7_pg3yyZdvAcDfOjA86Pfn0XMpXKRu%2BT_A%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/fc51abcd-a39e-4f52-84a6-2d8d07827b84%40www.fastmail.com.