- Added basic isolation level support to the SQL Server dialects

via :paramref:`.create_engine.isolation_level` and
:paramref:`.Connection.execution_options.isolation_level`
parameters.  fixes #3534

6 files changed, 128 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index e6dac9265..178383b85 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,19 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, mssql
+        :tickets: 3534
+
+        Added basic isolation level support to the SQL Server dialects
+        via :paramref:`.create_engine.isolation_level` and
+        :paramref:`.Connection.execution_options.isolation_level`
+        parameters.
+
+        .. seealso::
+
+            :ref:`change_3534`
+
+    .. change::
         :tags: feature, mysql
         :tickets: 3332
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 3f40c7278..3e839af63 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -2001,6 +2001,28 @@ given schema in the results.  Cross-schema foreign keys aren't supported.
 Dialect Improvements and Changes - SQL Server
 =============================================
 
+.. _change_3534:
+
+Added transaction isolation level support for SQL Server
+--------------------------------------------------------
+
+All SQL Server dialects support transaction isolation level settings
+via the :paramref:`.create_engine.isolation_level` and
+:paramref:`.Connection.execution_options.isolation_level`
+parameters.  The four standard levels are supported as well as
+``SNAPSHOT``::
+
+    engine = create_engine(
+        "mssql+pyodbc://scott:tiger@ms_2008",
+        isolation_level="REPEATABLE READ"
+    )
+
+.. seealso::
+
+    :ref:`mssql_isolation_level`
+
+:ticket:`3534`
+
 .. _change_3504:
 
 String / varlength types no longer represent "max" explicitly on reflection
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 31c55917b..f50c8d3ab 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -199,6 +199,47 @@ If using SQL Server 2005 or above, LIMIT with OFFSET
 support is available through the ``ROW_NUMBER OVER`` construct.
 For versions below 2005, LIMIT with OFFSET usage will fail.
 
+.. _mssql_isolation_level:
+
+Transaction Isolation Level
+---------------------------
+
+All SQL Server dialects support setting of transaction isolation level
+both via a dialect-specific parameter
+:paramref:`.create_engine.isolation_level`
+accepted by :func:`.create_engine`,
+as well as the :paramref:`.Connection.execution_options.isolation_level`
+argument as passed to
+:meth:`.Connection.execution_options`.  This feature works by issuing the
+command ``SET TRANSACTION ISOLATION LEVEL <level>`` for
+each new connection.
+
+To set isolation level using :func:`.create_engine`::
+
+    engine = create_engine(
+        "mssql+pyodbc://scott:tiger@ms_2008",
+        isolation_level="REPEATABLE READ"
+    )
+
+To set using per-connection execution options::
+
+    connection = engine.connect()
+    connection = connection.execution_options(
+        isolation_level="READ COMMITTED"
+    )
+
+Valid values for ``isolation_level`` include:
+
+* ``READ COMMITTED``
+* ``READ UNCOMMITTED``
+* ``REPEATABLE READ``
+* ``SERIALIZABLE``
+* ``SNAPSHOT`` - specific to SQL Server
+
+.. versionadded:: 1.1 support for isolation level setting on Microsoft
+   SQL Server.
+
+
 Nullability
 -----------
 MSSQL has support for three levels of column nullability. The default
@@ -1547,6 +1588,7 @@ class MSDialect(default.DefaultDialect):
                  use_scope_identity=True,
                  max_identifier_length=None,
                  schema_name="dbo",
+                 isolation_level=None,
                  deprecate_large_types=None,
                  legacy_schema_aliasing=False, **opts):
         self.query_timeout = int(query_timeout or 0)
@@ -1560,6 +1602,8 @@ class MSDialect(default.DefaultDialect):
 
         super(MSDialect, self).__init__(**opts)
 
+        self.isolation_level = isolation_level
+
     def do_savepoint(self, connection, name):
         # give the DBAPI a push
         connection.execute("IF @@TRANCOUNT = 0 BEGIN TRANSACTION")
@@ -1569,10 +1613,52 @@ class MSDialect(default.DefaultDialect):
         # SQL Server does not support RELEASE SAVEPOINT
         pass
 
+    _isolation_lookup = set(['SERIALIZABLE', 'READ UNCOMMITTED',
+                             'READ COMMITTED', 'REPEATABLE READ',
+                             'SNAPSHOT'])
+
+    def set_isolation_level(self, connection, level):
+        level = level.replace('_', ' ')
+        if level not in self._isolation_lookup:
+            raise exc.ArgumentError(
+                "Invalid value '%s' for isolation_level. "
+                "Valid isolation levels for %s are %s" %
+                (level, self.name, ", ".join(self._isolation_lookup))
+            )
+        cursor = connection.cursor()
+        cursor.execute(
+            "SET TRANSACTION ISOLATION LEVEL %s" % level)
+        cursor.close()
+
+    def get_isolation_level(self, connection):
+        cursor = connection.cursor()
+        cursor.execute("""
+          SELECT CASE transaction_isolation_level
+            WHEN 0 THEN NULL
+            WHEN 1 THEN 'READ UNCOMMITTED'
+            WHEN 2 THEN 'READ COMMITTED'
+            WHEN 3 THEN 'REPEATABLE READ'
+            WHEN 4 THEN 'SERIALIZABLE'
+            WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL
+            FROM sys.dm_exec_sessions
+            where session_id = @@SPID
+          """)
+        val = cursor.fetchone()[0]
+        cursor.close()
+        return val.upper()
+
     def initialize(self, connection):
         super(MSDialect, self).initialize(connection)
         self._setup_version_attributes()
 
+    def on_connect(self):
+        if self.isolation_level is not None:
+            def connect(conn):
+                self.set_isolation_level(conn, self.isolation_level)
+            return connect
+        else:
+            return None
+
     def _setup_version_attributes(self):
         if self.server_version_info[0] not in list(range(8, 17)):
             # FreeTDS with version 4.2 seems to report here
diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index ef34eef01..859819a34 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -273,6 +273,8 @@ class Connection(Connectable):
 
                 :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
 
+                :ref:`SQL Server Transaction Isolation <mssql_isolation_level>`
+
                 :ref:`session_transaction_isolation` - for the ORM
 
         :param no_parameters: When ``True``, if the final parameter
diff --git a/test/engine/test_transaction.py b/test/engine/test_transaction.py
index c81a7580f..7eda6e1b4 100644
--- a/test/engine/test_transaction.py
+++ b/test/engine/test_transaction.py
@@ -1247,6 +1247,8 @@ class IsolationLevelTest(fixtures.TestBase):
             return 'READ COMMITTED'
         elif testing.against('mysql'):
             return "REPEATABLE READ"
+        elif testing.against('mssql'):
+            return "READ COMMITTED"
         else:
             assert False, "default isolation level not known"
 
@@ -1257,6 +1259,8 @@ class IsolationLevelTest(fixtures.TestBase):
             return 'SERIALIZABLE'
         elif testing.against('mysql'):
             return "SERIALIZABLE"
+        elif testing.against('mssql'):
+            return "SERIALIZABLE"
         else:
             assert False, "non default isolation level not known"
 
diff --git a/test/requirements.py b/test/requirements.py
index abc8ad5c2..4ef5d62a3 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -225,7 +225,7 @@ class DefaultRequirements(SuiteRequirements):
     @property
     def isolation_level(self):
         return only_on(
-                    ('postgresql', 'sqlite', 'mysql'),
+                    ('postgresql', 'sqlite', 'mysql', 'mssql'),
                     "DBAPI has no isolation level support"
                 ) + fails_on('postgresql+pypostgresql',
                           'pypostgresql bombs on multiple isolation level calls')