diff options
| -rw-r--r-- | doc/build/changelog/unreleased_12/4536.rst | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 2 | ||||
| -rw-r--r-- | test/dialect/mssql/test_engine.py | 34 |
3 files changed, 44 insertions, 0 deletions
diff --git a/doc/build/changelog/unreleased_12/4536.rst b/doc/build/changelog/unreleased_12/4536.rst new file mode 100644 index 000000000..b21696211 --- /dev/null +++ b/doc/build/changelog/unreleased_12/4536.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, mssql + :tickets: 4536 + :versions: 1.3.1 + + A commit() is emitted after an isolation level change to SNAPSHOT, as both + pyodbc and pymssql open an implicit transaction which blocks subsequent SQL + from being emitted in the current transaction. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 2d883309d..992b97188 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2235,6 +2235,8 @@ class MSDialect(default.DefaultDialect): cursor = connection.cursor() cursor.execute("SET TRANSACTION ISOLATION LEVEL %s" % level) cursor.close() + if level == "SNAPSHOT": + connection.commit() def get_isolation_level(self, connection): if self.server_version_info < MS_2005_VERSION: diff --git a/test/dialect/mssql/test_engine.py b/test/dialect/mssql/test_engine.py index 5f46c5377..431b20c7a 100644 --- a/test/dialect/mssql/test_engine.py +++ b/test/dialect/mssql/test_engine.py @@ -435,6 +435,40 @@ class VersionDetectionTest(fixtures.TestBase): eq_(dialect._get_server_version_info(conn), expected) +class RealIsolationLevelTest(fixtures.TestBase): + __only_on__ = "mssql" + __backend__ = True + + @testing.provide_metadata + def test_isolation_level(self): + Table("test", self.metadata, Column("id", Integer)).create( + checkfirst=True + ) + + with testing.db.connect() as c: + default = testing.db.dialect.get_isolation_level(c.connection) + + values = [ + "READ UNCOMMITTED", + "READ COMMITTED", + "REPEATABLE READ", + "SERIALIZABLE", + "SNAPSHOT", + ] + for value in values: + with testing.db.connect() as c: + c.execution_options(isolation_level=value) + + c.execute("SELECT TOP 10 * FROM test") + + eq_( + testing.db.dialect.get_isolation_level(c.connection), value + ) + + with testing.db.connect() as c: + eq_(testing.db.dialect.get_isolation_level(c.connection), default) + + class IsolationLevelDetectTest(fixtures.TestBase): def _fixture(self, view): class Error(Exception): |