SQL Server:跨池连接的隔离级别泄漏

2021-12-02 00:00:00 transactions tsql sql-server transactionscope ado.net

正如之前的堆栈溢出问题(TransactionScope 和连接池和SqlConnection 如何管理 IsolationLevel?)，事务隔离级别在 SQL Server 和 ADO 的池连接之间泄漏.NET(还有 System.Transactions 和 EF，因为它们构建在 ADO.NET 之上).

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. 发生需要显式事务以确保数据一致性的请求
  2. 任何其他不使用显式事务的请求，因为它只进行非关键读取.此请求现在将作为可序列化执行，可能导致危险的阻塞和死锁

问题:防止这种情况的最佳方法是什么?现在真的需要到处使用显式事务吗?

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

这是一个独立的复制品.您将看到第三个查询将继承第二个查询的 Serializable 级别.

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

输出:

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

推荐答案

在 SQL Server 2014 中，这似乎已得到修复.如果使用 TDS 协议 7.3 或更高版本.

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

在 SQL Server 12.0.2000.8 版上运行，输出为:

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:

  • SQL Server 2014 中数据库引擎功能的行为变化
  • SQL Server 2014 中数据库引擎功能的重大更改

但微软论坛上已经记录了这一变化.

不幸的是，这后来未修复"；在 SQL Server 2014 CU6 和 SQL Server 2014 SP1 CU1 中，因为它引入了一个错误:

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

FIX:在SQL Server 2014中释放SQL Server连接时事务隔离级别被错误重置

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

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

解决方法

看起来，由于传递参数使驱动程序使用 sp_executesql，这会强制一个新的作用域，类似于存储过程.范围在批处理结束后回滚.

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

相关文章