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! 

Answer 1

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.

Answer 2

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