Prevent SQL server isolation level from failing

Fixed bug where SQL Server transaction isolation must be fetched
from a different view when using Azure data warehouse, the query
is now attempted against both views and then a NotImplemented
is raised unconditionally if failure continues to provide the
best resiliency against future arbitrary API changes in new
SQL Server versions.

Change-Id: I621b5089febe8ace136428fa133fde1a7e21cda4
Fixes: #3994

1 files changed, 30 insertions, 15 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 814fc7799..0399cbd90 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1786,21 +1786,36 @@ class MSDialect(default.DefaultDialect):
             raise NotImplementedError(
                 "Can't fetch isolation level prior to SQL Server 2005")
 
-        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()
+        views = ("sys.dm_exec_sessions", "sys.dm_pdw_nodes_exec_sessions")
+        for view in views:
+            cursor = connection.cursor()
+            try:
+                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 %s
+                    where session_id = @@SPID
+                  """ % view)
+                val = cursor.fetchone()[0]
+            except self.dbapi.Error as err:
+                continue
+            else:
+                return val.upper()
+            finally:
+                cursor.close()
+
+        util.warn(
+            "Could not fetch transaction isolation level, "
+            "tried views: %s; final error was: %s" % (views, err))
+        raise NotImplementedError(
+            "Can't fetch isolation level on this particular "
+            "SQL Server version"
+        )
 
     def initialize(self, connection):
         super(MSDialect, self).initialize(connection)