how to return cx_Oracle connection to "normal" state after using begin() w xid, e.g. xa_end (?)

[zzzeek]

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

[cjbj]

I think cx_Oracle should/could expose XA_END. We were waiting to hear from our XA team about this and anything other gaps they want to discuss.

A pure PL/SQL example I used to use is like:

   drop table mytable;
   create table mytable (id number, salary number);
   insert into mytable values (1, 100);
   insert into mytable values (2, 300);
   commit;

Session 1:

   var rc number
   exec :rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMNOFLAGS);
   UPDATE mytable SET salary = salary * 1.1 WHERE id = 1;
   exec :rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
   SELECT * from mytable;

Session 2:

   var rc number
   exec :rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMRESUME);
   UPDATE mytable SET salary = salary * 3 WHERE id = 2;
   exec :rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
   SELECT * from mytable;

Session 3:

   var rc number
   exec :rc := DBMS_XA.XA_COMMIT(DBMS_XA_XID(123), TRUE);
   SELECT * from mytable;

Management has lots of doc e.g. Managing In-Doubt or Pending Oracle XA Transactions. I am happy to start a dialog with the XA team if you have questions.

[zzzeek]

sorry for my lack of knowledge on PL/SQL calling forms how do I run that with the cursor?

    cursor.execute(
        """
        exec :rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
        """)

fails with "ORA-00900: invalid SQL statement". How does the argument to DBMS_XA_XID relate to the arguments I'm passing to connection.begin() ? (e.g. can you make my example script work)

[zzzeek]

I can get it to execute like this:

    cursor = conn.cursor()

    outval = cursor.var(str)
    cursor.execute("""
        begin
        :rc := DBMS_XA.XA_END(DBMS_XA_XID(:a, :b, :c), DBMS_XA.TMSUSPEND);
        end;
        """,
        {"rc": outval, "a": xid[0], "b": xid[1], "c": xid[2]}
    )
    cursor.close()

but the connection still doesn't work after that.

[anthony-tuininga]

I took a quick look. It would appear that the transaction remains associated with the connection even after commit/rollback takes place! I did a quick patch to ODPI-C to have it remove the transaction on commit and the code you provided works fine, then. I'll check internally if this is the right thing to do and, if so, provide a patch. :-)

[zzzeek]

does that also mean that in my test, when I call vanilla begin() it's still using the global xid silently?

[anthony-tuininga]

No, that transaction has been cleared so local transactions start as usual when needed. You would need to call connection.begin() again to start a 2nd distributed transaction. I'm still waiting on confirmation that what I did is the right way to do that. Once I get confirmation I'll create a patch -- which you can try yourself if you have the ability and desire to do so!

[zzzeek]

I was referring with the current, buggy behavior - " the transaction remains associated with the connection even after commit/rollback takes place" , but even when that issue occurs, " that transaction has been cleared so local transactions start as usual when needed" ? or once the bug is fixed?

[anthony-tuininga]

Apologies for the confusion! In the current (buggy) behavior, the transaction appears to remain associated with the connection even after commit/rollback takes place. With the proposed patch, commit/rollback clears that transaction so that subsequent local transactions start as usual when needed. Does that make more sense?

[zzzeek]

yup that clarifies thanks!

[cjbj]

I'm marking this as an enhancement so it doesn't get auto-closed. We may still want to augment the XA support, in addition to the ODPI-C change.

[anthony-tuininga]

cx_Oracle 8.2 has just been released which contains this patch. The XA support may be augmented further in the future, probably to use the names recommended by the database API (tpc_begin, etc.)