Re: [cx-oracle-users] cx_Oracle connection recovery subsequent to successful two-phase sequence

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Dear Michael,
I posted this message (https://sourceforge.net/mailarchive/message.php?msg_id=28850922) on this list on 2012-02-17 and I'm copying it again due to some limitations of the SourceForge archive viewer.
Unfortunately no one replyed me and I guessed there is no real interest in distributed transaction programming inside the Python community.

Regards
Ch.F.

From the previous post:

Dear All,
I'm the founder of LIXA project ( http://sourceforge.net/projects/lixa/ http://lixa.sourceforge.net/manuals/ ) and I'm interested in integrating LIXA with cx_Oracle.
LIXA
 is LIbre XA, a free and open source XA compliant transaction manager: 
it implements XA interface to deal with the Resouce Managers and 
implements TX interface to deal with the Application Programs.
LIXA 
supports Oracle as a Resource Manager and allows to create distributed 
transaction s (2 phase commit) with PostgreSQL, MySQL, Oracle and DB2.
XA and TX are interfaces designed for C (and C++) programs.
I am now interested in expanding the LIXA project scope to Python language and Python programmers.
It seems to me cx_Oracle is a good start point because it wraps the Oracle C API; LIXA too uses OCI.

It's time to describe my issue to ask some help to you.
First of all, I'm not a Python expert.
I've
 generated a LIXA wrapper using SWIG and it works fine, the usage is 
something like this (my development environment is based on Ubuntu 
10.04):

tiian@mojan:~/src/swig$ python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import lixa
>>> lixa.tx_open()
0
>>> lixa.tx_begin()
0
>>> lixa.tx_commit()
0
>>> lixa.tx_close()
0
>>>

tx_open(),
 tx_begin(), tx_commit(), tx_close() are the standard functions 
described in "TX (Transaction Demarcation) Specification" (CAE X/Open 
standard).
The operations related to the Resource Managers must be put between "tx_begin()" and "tx_commit()".

TX
 standard specifies the Resource Managers must be opened by the 
Transaction Manager using "tx_open()" by the Application Program. This 
is the key point: cx_Oracle supplies its own method to get a valid 
Oracle session: "cx_Oracle.connect(...)", while LIXA wants to create it 
using "lixa.tx_open()": the Transaction Manager must open all the 
Resource Managers, not only Oracle.
Using C as the development 
language, Oracle supplies some convenience methods to retrieve the 
environment and the context for a connection opened by an XA Transaction
 Manager: xaoEnv, xaoSvcCtx.

The following one is an excerpt from a C sample (http://lixa.svn.sourceforge.net/viewvc/lixa/doc/examples/example2_ora.c?revision=703&view=markup): 

[...]

    /* open the resource manager(s) */
    if (TX_OK != (txrc = tx_open())) {
        fprintf(stderr, "tx_open error: %d\n", txrc);
        exit(txrc);
    }

    /* retrieve environment and context */
    if (NULL == (oci_env = xaoEnv(NULL))) {
        fprintf(stderr, "xaoEnv returned a NULL pointer\n");
        exit(1);
    }
    if (NULL == (oci_svc_ctx = xaoSvcCtx(NULL))) {
        fprintf(stderr, "xaoSvcCtx returned a NULL pointer\n");
        exit(1);
    }

    /* allocate statement and error handles */
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&stmt_hndl,
                             OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate statement handle\n");
        exit(1);
    }
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&err_hndl,
                             OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate error handle\n");
        exit(1);
    }

[...]

the
 Application Program does not use "OCIEnvNlsCreate(...)": the 
Transaction Manager calls "xa_open()" ("xaoopen") inside "tx_open()" and
 the Application Program retrieves the environment using a convenience 
method (xaoEnv).
The same happens when the connection must be closed using "tx_close()".

Thinking about integration between LIXA and cx_Oracle I'm proposing three different paths:
1.
 patching cx_Oracle substituting "OCIEnvNlsCreate" with "xaoEnv" and 
removing some functions using user/password, SID, etc... I don't like 
this solution because the patch must be managed and tested every time 
cx_Oracle releases something new; I don't think it would be well 
accepted by the users too

2. it could be easy to overload the 
"cx_Oracle.connect()" method: if it accepted something like a wrapped 
"OCIEnv *" too, the integration would be something like: 
cx_Oracle.connect(lixa.lixa_ora_get_ocienv() )
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context 
                  and open Resource Managers
