SqlConnection如何管理IsolationLevel？

Question

这个MSDN文章声明：

隔离级别具有连接范围，并且一旦为使用set事务隔离级别语句的连接设置，它将一直有效，直到连接关闭或设置另一个隔离级别为止。当连接关闭并返回到池时，将保留上次SET事务隔离级别语句中的隔离级别。重用池连接的后续连接使用连接池时有效的隔离级别。

SqlConnection类没有可能保持隔离级别的成员。那么连接如何知道要在哪个隔离级别上运行呢？

我之所以问这个问题，是因为有以下情况：

1. 我在可序列化模式下使用TransactionScope打开了一个事务，比如"T1“。
2. 打开了T1的连接。
3. T1已完成/释放，连接返回到连接池。
4. 在同一连接上调用另一个查询(从连接池中获取该查询后)，此查询将以可序列化模式运行！

问题：

1. 池连接如何仍然知道与其关联的隔离级别？
2. 如何将其还原回其他事务级别？

决议： 池连接返回可序列化隔离级别的原因如下：

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

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

Answer 1

隔离级别是在底层DBMS中实现的，比如SqlServer。设置隔离级别很可能设置SQL命令，这些命令设置连接的隔离级别。

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

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

Answer 2

SqlConnection.BeginTransaction接受一个IsolationLevel参数，这就是控制SqlClient连接的隔离级别的方法。另一个选项是使用泛型System.Transactions，并在传递给TransactionScope 构造函数的TransactionOptions.IsolationLevel中指定隔离级别。在SqlClient和System.Transactions编程模型中，必须为每个事务显式指定隔离级别。如果未指定，则将使用默认设置( SqlClient已提交读，System.Transactions可序列化)。

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

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

Answer 3

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

使用DB服务器上的SP更改iso级别的尝试不起作用。输出：

以前: ReadCommitted 期间:可序列化 之后:可序列化 SP尝试重置后:可序列化 在由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