FWIW, within the realm of pyramid_tm, the more common use-cases for two-phase transaction support are for sending mail and a dealing with task queues - not two separate databases.
On Wednesday, January 27, 2021 at 2:40:21 PM UTC-5 Mike Bayer wrote: > > > 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 <mik...@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+...@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+...@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+...@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/162c6199-2636-4c40-8a2e-db6c638c701fn%40googlegroups.com.
