SqlConnection如何管理隔离级别？

Question

这篇MSDN文章阐述了以下内容：

隔离级别具有连接范围的作用，一旦使用SET TRANSACTION ISOLATION LEVEL语句为连接设置了隔离级别，它就会一直有效，直到连接关闭或设置其他隔离级别。当连接关闭并返回到池中时，将保留最后一个SET TRANSACTION ISOLATION LEVEL语句中的隔离级别。重用池化连接的后续连接使用连接在池化时的隔离级别。

SqlConnection类没有成员可以保存隔离级别。那么一个连接怎么知道应该以什么隔离级别运行呢？

我之所以问这个问题是因为出现了以下情况：

1. 我使用TransactionScope以Serializable模式打开了一个事务，称之为“T1”。
2. 为T1打开了一个连接。
3. T1已完成/释放，连接回到了连接池。
4. 在同一连接上调用另一个查询（从连接池中获取），并且此查询以Serializable模式运行！！！

问题：

1. 池化连接怎么还知道与其关联的隔离级别是什么？
2. 如何将其恢复为其他事务级别？

解决方案：
池化连接之所以返回Serializable隔离级别是因为以下原因：

1. 您有一个连接池（假设为CP1）
2. CP1可能有50个连接。
3. 您从CP1中选择一个连接C1，并使用Serializable执行它。此连接现在已设置其隔离级别。
无论您做什么，这都不会被重置（除非此连接用于在不同隔离级别下执行代码）。 执行查询后，C1（Serializable）返回到CP1。 如果再次执行步骤1-4，则使用的连接可能是C1之外的其他连接，比如C2或C3。因此，它的隔离级别也将设置为Serializable。 因此，逐渐地，在CP1中将Serializable设置为多个连接。 当执行一个没有明确设置隔离级别的查询时，从CP1选择的连接将决定隔离级别。例如，如果这样的查询请求连接，并且CP1使用C1（Serializable）来执行此查询，则即使您没有显式设置它，此查询也将以Serializable模式执行。 希望这能解决一些疑惑。 :)

- Sidharth Panwar

Answer 1

隔离级别是在底层数据库管理系统中实现的，比如SqlServer。设置隔离级别很可能会设置SQL命令，以为连接设置隔离级别。

只要连接保持打开状态，DBMS就会保持隔离级别。因为连接被放入池中，所以它保持打开状态并保留之前进行的设置。

在处理隔离级别时，应该在任何事务结束时重置隔离级别，或者更好的方法是在请求新连接时设置隔离级别。

- Stefan Steinegger

---

问题在于下一次使用连接时，我会执行一个没有任何事务的简单查询。那么如何设置隔离级别呢？另外，我不希望每个事务都设置隔离级别，因为那是很多工作。我希望这由一个中央实体来管理。 - Sidharth Panwar

构建您的中央连接提供程序。这是我能想到的最简单的解决方案。 - Stefan Steinegger

Answer 2

SqlConnection.BeginTransaction 接受一个IsolationLevel 参数，这是控制 SqlClient 连接隔离级别的方法之一。另一种选择是使用通用的 System.Transactions，在 TransactionScope 构造函数 中指定隔离级别，并在 TransactionOptions.IsolationLevel 中传递。在 SqlClient 和 System.Transactions 编程模型中，必须显式地为每个事务指定隔离级别。如果未指定，则将使用默认值（SqlClient 为 Read Committed，System.Transactions 为 Serializable）。

池化连接不会盲目地重用。它们有隐藏的内部成员来跟踪当前状态，如当前事务、待处理结果等，框架可以清理返回到池中的连接。仅因为状态在编程模型中没有公开，这并不意味着它不存在（这适用于任何库类，任何类设计人员都可以将成员隐藏在 internal 下）。

最后，任何从池中重用的连接都会调用 sp_reset_connection，这是一个清理服务器端会话状态的服务器过程。

- Remus Rusanu

---

惊喜！https://connect.microsoft.com/SQLServer/feedback/details/243527/sp-reset-connection-doesnt-reset-isolation-level - Mark Sowul

虽然大部分是正确的，但关于隔离级别的结论是错误的。 - user2864740

Answer 3

它不会将隔离级别返回到原始值。使用实体的示例需要一个空事务来重置级别（尽管显然不需要提交（不需要.Complete()）。

尝试在DB服务器上使用SP更改iso级别无效。 输出：

Before: ReadCommitted During: Serializable After: Serializable 通过SP尝试重置后：Serializable 通过XACT进行重置期间：ReadCommitted 通过XACT进行重置后：ReadCommitted

// using Dbg = System.Diagnostics.Debug;
XactIso.iso isoEntity = new XactIso.iso();
using (isoEntity)
{
    Dbg.WriteLine("Before: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    var xactOpts = new TransactionOptions();
    xactOpts.IsolationLevel = System.Transactions.IsolationLevel.Serializable;

    using (TransactionScope xact = new TransactionScope(TransactionScopeOption.Required, xactOpts))
    {
        Dbg.WriteLine("During: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
        xact.Complete();
    }

    Dbg.WriteLine("After: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    isoEntity.usp_SetXactIsoLevel("ReadCommitted");

    Dbg.WriteLine("After Reset by SP Attempt: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // failed

    var xactOpts2 = new TransactionOptions();
    xactOpts2.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
    using (TransactionScope xact2 = new TransactionScope(TransactionScopeOption.Required, xactOpts2))
        Dbg.WriteLine("During Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // works w/o commit

    Dbg.WriteLine("After Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
}

在 来自链接 处

proc [Common].[usp_GetXactIsoLevel]
as
begin          
    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
     from sys.dm_exec_sessions 
    where session_id = @@SPID;
end

并且（没有起作用）：

proc [Common].[usp_SetXactIsoLevel]
    @pNewLevel    varchar(30)
as
begin

    if @pNewLevel = 'ReadUncommitted'
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    else if @pNewLevel = 'ReadCommitted'
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    else if @pNewLevel = 'RepeatableRead'
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    else if @pNewLevel = 'Serializable'
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    else if @pNewLevel = 'Snapshot'
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    else
        raiserror('Unrecognized Transaction Isolation Level', 16, 1);         
end        

- crokusek

---

这个回答声称可以通过直接查询来设置它，而不是使用存储过程。 - crokusek

Answer 4

在SQL Server 2014中，当连接返回到池中时，池化连接的隔离级别会被重置。请参见this forum post。

引用："在SQL 2014中，对于使用TDS版本7.3或更高版本的客户端驱动程序，SQL服务器将重置池化连接的事务隔离级别为默认值（读取已提交）。对于使用低于7.3版本TDS的客户端，在针对SQL 2014运行时，它们将具有旧的行为。"

更新 2017-04-22：不幸的是，这个问题后来在SQL Server 2014 CU6和SQL Server 2014 SP1 CU1中被“取消修复”，因为它引入了一个错误。

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

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

- Thomas

---

在本地，使用.NET 4.51和SQL Server 2014 SP2似乎并不是真的。 - user2864740

进一步阅读链接的论坛帖子，听起来这个更改已经被撤销了。 - ChaseMedallion

未来的持续行为是什么？SQL Server 13+ 做了什么？ - binki