SQL Server: Isolation level leaks across pooled connections

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 ...

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());
}

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.