SqlConnection如何管理IsolationLevel？

Question

这篇 MSDN 文章指出：

隔离级别具有连接范围 范围，并且一旦设置为连接 使用设置事务隔离 LEVEL声明，仍然有效 直到连接关闭或 设置另一个隔离级别。当一个 连接关闭并返回 池，隔离级别 最后设置事务隔离级别 声明被保留。随后的 连接重用池化 连接使用隔离级别 当时有效的 连接被池化。

SqlConnection 类没有可以持有隔离级别的成员。那么连接如何知道要在什么隔离级别下运行？？？

我问这个问题的原因是由于以下情况：

1. 我使用 可序列化中的 TransactionScope 模式，说“T1”。
2. 打开 T1 的连接。
3. T1 已完成/处置，连接 返回连接池。
4. 对同一对象调用另一个查询 连接（从获取它之后 连接池）并且此查询运行 在可序列化模式下！！！

问题：

1. 池化连接如何仍然存在 知道隔离级别是什么 与之相关？？？
2. 如何将其恢复为其他状态 交易级别？？？

分辨率：
池连接返回可序列化隔离级别的原因如下：

1. 您有一个连接池（假设是 CP1）
2. CP1 可能有 50 个连接。
3. 您从 CP1 中选择一个连接 C1 并使用 Serialized 执行它。该连接现在已设置其隔离级别。 无论您做什么，都不会重置（除非此连接已关闭） 用于在不同的隔离级别执行代码）。
4. 执行查询后，C1(Serializable) 返回到 CP1。
5. 如果再次执行步骤 1-4，则使用的连接可能是 C1 之外的其他连接，例如 C2 或 C3。那么，这也将 将其隔离级别设置为可序列化。
6. 因此，慢慢地将 Serialzable 设置为 CP1 中的多个连接。
7. 当您执行未执行显式隔离级别设置的查询时，从 CP1 选取的连接将决定 隔离级别。例如，如果这样的查询请求连接 CP1使用C1(Serialized)来执行这个查询然后这个查询 即使您没有显式地执行，也会以可序列化模式执行 设置它。

希望能消除一些疑虑。 :)

Answer 1

隔离级别是在底层 DBMS（例如 SqlServer）中实现的。设置隔离级别很可能会设置 SQL 命令来设置连接的隔离级别。

只要连接保持打开状态，DBMS 就会保持隔离级别。由于连接已放入池中，因此它保持打开状态并保留之前所做的设置。

当搞乱隔离级别时，您应该在任何事务结束时重置隔离级别，或者更好的是在请求新连接时设置它。

Answer 2

SqlConnection.BeginTransaction 接受 IsolationLevel 参数，这就是一个控制SqlClient连接的隔离级别。另一种选择是使用通用 System.Transactions 并在 TransactionOptions.IsolationLevel 传递给 TransactionScope 构造函数。在 SqlClient 和 System.Transactions 编程模型中，都必须为每个事务显式指定隔离级别。如果未指定，则将使用默认值（对于 SqlClient 来说是已提交读，对于 System.Transactions 来说是可序列化）。

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

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

Answer 3

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

尝试在数据库服务器上使用 SP 更改 iso 级别是行不通的。 :

之前：已提交读取
期间：可序列化
之后：可序列化
通过 SP 尝试重置后：可串行化
在 XACT 重置期间：ReadComlated
由 XACT 重置后：已提交读取

// 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        

Answer 4

在SQL Server 2014中，当连接返回池时，池连接的隔离级别会重置。请参阅此论坛帖子

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

更新 2017-04-22

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

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

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