From 1b98ce355a60b7ad9898aeb412c21591bb438231 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 23 May 2017 10:17:51 -0400 Subject: [PATCH] Remove twophase for cx_Oracle 6.x Support for two-phase transactions has been removed entirely for cx_Oracle when version 6.0b1 or later of the DBAPI is in use. The two- phase feature historically has never been usable under cx_Oracle 5.x in any case, and cx_Oracle 6.x has removed the connection-level "twophase" flag upon which this feature relied. Change-Id: I2e8161cc2bc12f4845c9224cd483038112fe9734 Fixes: #3997 --- doc/build/changelog/changelog_11.rst | 11 ++++ lib/sqlalchemy/dialects/oracle/cx_oracle.py | 56 ++++++++------------- test/dialect/test_oracle.py | 25 +++++++++ 3 files changed, 57 insertions(+), 35 deletions(-) diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 90a8bb3a50..eb85fad1f5 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -32,6 +32,17 @@ best resiliency against future arbitrary API changes in new SQL Server versions. + .. change:: 3997 + :tags: bug, oracle + :tickets: 3997 + :versions: 1.2.0b1 + + Support for two-phase transactions has been removed entirely for + cx_Oracle when version 6.0b1 or later of the DBAPI is in use. The two- + phase feature historically has never been usable under cx_Oracle 5.x in + any case, and cx_Oracle 6.x has removed the connection-level "twophase" + flag upon which this feature relied. + .. changelog:: :version: 1.1.10 :released: Friday, May 19, 2017 diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index c284142458..f6c3c97b76 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -25,7 +25,10 @@ directly as a TNS name. Additional arguments which may be specified either as query string arguments on the URL, or as keyword arguments to :func:`.create_engine()` are: -* ``allow_twophase`` - enable two-phase transactions. Defaults to ``True``. +* ``allow_twophase`` - enable two-phase transactions. This argument is + **deprecated** as of the cx_Oracle 5.x series, two phase transactions are + not supported under cx_Oracle and as of cx_Oracle 6.0b1 this feature is + removed entirely. * ``arraysize`` - set the cx_oracle.arraysize value on cursors, defaulted to 50. This setting is significant with cx_Oracle as the contents of LOB @@ -185,39 +188,12 @@ To disable this processing, pass ``auto_convert_lobs=False`` to Two Phase Transaction Support ----------------------------- -Two Phase transactions are implemented using XA transactions, and are known -to work in a rudimental fashion with recent versions of cx_Oracle -as of SQLAlchemy 0.8.0b2, 0.7.10. However, the mechanism is not yet -considered to be robust and should still be regarded as experimental. +Two phase transactions are **not supported** under cx_Oracle due to poor +driver support. As of cx_Oracle 6.0b1, the interface for +two phase transactions has been changed to be more of a direct pass-through +to the underlying OCI layer with less automation. The additional logic +to support this system is not implemented in SQLAlchemy. -In particular, the cx_Oracle DBAPI as recently as 5.1.2 has a bug regarding -two phase which prevents -a particular DBAPI connection from being consistently usable in both -prepared transactions as well as traditional DBAPI usage patterns; therefore -once a particular connection is used via :meth:`.Connection.begin_prepared`, -all subsequent usages of the underlying DBAPI connection must be within -the context of prepared transactions. - -The default behavior of :class:`.Engine` is to maintain a pool of DBAPI -connections. Therefore, due to the above glitch, a DBAPI connection that has -been used in a two-phase operation, and is then returned to the pool, will -not be usable in a non-two-phase context. To avoid this situation, -the application can make one of several choices: - -* Disable connection pooling using :class:`.NullPool` - -* Ensure that the particular :class:`.Engine` in use is only used - for two-phase operations. A :class:`.Engine` bound to an ORM - :class:`.Session` which includes ``twophase=True`` will consistently - use the two-phase transaction style. - -* For ad-hoc two-phase operations without disabling pooling, the DBAPI - connection in use can be evicted from the connection pool using the - :meth:`.Connection.detach` method. - -.. versionchanged:: 0.8.0b2,0.7.10 - Support for cx_oracle prepared transactions has been implemented - and tested. .. _cx_oracle_numeric: @@ -726,6 +702,8 @@ class OracleDialect_cx_oracle(OracleDialect): self._cx_oracle_binary_types = types("BFILE", "CLOB", "NCLOB", "BLOB") self.supports_unicode_binds = self.cx_oracle_ver >= (5, 0) + self._enable_twophase = self.cx_oracle_ver < (6, 0) + self.supports_sane_multi_rowcount = self.cx_oracle_ver >= (5, 0) self.coerce_to_unicode = ( @@ -909,6 +887,7 @@ class OracleDialect_cx_oracle(OracleDialect): def create_connect_args(self, url): dialect_opts = dict(url.query) + for opt in ('use_ansi', 'auto_setinputsizes', 'auto_convert_lobs', 'threaded', 'allow_twophase'): if opt in dialect_opts: @@ -941,9 +920,11 @@ class OracleDialect_cx_oracle(OracleDialect): opts = dict( threaded=self.threaded, - twophase=self.allow_twophase, ) + if self._enable_twophase: + opts['twophase'] = self.allow_twophase + if dsn is not None: opts['dsn'] = dsn if url.password is not None: @@ -999,7 +980,12 @@ class OracleDialect_cx_oracle(OracleDialect): """create a two-phase transaction ID. this id will be passed to do_begin_twophase(), do_rollback_twophase(), - do_commit_twophase(). its format is unspecified.""" + do_commit_twophase(). its format is unspecified. + + .. deprecated:: two-phase transaction support is no longer functional + in SQLAlchemy's cx_Oracle dialect as of cx_Oracle 6.0b1 + + """ id = random.randint(0, 2 ** 128) return (0x1234, "%032x" % id, "%032x" % 9) diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index 3219a1b6dd..32b39e8836 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -17,6 +17,7 @@ from sqlalchemy.testing.engines import testing_engine from sqlalchemy.dialects.oracle import cx_oracle, base as oracle from sqlalchemy.engine import default import decimal +from sqlalchemy.engine import url from sqlalchemy.testing.schema import Table, Column import datetime import os @@ -43,6 +44,30 @@ class DialectTest(fixtures.TestBase): (6, 0) ) + def test_twophase_arg(self): + + mock_dbapi = Mock(version="5.0.3") + dialect = cx_oracle.OracleDialect_cx_oracle(dbapi=mock_dbapi) + args = dialect.create_connect_args( + url.make_url("oracle+cx_oracle://a:b@host/db")) + + eq_(args[1]['twophase'], True) + + mock_dbapi = Mock(version="5.0.3") + dialect = cx_oracle.OracleDialect_cx_oracle( + dbapi=mock_dbapi, allow_twophase=False) + args = dialect.create_connect_args( + url.make_url("oracle+cx_oracle://a:b@host/db")) + + eq_(args[1]['twophase'], False) + + mock_dbapi = Mock(version="6.0b1") + dialect = cx_oracle.OracleDialect_cx_oracle(dbapi=mock_dbapi) + args = dialect.create_connect_args( + url.make_url("oracle+cx_oracle://a:b@host/db")) + + assert 'twophase' not in args[1] + class OutParamTest(fixtures.TestBase, AssertsExecutionResults): __only_on__ = 'oracle+cx_oracle' -- 2.39.5