liora = lixa.lixa_ora_get_ocienv()
conn = cx_Oracle.connect( some_function(liora) )
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
conn.close()      ... it should destroy the Python object without calling the Oracle's close function
lixa.tx_close()   ... destroy Transaction Manager context 
                  and close Resource Manager connections 
                  using xa_close() (xaoclose)

3. it could be possible to call the logic of cx_Oracle.connect() and conn.close() inside lixa.tx_open() and lixa.tx_close()
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context, 
                  open Resource Managers, 
                  call cx_Oracle.connect logic (without OCIEnvNlsCreate)
conn = lixa.lixa_ora_get_conn()
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
lixa.tx_close()   ... destroy Transaction Manager context, 
                  close Resource Manager connections, destroy 
                  conn connection object calling 
                  conn.close (cx_Oracle logic)

Option
 3 requires more integration between LIXA and cx_Oracle object but could
 be more acceptable from the development community.

What's your opinions and suggestions?

>________________________________
> Da: Michael Bayer <mi...@zz...>
>A: cx-...@li... 
>Inviato: Venerdì 7 Dicembre 2012 0:01
>Oggetto: [cx-oracle-users] cx_Oracle connection recovery subsequent to successful two-phase sequence
> 
>Hi all -
>
>I've identified a probable bug in the mechanism by which cx_oracle engages in a two-phase transaction.
>
>The state of the connection changes permanently after a two-phase begin() is emitted, and the connection has completed, either with commit() or rollback(), subsequent to the prepare() call.   It appears that once an XID is associated with the connection, this association cannot be removed, and the traditional DBAPI commit sequence no longer functions - a call to begin() is required for all subsequent usages, else the error "ORA-24776: cannot start a new transaction" is emitted.  Additionally, once this error is emitted, the connection is then in a permanently unusable state - many methods, including rollback(), commit(), and even close(),  will now produce this error unconditionally, whether or not begin() is called.   The connection cannot even be closed without exiting the application.
>
>The attached test case illustrates this, by running three different scenarios, followed by a traditional DBAPI conversation with two statements and two commit() calls.  When preceded by a successful two-phase sequence, the connection can no longer be used without emitting begin() on each call; the test suite then fails within its tearDown() sequence as the connection cannot even be closed.  I'm running with cx_Oracle 5.1.2, Python 2.6 against Oracle 10g Expression Edition on Fedora Linux.
>
>python test_oracle_twophase.py 
>..EE
>======================================================================
>ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
>----------------------------------------------------------------------
>Traceback (most recent call last):
>File "test_oracle_twophase.py", line 78, in test_run_after_twophase_begin
>   self._run_traditional_dbapi_transaction(conn)
>File "test_oracle_twophase.py", line 46, in _run_traditional_dbapi_transaction
>   conn.commit()
>DatabaseError: ORA-24776: cannot start a new transaction
>
>
>======================================================================
>ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
>----------------------------------------------------------------------
>Traceback (most recent call last):
>File "test_oracle_twophase.py", line 33, in tearDown
>   self.conn.close()
>DatabaseError: ORA-24776: cannot start a new transaction
>
>
>----------------------------------------------------------------------
>Ran 3 tests in 0.853s
>
>FAILED (errors=2)
>
>I've tried to take some naive shots at the solution to this, which appears that OCITransDetach() (maybe?) should be called after a commit() or rollback() from a two phase transaction (http://docs.oracle.com/cd/B14117_01/appdev.101/b10779/oci16ms8.htm#423200).   Though it appears this function needs to be called carefully and my initial attempts at just sticking it in there were not successful.
>
>I'm very much hoping the cx_Oracle community can confirm this behavior is considered as a bug, or at least provide guidance on some means of returning a connection back to a usable state after this error condition has proceeded.   With some guidelines on whether or not OCITransDetach() is the right path, I can attempt to provide a patch.
>
>
>
>
>
>
>
>------------------------------------------------------------------------------
>LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
>Remotely access PCs and mobile devices and provide instant support
>Improve your efficiency, and focus on delivering more value-add services
>Discover what IT Professionals Know. Rescue delivers
>http://p.sf.net/sfu/logmein_12329d2d
>_______________________________________________
>cx-oracle-users mailing list
>cx-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> 