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

[Michael B. <mi...@zz...>]

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.