fully support isolation_level parameter in base dialect

Generalized the :paramref:`_sa.create_engine.isolation_level` parameter to
the base dialect so that it is no longer dependent on individual dialects
to be present. This parameter sets up the "isolation level" setting to
occur for all new database connections as soon as they are created by the
connection pool, where the value then stays set without being reset on
every checkin.

The :paramref:`_sa.create_engine.isolation_level` parameter is essentially
equivalent in functionality to using the
:paramref:`_engine.Engine.execution_options.isolation_level` parameter via
:meth:`_engine.Engine.execution_options` for an engine-wide setting. The
difference is in that the former setting assigns the isolation level just
once when a connection is created, the latter sets and resets the given
level on each connection checkout.

Fixes: #6342
Change-Id: Id81d6b1c1a94371d901ada728a610696e09e9741

5 files changed, 130 insertions, 36 deletions

diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index ef6282525..24f8a8a87 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -2392,9 +2392,6 @@ class Engine(ConnectionEventsTarget, log.Identified):
         * The logging configuration and logging_name is copied from the parent
           :class:`_engine.Engine`.
 
-        .. TODO: the below autocommit link will have a more specific ref
-           for the example in an upcoming commit
-
         The intent of the :meth:`_engine.Engine.execution_options` method is
         to implement schemes where multiple :class:`_engine.Engine`
         objects refer to the same connection pool, but are differentiated
@@ -2404,7 +2401,7 @@ class Engine(ConnectionEventsTarget, log.Identified):
         :class:`_engine.Engine`
         has a lower :term:`isolation level` setting configured or is even
         transaction-disabled using "autocommit".  An example of this
-        configuration is at :ref:`dbapi_autocommit`.
+        configuration is at :ref:`dbapi_autocommit_multiple`.
 
         Another example is one that
         uses a custom option ``shard_id`` which is consumed by an event
diff --git a/lib/sqlalchemy/engine/characteristics.py b/lib/sqlalchemy/engine/characteristics.py
index c00bff40d..2543f591b 100644
--- a/lib/sqlalchemy/engine/characteristics.py
+++ b/lib/sqlalchemy/engine/characteristics.py
@@ -50,7 +50,7 @@ class IsolationLevelCharacteristic(ConnectionCharacteristic):
         dialect.reset_isolation_level(dbapi_conn)
 
     def set_characteristic(self, dialect, dbapi_conn, value):
-        dialect.set_isolation_level(dbapi_conn, value)
+        dialect._assert_and_set_isolation_level(dbapi_conn, value)
 
     def get_characteristic(self, dialect, dbapi_conn):
         return dialect.get_isolation_level(dbapi_conn)
diff --git a/lib/sqlalchemy/engine/create.py b/lib/sqlalchemy/engine/create.py
index e6da1d8e6..5932bfb9b 100644
--- a/lib/sqlalchemy/engine/create.py
+++ b/lib/sqlalchemy/engine/create.py
@@ -201,34 +201,32 @@ def create_engine(url, **kwargs):
            should **always be set to True**.  Some SQLAlchemy features will
            fail to function properly if this flag is set to ``False``.
 
-    :param isolation_level: this string parameter is interpreted by various
-        dialects in order to affect the transaction isolation level of the
-        database connection.   The parameter essentially accepts some subset of
-        these string arguments: ``"SERIALIZABLE"``, ``"REPEATABLE READ"``,
-        ``"READ COMMITTED"``, ``"READ UNCOMMITTED"`` and ``"AUTOCOMMIT"``.
-        Behavior here varies per backend, and
-        individual dialects should be consulted directly.
-
-        Note that the isolation level can also be set on a
-        per-:class:`_engine.Connection` basis as well, using the
+    :param isolation_level: optional string name of an isolation level
+        which will be set on all new connections unconditionally.
+        Isolation levels are typically some subset of the string names
+        ``"SERIALIZABLE"``, ``"REPEATABLE READ"``,
+        ``"READ COMMITTED"``, ``"READ UNCOMMITTED"`` and ``"AUTOCOMMIT"``
+        based on backend.
+
+        The :paramref:`_sa.create_engine.isolation_level` parameter is
+        in contrast to the
         :paramref:`.Connection.execution_options.isolation_level`
