SQL Server: Isolation level leaks across pooled connections

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.

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:

ReadCommittedSerializableReadCommitted

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

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