MSSQL transaction isolation support

[Denis Stebunov]

I'm trying to set transaction isolation level to READ UNCOMMITTED for connection to MSSQL 2012. If I specify it as a parameter to create_engine(), I get

...
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 144, in create
    engineclass.__name__))
TypeError: Invalid argument(s) 'isolation_level' sent to create_engine(), using configuration MSDialect_pymssql/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

If I specify it as a parameter to sessionmaker(), I get

...
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/interfaces.py", line 716, in set_isolation_level
    raise NotImplementedError()
NotImplementedError

I tried it both with pyodbc and pymssql, and none of them worked.

As a last resort I'm using .with_hint(myTable, 'WITH (nolock)') for my queries (this is equivalent to READ UNCOMMITTED at query level), but unfortunately it doesn't work with relationships loaded with joinedload - hint is ignored for relationships (but successfully applied to main table in the query).

Any ideas?

[Mike Bayer]

this is nothing that's directly in the API for pymssql or pyodbc AFAICT so we'd need to implement it using execution of "SET" statements.

you can work around for now by just emitting the appropriate "SET TRANSACTION ISOLATION LEVEL" statements on each new DBAPI connection using a connect listener, see http://docs.sqlalchemy.org/en/rel_1_0/core/events.html?highlight=connect#sqlalchemy.events.PoolEvents.connect

[Denis Stebunov]

Thank you,

proposed workaround worked for me. Full code sample if anyone interested:

from sqlalchemy import create_engine, event

engine = create_engine(...)

@event.listens_for(engine, 'connect')
def on_connect(dbapi_connection, connection_record):
    dbapi_connection.execute('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED')

[Mike Bayer]

• 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

→ <<cset 4d147c3b169a>>