sqlconnection如何管理isolationlevel?

隔离级别是在底层DBMS中实现的,比如说SQLServer。设置隔离级别很可能会设置用于设置连接隔离级别的SQL命令。

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

当处理隔离级别时,您应该在任何事务结束时重置隔离级别,或者在请求新连接时设置隔离级别,甚至更好。

SqlConnection.BeginTransaction 接受一个 IsolationLevel 参数,这就是如何控制sqlclient连接的隔离级别。另一个选项是使用通用System.Transactions并在 TransactionOptions.IsolationLevel 传递到TransactionScope constructor . 在sqlclient和system.transactions编程模型中,必须为每个事务显式指定隔离级别。如果未指定,将使用默认值(read committed for sqlclient,serializable for system.transactions)。

集合连接不能盲目重复使用。它们具有隐藏的内部成员来跟踪当前状态,如当前事务、挂起的结果等,框架可以清除返回池的连接。仅仅因为状态在编程模型中不公开,这并不意味着不存在(这适用于任何库类,任何类设计器都可以将成员隐藏在 internal 雨伞。

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

它不会将隔离级别返回到原始值。使用实体的示例需要一个空事务来重置级别(尽管它显然不需要提交(不需要.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());
}

在哪里? from link

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        

在 SQL Server 2014 当连接返回池时,池连接的隔离级别将重置。见 this forum post

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

更新日期:2017-04-22

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

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

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