how to return cx_Oracle connection to "normal" state after using begin() w xid, e.g. xa_end (?) · Issue #530 · oracle/python-cx_Oracle (original) (raw)

hey all -

Just as we were discussing this recently, we now have a user dealing with 2pc for Oracle. On the SQLAlchemy side, I've had 2pc marked as "deprecated" as I was misled by the removal of the "twophase" flag indicating this meant two phase support was being dropped.

Looking now to re-establish complete 2pc support for Oracle this is the API question I have, which is how to revert a cx_Oracle connection back to "non 2pc" state after a begin(*xid) / prepare()/ commit() sequence has completed.

It appears that once conn.begin(*xid) is called, and then prepare() and commit() are done, all subsequent calls to commit() or rollback() will emit "ORA-24776: cannot start a new transaction", unless conn.begin() was called first, but I suspect the connection is still using that same XID.

It looks like I want the equivalent of XA_END https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_xa.htm#BABGAGII to be somehow available. But I don't know how to get that to work. If cx_Oracle had API to de-associate the connection from the prepared transaction entirely that would be best.

Demo so far. If you can show me what to put in the "cant get this part to work" part, I can move forward.

Also, once an XA transaction is created, what's the lifecycle of the "branch" ? that is, if I run a prepared transaction test in my CI, is it creating transaction artifacts on each run that are persistent? if so how do I get rid of them? thanks for your help!

import cx_Oracle import random

def setup(conn): cursor = conn.cursor() try: cursor.execute("DROP TABLE foo") except: pass

cursor.execute("CREATE TABLE foo (id INT)")
cursor.close()
conn.commit()

def do_a_twophase_thing(conn):

id_ = random.randint(0, 2 ** 128)
xid = (0x1234, "%032x" % id_, "%032x" % 9)

conn.begin(*xid)
cursor = conn.cursor()

cursor.execute("INSERT INTO foo(id) VALUES(1)")

cursor.close()

prepared = conn.prepare()
assert prepared
conn.commit()

# here, we want everything 2pc to be poof gone.  if that's
# totally impossible, then OK we will change our implementation :)


if False:
    cursor = conn.cursor()

    # can't get this to work, I think passing in the xid we have
    # above would be best
    cursor.execute(
        """
        declare
        trans_id dbms_xa_id := dbms_transaction.local_transaction_id( TRUE );
        begin
            trans_id :=
            DBMS_XA.XA_END(trans_id, dbms_xa.tmnoflags);
        end;
        """)
    cursor.close()

def do_a_normal_thing(conn, emit_begin): # things "work" if we do an explicit begin(). # however normal pep-249 does not require begin() and the connection # appears to be in a permanent state of "needs begin"

if emit_begin:
    conn.begin()

cursor = conn.cursor()

cursor.execute("INSERT INTO foo(id) VALUES(2)")

cursor.close()

conn.commit()

conn = cx_Oracle.connect(user="scott", password="tiger", dsn="oracle18c")

setup(conn) do_a_twophase_thing(conn) # works do_a_normal_thing(conn, emit_begin=True) # works do_a_normal_thing(conn, emit_begin=False) # ORA-24776: cannot start a new transaction