-        feature.
+        execution option, which may be set on an individual
+        :class:`.Connection`, as well as the same parameter passed to
+        :meth:`.Engine.execution_options`, where it may be used to create
+        multiple engines with different isolation levels that share a common
+        connection pool and dialect.
+
+        .. versionchanged:: 2.0 The
+           :paramref:`_sa.create_engine.isolation_level`
+           parameter has been generalized to work on all dialects which support
+           the concept of isolation level, and is provided as a more succinct,
+           up front configuration switch in contrast to the execution option
+           which is more of an ad-hoc programmatic option.
 
         .. seealso::
 
-            :attr:`_engine.Connection.default_isolation_level`
-            - view default level
-
-            :paramref:`.Connection.execution_options.isolation_level`
-            - set per :class:`_engine.Connection` isolation level
-
-            :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
-
-            :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>`
-
-            :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
-
-            :ref:`session_transaction_isolation` - for the ORM
+            :ref:`dbapi_autocommit`
 
     :param json_deserializer: for dialects that support the
         :class:`_types.JSON`
@@ -594,6 +592,10 @@ def create_engine(url, **kwargs):
 
             event.listen(pool, "connect", on_connect)
 
+        builtin_on_connect = dialect._builtin_onconnect()
+        if builtin_on_connect:
+            event.listen(pool, "connect", builtin_on_connect)
+
         def first_connect(dbapi_connection, connection_record):
             c = base.Connection(
                 engine,
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index d670cf231..9a138e69e 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -239,6 +239,7 @@ class DefaultDialect(interfaces.Dialect):
         self,
         encoding="utf-8",
         paramstyle=None,
+        isolation_level=None,
         dbapi=None,
         implicit_returning=None,
         supports_native_boolean=None,
@@ -251,12 +252,6 @@ class DefaultDialect(interfaces.Dialect):
         **kwargs
     ):
 
-        if not getattr(self, "ported_sqla_06", True):
-            util.warn(
-                "The %s dialect is not yet ported to the 0.6 format"
-                % self.name
-            )
-
         if server_side_cursors:
             if not self.supports_server_side_cursors:
                 raise exc.ArgumentError(
@@ -279,6 +274,7 @@ class DefaultDialect(interfaces.Dialect):
             self.implicit_returning = implicit_returning
         self.positional = self.paramstyle in ("qmark", "format", "numeric")
         self.identifier_preparer = self.preparer(self)
+        self._on_connect_isolation_level = isolation_level
         self.type_compiler = self.type_compiler(self)
         if supports_native_boolean is not None:
             self.supports_native_boolean = supports_native_boolean
@@ -345,6 +341,18 @@ class DefaultDialect(interfaces.Dialect):
         except ImportError:
             pass
 
+    def _builtin_onconnect(self):
+        if self._on_connect_isolation_level is not None:
+
+            def builtin_connect(dbapi_conn, conn_rec):
+                self._assert_and_set_isolation_level(
+                    dbapi_conn, self._on_connect_isolation_level
+                )
+
+            return builtin_connect
+        else:
+            return None
+
     def initialize(self, connection):
         try:
             self.server_version_info = self._get_server_version_info(
@@ -573,11 +581,51 @@ class DefaultDialect(interfaces.Dialect):
     def is_disconnect(self, e, connection, cursor):
         return False
 
+    @util.memoized_instancemethod
+    def _gen_allowed_isolation_levels(self, dbapi_conn):
+
+        try:
+            raw_levels = list(self.get_isolation_level_values(dbapi_conn))
+        except NotImplementedError:
+            return None
+        else:
+            normalized_levels = [
+                level.replace("_", " ").upper() for level in raw_levels
+            ]
+            if raw_levels != normalized_levels:
+                raise ValueError(
+                    f"Dialect {self.name!r} get_isolation_level_values() "
+                    f"method should return names as UPPERCASE using spaces, "
+                    f"not underscores; got "
+                    f"{sorted(set(raw_levels).difference(normalized_levels))}"
+                )
+            return tuple(normalized_levels)
+
+    def _assert_and_set_isolation_level(self, dbapi_conn, level):
+        level = level.replace("_", " ").upper()
+
+        _allowed_isolation_levels = self._gen_allowed_isolation_levels(
+            dbapi_conn
+        )
+        if (
+            _allowed_isolation_levels
+            and level not in _allowed_isolation_levels
+        ):
+            raise exc.ArgumentError(
+                f"Invalid value {level!r} for isolation_level. "
+                f"Valid isolation levels for {self.name!r} are "
+                f"{', '.join(_allowed_isolation_levels)}"
+            )
+
+        self.set_isolation_level(dbapi_conn, level)
+
     def reset_isolation_level(self, dbapi_conn):
         # default_isolation_level is read from the first connection
         # after the initial set of 'isolation_level', if any, so is
         # the configured default of this dialect.
-        self.set_isolation_level(dbapi_conn, self.default_isolation_level)
+        self._assert_and_set_isolation_level(
+            dbapi_conn, self.default_isolation_level
+        )
 
     def normalize_name(self, name):
         if name is None:
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index 38d2c7a57..fbdf9b829 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -919,6 +919,11 @@ class Dialect(object):
         isolation level facilities; these APIs should be preferred for
         most typical use cases.
 
+        If the dialect also implements the
+        :meth:`.Dialect.get_isolation_level_values` method, then the given
+        level is guaranteed to be one of the string names within that sequence,
+        and the method will not need to anticipate a lookup failure.
+
         .. seealso::
 
             :meth:`_engine.Connection.get_isolation_level`
@@ -990,6 +995,48 @@ class Dialect(object):
         """
         raise NotImplementedError()
 
+    def get_isolation_level_values(self, dbapi_conn):
+        """return a sequence of string isolation level names that are accepted
+        by this dialect.
+
+        The available names should use the following conventions:
+
+        * use UPPERCASE names.   isolation level methods will accept lowercase
+          names but these are normalized into UPPERCASE before being passed
+          along to the dialect.
+        * separate words should be separated by spaces, not underscores, e.g.
+          ``REPEATABLE READ``.  isolation level names will have underscores
+          converted to spaces before being passed along to the dialect.
+        * The names for the four standard isolation names to the extent that
+          they are supported by the backend should be ``READ UNCOMMITTED``
+          ``READ COMMITTED``, ``REPEATABLE READ``, ``SERIALIZABLE``
+        * if the dialect supports an autocommit option it should be provided
+          using the isolation level name ``AUTOCOMMIT``.
+        * Other isolation modes may also be present, provided that they
+          are named in UPPERCASE and use spaces not underscores.
+
+        This function is used so that the default dialect can check that
+        a given isolation level parameter is valid, else raises an
+        :class:`_exc.ArgumentError`.
+
+        A DBAPI connection is passed to the method, in the unlikely event that
+        the dialect needs to interrogate the connection itself to determine
+        this list, however it is expected that most backends will return
+        a hardcoded list of values.  If the dialect supports "AUTOCOMMIT",
+        that value should also be present in the sequence returned.
+
+        The method raises ``NotImplementedError`` by default.  If a dialect
+        does not implement this method, then the default dialect will not
+        perform any checking on a given isolation level value before passing
+        it onto the :meth:`.Dialect.set_isolation_level` method.  This is
+        to allow backwards-compatibility with third party dialects that may
+        not yet be implementing this method.
+
+        .. versionadded:: 2.0
+
+        """
+        raise NotImplementedError()
+
     @classmethod
     def get_dialect_cls(cls, url):
         """Given a URL, return the :class:`.Dialect` that will be used.