How Do I Specify Transaction Isolation Level For Ms Sql Backend In Sql Alchemy Python

How do I set transaction level READ UNCOMMITED for all queries done through a SQL Alchemy engine object? I set the isolation_level argument as notated here: http://docs.sqlalchemy

Solution 1:

This is only available in the beta (pre-release) version of SQL Alchemy (currently at 1.1.0b2). Otherwise, the current release (1.0.14) does not have this feature. If you really want to use this feature, you can install the pre-release version like this:

pip install --pre--upgrade sqlalchemy

Source: http://docs.sqlalchemy.org/en/latest/intro.html#install-via-pip

Alternative solution is to issue the following SQL command directly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  

Source: https://msdn.microsoft.com/en-us/library/ms173763.aspx

One way to do the latter in SQL Alchemy is:

query = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'
session.connection().connection.execute(query)

Solution 2:

I notice this is an old post, but wonder if the answer to this question is to make sure the right isolation level is switched on at the database level

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

https://docs.microsoft.com/en-au/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Once the isolation level is enabled at the database level then you can set the session isolation level.

Hope that helps.