diff options
| author | Mike Bayer <mike_mp@zzzcomputing.com> | 2017-05-21 15:28:00 -0400 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2017-05-22 10:58:08 -0400 |
| commit | 2574ca4b215cb06720d3ff4352d87ce87ebdd160 (patch) | |
| tree | e29dbc695351cfffd747dfea8822fabdf567329d | |
| parent | ccbd68bb3ce09a013c998e4dcc26c0df7b03f205 (diff) | |
| download | sqlalchemy-2574ca4b215cb06720d3ff4352d87ce87ebdd160.tar.gz | |
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
| -rw-r--r-- | doc/build/changelog/changelog_11.rst | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 45 |
2 files changed, 41 insertions, 15 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 1996f90dd..9fba02c32 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -21,6 +21,17 @@ .. changelog:: :version: 1.1.11 + .. change:: 3994 + :tags: bug, mssql + :tickets: 3994 + + 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. + .. changelog:: :version: 1.1.10 :released: Friday, May 19, 2017 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) |