Implement autocommit isolation level for cx_oracle

As part of this change Oracle also gets the concept of a
default isolation level, however since Oracle does not provide a
fixed method of knowing what the isolation level would be without a
server side transaction actually in progress, for now we hardcode
just to "READ COMMITTED".

Enhanced the test suite for isolation level testing in the dialect
test suite and added features to requirements so that the supported
isolation levels can be reported generically for dialects.

Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #5200
Change-Id: I2c4d49da9ff80ccc228c21e196ec9a961de53478

2 files changed, 57 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index d12453924..c0ee66ad7 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -53,6 +53,36 @@ This step is also required when using table reflection, i.e. autoload=True::
         autoload=True
   )
 
+Transaction Isolation Level / Autocommit
+----------------------------------------
+
+The Oracle database supports "READ COMMITTED" and "SERIALIZABLE" modes
+of isolation, however the SQLAlchemy Oracle dialect currently only has
+explicit support for "READ COMMITTED".  It is possible to emit a
+"SET TRANSACTION" statement on a connection in order to use SERIALIZABLE
+isolation, however the SQLAlchemy dialect will remain unaware of this setting,
+such as if the :meth:`.Connection.get_isolation_level` method is used;
+this method is hardcoded to return "READ COMMITTED" right now.
+
+The AUTOCOMMIT isolation level is also supported by the cx_Oracle dialect.
+
+To set using per-connection execution options::
+
+    connection = engine.connect()
+    connection = connection.execution_options(
+        isolation_level="AUTOCOMMIT"
+    )
+
+Valid values for ``isolation_level`` include:
+
+* ``READ COMMITTED``
+* ``AUTOCOMMIT``
+
+
+.. versionadded:: 1.3.16 added support for AUTOCOMMIT to the cx_oracle dialect
+   as well as the notion of a default isolation level, currently harcoded
+   to "READ COMMITTED".
+
 Identifier Casing
 -----------------
 
@@ -1395,6 +1425,20 @@ class OracleDialect(default.DefaultDialect):
             connection, additional_tests
         )
 
+    _isolation_lookup = ["READ COMMITTED"]
+
+    def get_isolation_level(self, connection):
+        return "READ COMMITTED"
+
+    def set_isolation_level(self, connection, level):
+        # prior to adding AUTOCOMMIT support for cx_Oracle, the Oracle dialect
+        # had no notion of setting the isolation level.  As Oracle
+        # does not have a straightforward way of getting the isolation level
+        # if a server-side transaction is not yet in progress, we currently
+        # hardcode to only support "READ COMMITTED" and "AUTOCOMMIT" at the
+        # cx_oracle level.  See #5200.
+        pass
+
     def has_table(self, connection, table_name, schema=None):
         if not schema:
             schema = self.default_schema_name
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index d5177daa6..3a3bbad25 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -1138,5 +1138,18 @@ class OracleDialect_cx_oracle(OracleDialect):
     def do_recover_twophase(self, connection):
         connection.info.pop("cx_oracle_prepared", None)
 
+    def set_isolation_level(self, connection, level):
+        if hasattr(connection, "connection"):
+            dbapi_connection = connection.connection
+        else:
+            dbapi_connection = connection
+        if level == "AUTOCOMMIT":
+            dbapi_connection.autocommit = True
+        else:
+            dbapi_connection.autocommit = False
+            super(OracleDialect_cx_oracle, self).set_isolation_level(
+                dbapi_connection, level
+            )
+
 
 dialect = OracleDialect_cx_oracle