处置Transactionscope后，实体框架和Transactionscope不会还原隔离级别 [英] Entity Framework and Transactionscope doesn't revert the isolation level after dispose of Transactionscope
问题描述
我在事务范围和实体框架方面有些挣扎。
I am struggeling a bit with transaction scopes and entity framework.
最初，我们希望应用程序中的所有连接在读取数据时都使用快照隔离级别，但是在在某些情况下，我们希望读取已读隔离或未提交隔离级别的数据，为此，我们将使用事务范围临时更改查询的隔离级别（如此处的几篇文章和不同的博客所指出的那样）。
Initially we want all our connections in the application to use snapshot isolation level when reading data, but in some circumstances we want to read data with either read committed or read uncommitted isolation level and for this we will use transaction scopes to change the isolation level temporary for queries (as pointed out in several posts here and in different blogs).
但是，问题是，当事务范围被处置时，隔离仍然保留在连接上，这会引起很多问题。
However, the problem is that when the transaction scope is disposed, the isolation still remains on the connection, which causes quite a bit of issues.
我尝试了所有类型的变体，但结果相同；
I have tried all types of variations, but with the same result; the isolationlevel is preserved beyond the transaction scope.
是否有人可以为我解释这种行为或可以解释我做错了什么？
Is there anyone that can explain this behaviour for me or can explain what I am doing wrong?
我通过将事务范围封装在一次性类中为我恢复了隔离级别，从而找到了解决该问题的方法，但是我希望对此行为做出很好的解释，我认为这种行为不会不仅会影响我的代码，还会影响其他代码。
I have found a workaround for the problem by encapsulating the transaction scope within a disposable class that reverts the isolation level for me, but I would appreciate a good explanation on this behaviour, I think that this behaviour doesn't only affect my code, but others too.
以下示例代码说明了问题：
Here is an examplecode that illustrates the problem:
using (var context = new MyContext())
{
context.Database.Connection.Open();
//Sets the connection to default read snapshot
using (var command = context.Database.Connection.CreateCommand())
{
command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
command.ExecuteNonQuery();
}
//Executes a DBCC USEROPTIONS to print the current connection information and this shows snapshot
PrintDBCCoptions(context.Database.Connection);
//Executes a query
var result = context.MatchTypes.ToArray();
//Executes a DBCC USEROPTIONS to print the current connection information and this still shows snapshot
PrintDBCCoptions(context.Database.Connection);
using (var scope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadCommitted //Also tried ReadUncommitted with the same result
}))
{
//Executes a DBCC USEROPTIONS to print the current connection information and this still shows snapshot
//(This is ok, since the actual new query with the transactionscope isn't executed yet)
PrintDBCCoptions(context.Database.Connection);
result = context.MatchTypes.ToArray();
//Executes a DBCC USEROPTIONS to print the current connection information and this has now changed to read committed as expected
PrintDBCCoptions(context.Database.Connection);
scope.Complete(); //tested both with and without
}
//Executes a DBCC USEROPTIONS to print the current connection information and this is still read committed
//(I can find this ok too, since no command has been executed outside the transaction scope)
PrintDBCCoptions(context.Database.Connection);
result = context.MatchTypes.ToArray();
//Executes a DBCC USEROPTIONS to print the current connection information and this is still read committed
//THIS ONE is the one I don't expect! I expected that the islation level of my connection should revert here
PrintDBCCoptions(context.Database.Connection);
}
推荐答案
我发现了一些问题，我将与他人分享发现，以供他人了解并获得意见和建议。
Well, after some digging today I found out a bit around this that I will share findings both for others to know and to get opinions and suggestions.
我的问题之所以发生，有几个原因取决于
There are several reasons why my issue happens dependent on the environment.
数据库服务器版本：
首先，操作取决于您正在运行的SQL Server版本（已在SQL Server 2012和SQL Server 2014上测试）。
First of all, the result of the operations depends on the SQL Server version you are running (tested on SQL Server 2012 and SQL Server 2014).
SQL Server 2012
在SQL Server 2012上，即使在后续操作中连接被释放回连接池并从其他线程/操作中检索到，最后设置的隔离级别也会遵循该连接。在实践中;这意味着，如果您在某个线程/操作中将隔离级别设置为使用事务读取未提交，则连接将保留此状态，直到另一个事务作用域将其设置为另一个隔离级别为止（或通过在客户端上执行SET TRANSACTION ISOLATION LEVEL命令）连接）。不好，您可能会突然不知道该怎么读。
On SQL Server 2012, the last set isolation level will follow the connection on subsequent operations even if it is released back to the connection pool and retrieved back from other threads/actions. In practice; this means that if you in some thread/action sets the isolation level to read uncommitted using a transaction, the connection will preserve this until a another transaction scope sets it to a another isolation level (or by doing a SET TRANSACTION ISOLATION LEVEL command on the connection). Not good, you could suddenly get dirty reads without knowing it.
例如：
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());
using (var scope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
.Select(mt => mt.LastUpdated).First());
scope.Complete(); //tested both with and without
}
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());
在此示例中，第一个EF命令将在数据库默认情况下运行，事务范围内的命令
In this example, the first EF command will run with database default, the one within the transaction scope will run with ReadUncommitted, and the third one will also run with ReadUncommitted.
SQL Server 2014
另一方面，在SQL Server 2014中，每次从连接池中获取连接时，sp_reset_connection过程（无论如何看起来都是这样）将在数据库上将隔离级别设置回默认值，即使从同一事务范围内重新获得该连接。在实践中;这意味着，如果您有一个事务范围，在其中执行两个后续命令，则只有第一个命令将获得该事务范围的隔离级别。也不好；您将获得（基于数据库的默认隔离级别）锁定或快照读数。
On SQL Server 2014 on the other hand, each time a connection is acquired from the connection pool the sp_reset_connection procedure (it seems like it is this one anyway) will set the isolation level back to default on the database, EVEN if the connection is reacquired from within the same transaction scope. In practice; this means that if you have a transaction scope where you execute two subsequent commands only the first one will get the isolation level of the transaction scope. Also not good; you will get (based on the default isolation level on the database) either get a lock or snapshot readings.
例如：
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2).Select(mt => mt.LastUpdated).First());
using (var scope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
.Select(mt => mt.LastUpdated).First());
Console.WriteLine(context.MatchTypes.Where(mt => mt.Id == 2)
.Select(mt => mt.LastUpdated).First());
scope.Complete();
}
在此示例中，第一个EF命令将在数据库默认情况下运行，第一个事务中的第一个将使用ReadUncommitted运行，但是范围中的第二个将突然作为数据库默认值再次运行。
In this example, the first EF command will run with database default, the first one within the transaction will run with ReadUncommitted, but the second one within the scope will suddenly run as database default again.
手动打开的连接问题：
使用手动打开的连接的其他SQL Server版本上还会发生其他问题，但是，我们严格不需要这样做，所以我不是
There is other issues that happens on the different SQL Server versions with a manually open connection, however, we strictly don't need to do this so I am not going to dwell down into this problem now.
使用Database.BeginTransaction：
由于某种原因，实体框架的Database.BeginTransaction逻辑似乎在两个数据库中都可以正常工作，但是在我们的代码中，我们对两个不同的数据库进行工作，然后需要事务作用域。
For some reason, the Database.BeginTransaction logic of Entity Framework seems to work in both databases which is OK, but in our code we works against two different databases and then we need transaction scopes.
结论：
我发现隔离级别的这种处理与SQL中的事务作用域结合在一起在此之后，服务器漏洞百出，我认为使用它不安全，并且在我看来，它可能会在任何应用程序中引起严重问题。
I find this handling of isolation level in conjunction with transaction scopes in SQL Server quite buggy after this, it is in my opinion not safe to use and could cause serious problems in any application as I see it. Be very cautious using this.
但是事实仍然存在，我们需要在代码中使用它。最近在MS处理乏味的支持时并没有取得很好的结果，我首先会找到一种对我们有用的解决方法。然后，我将使用Connect报告我的发现，并希望Microsoft能够最好地围绕事务范围处理和连接执行某些操作。
But the fact remains, we need to have this working in our code. Having dealt with the tedious support at MS lately with not that great result I will first find a workaround that works for us. I will then report my findings using Connect and hope for the best that Microsoft does some actions around the transaction scope handling and connections.
解决方案：
解决方案（据我所知）是这样的。
The solution (as far as I have come) is like this.
这是此解决方案的要求将具有：
1.数据库必须必须以隔离级别进行读取提交，因为其他应用程序需要在同一数据库上运行该数据库，因此我们无法在数据库$上使用读取提交的快照默认值b $ b 2.我们的应用程序必须具有默认的SNAPSHOT隔离级别
-这可以通过使用SET TRANSACTION ISOLATIONLEVEL SNAPSHOT
来解决。3.如果存在交易范围，我们需要满足此隔离级别的要求
Here is the requirements this solution will have: 1. The database MUST be READ COMMITTED in isolation level due to other applications that runs against the same database that requires this, we cannot use READ COMMITTED SNAPSHOT defaults on the database 2. Our application MUST have a default of SNAPSHOT isolation level - This is solved by using SET TRANSACTION ISOLATIONLEVEL SNAPSHOT 3. If there is a transaction scope, we need to honour the isolation level for this
因此，根据这些标准，解决方案将如下所示：
So based on these criterias the solution will be like this:
在上下文构造函数中，我注册到StateChange事件，然后在其中te更改为Open，并且没有活动事务使用默认的ADO.NET将默认隔离级别设置为快照。如果使用事务作用域，则需要通过基于此处的设置运行SET TRANSACTION ISOLATIONLEVEL来兑现其设置（为了限制我们自己的代码，我们仅允许ReadCommitted，ReadUncommitted和Snapshot的IsolationLevel）。至于由Database.BeginTransaction在上下文中创建的事务，似乎应该这样做就很荣幸，因此我们对这些类型的事务不执行任何特殊操作。
In the context constructor, I register to the StateChange event where I in turn when the state is changed to Open and there is no active transaction defaults the isolation level to snapshot using classic ADO.NET. If a transaction scope is used, we need to honour the settings of this by running SET TRANSACTION ISOLATIONLEVEL based on the settings here (to limit our own code, we will only allow IsolationLevel of ReadCommitted, ReadUncommitted and Snapshot). As for transactions created by Database.BeginTransaction on the context it seems that this is honoured as it should so we don't do any special actions with these types of transactions.
这是上下文中的代码：
public MyContext()
{
Database.Connection.StateChange += OnStateChange;
}
protected override void Dispose(bool disposing)
{
if(!_disposed)
{
Database.Connection.StateChange -= OnStateChange;
}
base.Dispose(disposing);
}
private void OnStateChange(object sender, StateChangeEventArgs args)
{
if (args.CurrentState == ConnectionState.Open && args.OriginalState != ConnectionState.Open)
{
using (var command = Database.Connection.CreateCommand())
{
if (Transaction.Current == null)
{
command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
}
else
{
switch (Transaction.Current.IsolationLevel)
{
case IsolationLevel.ReadCommitted:
command.CommandText = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
break;
case IsolationLevel.ReadUncommitted:
command.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
break;
case IsolationLevel.Snapshot:
command.CommandText = "SET TRANSACTION ISOLATION LEVEL SNAPSHOT";
break;
default:
throw new ArgumentOutOfRangeException();
}
}
command.ExecuteNonQuery();
}
}
}
我都测试了此代码在SQL Server 2012和2014中运行，它似乎可以正常工作。它不是最好的代码，并且有其局限性（例如，对于每个EF执行，它将始终对数据库执行SET TRANSACTION ISOLATIONLEVEL，从而增加了网络流量。）
I have tested this code both in SQL Server 2012 and 2014 and it seems to work. It is not the most nice code and it has it's limitations (e.g. it will for each EF execution always do a SET TRANSACTION ISOLATIONLEVEL against the database and thus add extra network traffic.)
这篇关于处置Transactionscope后，实体框架和Transactionscope不会还原隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
