SQL Server: Isolation level leaks across pooled connections

Question

As demonstrated by previous Stack Overflow questions (TransactionScope and Connection Pooling and How does SqlConnection manage IsolationLevel?), the transaction isolation level leaks across pooled connections with SQL Server and ADO.NET (also System.Transactions and EF, because they build on top of ADO.NET).

This means, that the following dangerous sequence of events can happen in any application:

1. A request happens which requires an explicit transaction to ensure data consistency
2. Any other request comes in which does not use an explicit transaction because it is only doing uncritical reads. This request will now execute as serializable, potentially causing dangerous blocking and deadlocks

The question: What is the best way to prevent this scenario? Is it really required to use explicit transactions everywhere now?

Here is a self-contained repro. You will see that the third query will have inherited the Serializable level from the second query.

class Program
{
    static void Main(string[] args)
    {
        RunTest(null);
        RunTest(IsolationLevel.Serializable);
        RunTest(null);
        Console.ReadKey();
    }

    static void RunTest(IsolationLevel? isolationLevel)
    {
        using (var tran = isolationLevel == null ? null : new TransactionScope(0, new TransactionOptions() { IsolationLevel = isolationLevel.Value }))
        using (var conn = new SqlConnection("Data Source=(local); Integrated Security=true; Initial Catalog=master;"))
        {
            conn.Open();

            var cmd = new SqlCommand(@"
select         
        case transaction_isolation_level 
            WHEN 0 THEN 'Unspecified' 
            WHEN 1 THEN 'ReadUncommitted' 
            WHEN 2 THEN 'ReadCommitted' 
            WHEN 3 THEN 'RepeatableRead' 
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot' 
        end as lvl, @@SPID
     from sys.dm_exec_sessions 
    where session_id = @@SPID", conn);

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Isolation Level = " + reader.GetValue(0) + ", SPID = " + reader.GetValue(1));
                }
            }

            if (tran != null) tran.Complete();
        }
    }
}

Output:

Isolation Level = ReadCommitted, SPID = 51
Isolation Level = Serializable, SPID = 51
Isolation Level = Serializable, SPID = 51 //leaked!

Solution 1

In SQL Server 2014 this seem to have been fixed. If using TDS protocol 7.3 or higher.

Running on SQL Server version 12.0.2000.8 the output is:

ReadCommitted
Serializable
ReadCommitted

Unfortunately this change is not mentioned in any documentation such as:

• Behavior Changes to Database Engine Features in SQL Server 2014
• Breaking Changes to Database Engine Features in SQL Server 2014

But the change has been documented on a Microsoft Forum.

Update 2017-03-08

Unfortunately this was later "unfixed" in SQL Server 2014 CU6 and SQL Server 2014 SP1 CU1 since it introduced a bug:

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

"Assume that you use the TransactionScope class in SQL Server client-side source code, and you do not explicitly open the SQL Server connection in a transaction. When the SQL Server connection is released, the transaction isolation level is reset incorrectly."

Workaround

It appears that, since passing through a parameter makes the driver use sp_executesql, this forces a new scope, similar to a stored procedure. The scope is rolled back after the end of the batch.

Therefore, to avoid the leak, pass through a dummy parameter, as show below.

using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}
using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    comm.Parameters.Add("@dummy", SqlDbType.Int).Value = 0;  // see with and without
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}
using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}

Solution 2

The connection pool calls sp_resetconnection before recycling a connection. Resetting the transaction isolation level is not in the list of things that sp_resetconnection does. That would explain why "serializable" leaks across pooled connections.

I guess you could start each query by making sure it's at the right isolation level:

if not exists (
              select  * 
              from    sys.dm_exec_sessions 
              where   session_id = @@SPID 
                      and transaction_isolation_level = 2
              )
    set transaction isolation level read committed

Another option: connections with a different connection string do not share a connection pool. So if you use another connection string for the "serializable" queries, they won't share a pool with the "read committed" queries. An easy way to alter the connection string is to use a different login. You could also add a random option like Persist Security Info=False;.

Finally, you could make sure every "serializable" query resets the isolation level before it returns. If a "serializable" query fails to complete, you could clear the connection pool to force the tainted connection out of the pool:

SqlConnection.ClearPool(yourSqlConnection);

This is potentially expensive, but failing queries are rare, so you should not have to call ClearPool() often.

Solution 3

For those using EF in .NET, you can fix this for your whole application by setting a different appname per isolation level (as also stated by @Andomar):

//prevent isolationlevel leaks
//https://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections
public static DataContext CreateContext()
{
    string isolationlevel = Transaction.Current?.IsolationLevel.ToString();
    string connectionString = ConfigurationManager.ConnectionStrings["yourconnection"].ConnectionString;
    connectionString = Regex.Replace(connectionString, "APP=([^;]+)", "App=$1-" + isolationlevel, RegexOptions.IgnoreCase);

    return new DataContext(connectionString);
}

Strange this is still an issue 8 years later ...

Comments

[user423430]

This behavior is 'By Design': connect.microsoft.com/SQLServer/feedback/details/243527/…

[usr]

Accepting this because it shows that this behavior is by design. A good solution does not seem to be available.

[Mark Sowul]

We went the connection string route. If Transaction.Current is not null, we change the "Application Name"

[usr]

Looks great. I'm waiting for official confirmation. Leave a comment here if you notice any. The connect issue does not have one yet.

[Thomas]

SQL Team confirmation in MSDN Forum: social.msdn.microsoft.com/Forums/sqlserver/en-US/…

[Erik Hart]

Anyway, there will still be SQL 2005, 2008 and 2012 with most business applications for a while, but nice to see that transactions finally become transactional, in terms of isolation level.

[StuartLC]

Celebrations with Sql2014 may be premature - see here: support.microsoft.com/en-us/kb/3025845

[Nick Jones]

I've just tested on 12.0.4100 (2014 SP1) and the fix still holds. The third connection goes back to ReadCommitted.

[Shane Neuville]

I tested this against SQL Azure V12 and the third connection goes back to ReadCommitted

[Richard]

I just tested this on SQL Server 2014 Standard SP4 CU2 and the 3rd connection is Serializable, ie the fix doesn't appear to be present.

[Mike Dimmick]

@Stony the fix is also dependent on the TDS protocol version in use. It requires that you're using .NET 4.0 at least (according to github.com/Microsoft/azure-docs/blob/… which states ADO.NET 4.0 uses TDS 7.3). freetds.org/userguide/tdshistory.htm states that 7.3 added support for the extended date time types, which were added to ADO.NET for 3.5 SP1.

[Chris F Carroll]

Using different connection strings for different isolation levels makes good sense to me

[user2864740]

Note: Adding spaces to the end of the connection string is sufficient to get it to be from a different pool. This is seriously the approach I am thinking of :-/

[kshitij Sabale]

This issue is still occuring on SQL Server 2016 SP1 CU5 with a .Net 4.6 client running on Windows Server 2016

[usr]

Very creative solution!

[Christian Davén]

The reason why this works, is that connection pooling is per connection string, so you can modify anything in the connection string to get a new pool.