我在处理事务范围和Entity Framework方面有些困难。
最初,我们希望应用程序中的所有连接在读取数据时都使用快照隔离级别,但在某些情况下,我们希望使用已提交或未提交的隔离级别来读取数据,为此,我们将使用事务范围临时更改查询的隔离级别(正如在几篇帖子和不同的博客中指出的那样)。
然而,问题在于当事务范围被处理后,隔离级别仍然保留在连接上,这会导致相当多的问题。
我尝试了各种变化,但结果都一样; 隔离级别在事务范围之外被保留。
是否有人可以为我解释这种行为,或者可以解释我做错了什么?
我已经找到了一个解决方法,通过将事务范围封装在一个可处置类中,该类为我还原隔离级别,但我希望能够对这种行为进行良好的解释,我认为这种行为不仅影响我的代码,而且也会影响其他人。
以下是一个示例代码,说明了问题:
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);
}