Entity Framework and Transactionscope doesn't revert the isolation level after dispose of Transactionscope

Question

I am struggeling a bit with transaction scopes and entity framework.

Initially we want all our connections in the application to use snapshot isolation level when reading data, but in some circumstances we want to read data with either read committed or read uncommitted isolation level and for this we will use transaction scopes to change the isolation level temporary for queries (as pointed out in several posts here and in different blogs).

However, the problem is that when the transaction scope is disposed, the isolation still remains on the connection, which causes quite a bit of issues.

I have tried all types of variations, but with the same result; the isolationlevel is preserved beyond the transaction scope.

Is there anyone that can explain this behaviour for me or can explain what I am doing wrong?

I have found a workaround for the problem by encapsulating the transaction scope within a disposable class that reverts the isolation level for me, but I would appreciate a good explanation on this behaviour, I think that this behaviour doesn't only affect my code, but others too.

Here is an examplecode that illustrates the problem:

using (var context = new MyContext())
{
    context.Database.Connection.Open();

    //Sets the connection to default read snapshot
    using (var command = context.Database.Connection.CreateCommand())
    {
        command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
        command.ExecuteNonQuery();
    }

    //Executes a DBCC USEROPTIONS to print the current connection information and this shows snapshot
    PrintDBCCoptions(context.Database.Connection);

    //Executes a query
    var result = context.MatchTypes.ToArray();

    //Executes a DBCC USEROPTIONS to print the current connection information and this still shows snapshot
    PrintDBCCoptions(context.Database.Connection);

    using (var scope = new TransactionScope(TransactionScopeOption.Required,
        new TransactionOptions()
        {
            IsolationLevel = IsolationLevel.ReadCommitted //Also tried ReadUncommitted with the same result
        }))
    {
        //Executes a DBCC USEROPTIONS to print the current connection information and this still shows snapshot
        //(This is ok, since the actual new query with the transactionscope isn't executed yet)
        PrintDBCCoptions(context.Database.Connection);
        result = context.MatchTypes.ToArray();
        //Executes a DBCC USEROPTIONS to print the current connection information and this has now changed to read committed as expected                    
        PrintDBCCoptions(context.Database.Connection);
        scope.Complete(); //tested both with and without
    }

    //Executes a DBCC USEROPTIONS to print the current connection information and this is still read committed
    //(I can find this ok too, since no command has been executed outside the transaction scope)
    PrintDBCCoptions(context.Database.Connection);
    result = context.MatchTypes.ToArray();

    //Executes a DBCC USEROPTIONS to print the current connection information and this is still read committed
    //THIS ONE is the one I don't expect! I expected that the islation level of my connection should revert here
    PrintDBCCoptions(context.Database.Connection);
}

Answer 1

Well, after some digging today I found out a bit around this that I will share findings both for others to know and to get opinions and suggestions.

There are several reasons why my issue happens dependent on the environment.

Database server version:

First of all, the result of the operations depends on the SQL Server version you are running (tested on SQL Server 2012 and SQL Server 2014).

SQL Server 2012

On SQL Server 2012, the last set isolation level will follow the connection on subsequent operations even if it is released back to the connection pool and retrieved back from other threads/actions. In practice; this means that if you in some thread/action sets the isolation level to read uncommitted using a transaction, the connection will preserve this until a another transaction scope sets it to a another isolation level (or by doing a SET TRANSACTION ISOLATION LEVEL command on the connection). Not good, you could suddenly get dirty reads without knowing it.

For example:

Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());

using (var scope = new TransactionScope(TransactionScopeOption.Required, 
                                        new TransactionOptions 
                                        { 
                                            IsolationLevel = IsolationLevel.ReadUncommitted 
                                        }))
{
    Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
                                        .Select(mt => mt.LastUpdated).First());
    scope.Complete(); //tested both with and without
}

Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());

In this example, the first EF command will run with database default, the one within the transaction scope will run with ReadUncommitted, and the third one will also run with ReadUncommitted.

