SQLAlchemy transaction ID

32 views
Skip to first unread message

Thierry Florac

unread,
Jan 27, 2021, 8:32:34 AM1/27/21
to sqlalchemy
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?

Best regards,
Thierry

-- 

Jonathan Vanasco

unread,
Jan 27, 2021, 12:52:40 PM1/27/21
to sqlalchemy
Thierry,

Would you mind putting together a test-case on this?  I haven't experienced that before, and I authored that feature in the debugtoolbar.  If I can recreate it, I'll put together a fix and work with the pyramid team to get a new release out asap.

Mike Bayer

unread,
Jan 27, 2021, 1:19:45 PM1/27/21
to noreply-spamdigest via sqlalchemy
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

-- 


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Thierry Florac

unread,
Jan 27, 2021, 2:24:12 PM1/27/21
to sqlalchemy
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.
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!

@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!

Best regards,
Thierry

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com.

Mike Bayer

unread,
Jan 27, 2021, 2:40:21 PM1/27/21
to noreply-spamdigest via sqlalchemy


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.




Jonathan Vanasco

unread,
Jan 27, 2021, 4:51:41 PM1/27/21
to sqlalchemy
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.

Thierry Florac

unread,
Jan 29, 2021, 8:27:31 AM1/29/21
to sqlalchemy
Hi Mike,
I'm effectively using cx-Oracle... but I didn't notice any deprecation warning! Maybe it's because I'm still using release 8.0.1 (as I still need Python 3.5 support)?

If you need anyone to test for cx-Oracle features, just ask!

Anyway, I'm also using other "no-transactional" databases (like Elasticsearch indexes), for which I effectively just use a basic datamanager to post data, without any need for synchronized transactions...

Best regards,
Thierry

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fc51abcd-a39e-4f52-84a6-2d8d07827b84%40www.fastmail.com.

Mike Bayer

unread,
Jan 29, 2021, 9:53:41 AM1/29/21
to noreply-spamdigest via sqlalchemy
the deprecation warning is from the SQLAlchemy side and we need to remove it which will be for 1.4.

Also we've identified a bug in cx_Oracle in this area that's not fixed yet, a cx_Oracle 2pc transaction cannot be replaced by a non-2pc transaction on a single connection:


I would guess that your environment is not immediately impacted because you use 2pc in all cases.
Reply all
Reply to author
Forward
0 new messages