SQL Server：隔离级别在连接池中泄漏

Question

正如之前在 Stack Overflow 问题 (TransactionScope and Connection Pooling 和 How does SqlConnection manage IsolationLevel?) 中所示，事务隔离级别在与 SQL Server 和 ADO.NET 相关联的连接池中泄漏（同样适用于 System.Transactions 和 EF，因为它们建立在 ADO.NET 之上）。

这意味着，在任何应用程序中都可能发生以下危险事件序列：

1. 发生需要显式事务以确保数据一致性的请求
2. 其他任何不使用显式事务的请求进入，因为它只执行非关键读取。现在，这个请求将作为可串行化操作执行，有可能导致危险的阻塞和死锁

问题是：如何最好地预防此情况？ 现在是否真的需要在所有地方使用显式事务？

这里是一个自包含的重现。您将看到第三个查询将继承自第二个查询的 Serializable 级别。

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

输出：

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

- usr

---

在Azure SQL数据库上，您不再会遇到这种"泄漏"的问题 - 但不幸的是，在某些情况下，它现在会重置隔离级别，而您本来希望它保持不变。https://github.com/dotnet/SqlClient/issues/146 - Martin Smith

Answer 1

在回收连接之前，连接池会调用sp_resetconnection。重置事务隔离级别不在sp_resetconnection的操作列表中。这就解释了为什么“可串行化”会在连接池中泄漏。

我猜你可以通过确保每个查询处于正确的隔离级别来开始：

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

另一个选项：使用不同连接字符串的连接不共享连接池。因此，如果您为“可串行化”查询使用另一个连接字符串，则它们将不与“读取提交”查询共享池。更改连接字符串的简单方法是使用不同的登录名。您还可以添加一个随机选项，例如Persist Security Info=False;。

最后，您可以确保每个“可串行化”查询在返回之前重置隔离级别。如果“可串行化”查询未能完成，则可以清除连接池以强制将受污染的连接从池中移出：

SqlConnection.ClearPool(yourSqlConnection);

这可能会很耗费资源，但查询失败的情况很少发生，因此您不需要经常调用 ClearPool()。

- Andomar

---

这种行为是“按设计实现的”：http://connect.microsoft.com/SQLServer/feedback/details/243527/sp-reset-connection-doesnt-reset-isolation-level - user423430

接受这个是因为它表明这种行为是有意设计的。好的解决方案似乎不可用。 - usr

我们采用了连接字符串路线。如果Transaction.Current不为空，我们会更改“应用程序名称”。 - Mark Sowul

对于不同的隔离级别使用不同的连接字符串，这对我来说是有意义的。 - Chris F Carroll

注意：在连接字符串末尾添加空格就足以使它来自不同的池中。这是我正在考虑的方法:-/ - user2864740

Answer 2

在 SQL Server 2014 中似乎已经解决了这个问题。如果使用TDS协议7.3或更高版本。

在运行SQL Server版本12.0.2000.8上的输出为：

ReadCommitted
Serializable
ReadCommitted

不幸的是，这个更改在任何文档中都没有提到，例如：

• SQL Server 2014 中数据库引擎功能的行为更改
• SQL Server 2014 中数据库引擎功能的破坏性更改

但是该更改已在 Microsoft 论坛上记录。

更新 2017-03-08

不幸的是，这个问题在 SQL Server 2014 CU6 和 SQL Server 2014 SP1 CU1 中后来被“不修复”，因为它引入了一个 bug：

FIX：在释放 SQL Server 连接时错误地重置事务隔离级别 (SQL Server 2014)

"假设您在 SQL Server 客户端源代码中使用 TransactionScope 类，并且您没有在事务中显式打开 SQL Server 连接。当 SQL Server 连接被释放时，事务隔离级别将不正确地重置。"

解决方法

似乎，由于通过参数传递使驱动程序使用 sp_executesql，这会强制使用类似存储过程的新范围。该范围在批处理结束后回滚。

因此，为避免泄漏，请按如下所示通过一个虚拟参数传递。

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

- Thomas

---

看起来很不错。我正在等待官方确认。如果您注意到任何问题，请在此处留下评论。连接问题尚未解决。 - usr

无论如何，对于大多数商业应用程序来说，SQL 2005、2008和2012仍然会存在一段时间。但很高兴看到事务终于在隔离级别方面变得更加事务化。 - Erik Hart

庆祝使用Sql2014可能为时过早-请参见此处：http://support.microsoft.com/en-us/kb/3025845 - StuartLC

我刚在 SQL Server 2014 Standard SP4 CU2 上进行了测试，第三个连接是 Serializable 的，即修复似乎不存在。 - Richard

此问题仍在运行 SQL Server 2016 SP1 CU5 上发生，客户端为 .Net 4.6，在 Windows Server 2016 上运行。 - Mitch

Answer 3

对于在.NET中使用EF的用户，您可以通过为每个隔离级别设置不同的应用程序名称来为整个应用程序解决此问题（正如@Andomar所述）：

//prevent isolationlevel leaks
//https://dev59.com/22kw5IYBdhLWcg3wfKrZ
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);
}

这个问题在8年后仍然存在，让人感到奇怪...

- MichaelD

---

非常有创意的解决方案！ - usr

这个方法有效的原因是连接池是基于连接字符串的，所以你可以修改连接字符串中的任何内容来获取一个新的连接池。 - Christian Davén

Answer 4

我刚刚在这个主题上提出了一个问题，并添加了一段C#代码，可以帮助解决这个问题（意思是：仅为一个事务更改隔离级别）。

仅更改单个ADO.NET事务中的隔离级别

基本上，它是一个类，需要包装在“using”块中，在查询原始隔离级别之前和之后恢复它。

但是，它确实需要两个额外的往返来检查和恢复默认的隔离级别，而且我并不完全确定它永远不会泄漏修改后的隔离级别，尽管我几乎看不到那种危险。

- Erik Hart