SQL Server 2014

On SQL Server 2014 on the other hand, each time a connection is acquired from the connection pool the sp_reset_connection procedure (it seems like it is this one anyway) will set the isolation level back to default on the database, EVEN if the connection is reacquired from within the same transaction scope. In practice; this means that if you have a transaction scope where you execute two subsequent commands only the first one will get the isolation level of the transaction scope. Also not good; you will get (based on the default isolation level on the database) either get a lock or snapshot readings.

For example:

Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());

using (var scope = new TransactionScope(TransactionScopeOption.Required, 
                                        new TransactionOptions 
                                        { 
                                            IsolationLevel = IsolationLevel.ReadUncommitted 
                                        }))
{
    Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
                             .Select(mt => mt.LastUpdated).First());
    Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
                             .Select(mt => mt.LastUpdated).First());
    scope.Complete(); 
}

In this example, the first EF command will run with database default, the first one within the transaction will run with ReadUncommitted, but the second one within the scope will suddenly run as database default again.

The manually open connection issue:

There is other issues that happens on the different SQL Server versions with a manually open connection, however, we strictly don't need to do this so I am not going to dwell down into this problem now.

Using Database.BeginTransaction:

For some reason, the Database.BeginTransaction logic of Entity Framework seems to work in both databases which is OK, but in our code we works against two different databases and then we need transaction scopes.

Conclusion:

I find this handling of isolation level in conjunction with transaction scopes in SQL Server quite buggy after this, it is in my opinion not safe to use and could cause serious problems in any application as I see it. Be very cautious using this.

But the fact remains, we need to have this working in our code. Having dealt with the tedious support at MS lately with not that great result I will first find a workaround that works for us. I will then report my findings using Connect and hope for the best that Microsoft does some actions around the transaction scope handling and connections.

Solution:

The solution (as far as I have come) is like this.

Here is the requirements this solution will have: 1. The database MUST be READ COMMITTED in isolation level due to other applications that runs against the same database that requires this, we cannot use READ COMMITTED SNAPSHOT defaults on the database 2. Our application MUST have a default of SNAPSHOT isolation level - This is solved by using SET TRANSACTION ISOLATIONLEVEL SNAPSHOT 3. If there is a transaction scope, we need to honour the isolation level for this

So based on these criterias the solution will be like this:

In the context constructor, I register to the StateChange event where I in turn when the state is changed to Open and there is no active transaction defaults the isolation level to snapshot using classic ADO.NET. If a transaction scope is used, we need to honour the settings of this by running SET TRANSACTION ISOLATIONLEVEL based on the settings here (to limit our own code, we will only allow IsolationLevel of ReadCommitted, ReadUncommitted and Snapshot). As for transactions created by Database.BeginTransaction on the context it seems that this is honoured as it should so we don't do any special actions with these types of transactions.

Here is the code in the context:

public MyContext()
{
    Database.Connection.StateChange += OnStateChange;
}

protected override void Dispose(bool disposing)
{
    if(!_disposed)
    {
        Database.Connection.StateChange -= OnStateChange;
    }

    base.Dispose(disposing);
}

private void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.CurrentState == ConnectionState.Open && args.OriginalState != ConnectionState.Open)
    {
        using (var command = Database.Connection.CreateCommand())
        {
            if (Transaction.Current == null)
            {
                command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
            }
            else
            {
                switch (Transaction.Current.IsolationLevel)
                {
                    case IsolationLevel.ReadCommitted:
                        command.CommandText = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
                        break;
                    case IsolationLevel.ReadUncommitted:
                        command.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                        break;
                    case IsolationLevel.Snapshot:
                        command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }
            }

            command.ExecuteNonQuery();
        }
    }
}

I have tested this code both in SQL Server 2012 and 2014 and it seems to work. It is not the most nice code and it has it's limitations (e.g. it will for each EF execution always do a SET TRANSACTION ISOLATIONLEVEL against the database and thus add extra network traffic.)