在 .NET/Entity Framework for SQL Server 中设置事务隔离级别 [英] Setting Transaction Isolation Level in .NET / Entity Framework for SQL Server

问题描述

我正在尝试在 .NET/C# 中为事务设置事务隔离级别.我正在使用以下代码来设置交易:

I am attempting to set the Transaction Isolation Level in .NET/C# for a Transaction. I am using the following code to set up the transaction:

 using (var db = new DbContext("ConnectionString"))
 {
     using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew, 
             new TransactionOptions() { IsolationLevel = IsolationLevel.Snapshot }))
     {
         ...code here

         transaction.Complete();
     }
 }

使用 SQL Server Profiler，这会产生以下结果:

Using SQL Server Profiler, this produces the following:

set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

当我特别请求快照"时，为什么将隔离级别设置为已提交读"?

Why is it setting the isolation level as 'read committed' when I specifically requested 'snapshot'?

已为相关数据库启用快照隔离.

Snapshot isolation has been turned on for the database in question.

此已提交读"被另一个长时间运行的事务阻止.

This 'read committed' is being blocked by another long running transaction.

在长时间运行的事务期间，在 SQL Server Management Studio 中运行以下代码工作正常，但上面的代码被阻止，因为隔离级别与我指定的不同.

Running the following inside the SQL Server Management Studio works just fine during the long running transaction, but the code above is blocked because the isolation level is being changed from what I specified.

USE <database>;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION;
GO

<SELECT STATEMENT>

GO
COMMIT TRANSACTION;
GO

为什么?

推荐答案

new TransactionScope 所做的只是设置 Transaction.Current.这就是它所做的 100%.现在，任何想要支持交易的人都可以查看该资产并入伍.通常的 DbConnection 类在打开时会被登记.显然，您的代码在此特定范围内不会打开连接.

All that new TransactionScope does is to set Transaction.Current. That is 100% all that it does. Now anyone who want to support transactions can look at that property and enlist. The usual DbConnection classes enlist when they are opened. Apparently, your code does not open a connection while under this particular scope.

System.Transactions 没有改变隔离级别的内置支持(这是一个可悲的遗漏).你需要自己做.

System.Transactions has no built-in support for changing the isolation level (which is a sad omission). You need to do that yourself.

这篇关于在 .NET/Entity Framework for SQL Server 中设置事务隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！